Manage huge amount of data

Requirement:
1 billion records to be inserted every day
180 days data to be maintained
180 * 1 billion records = approx no of records = defines the size of the
database
Need to design the database/process to maintain so huge data?
- RHi Rakesh,
Need more info like an idea of the number of tables, size of rows, querying
which will reflect what indexes are required etc...
You will need some good kit though, lots of disks; what sort of fault
tolerance are you looking at because backups are going to be a problem.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:CE1870BB-68DB-47B8-A1B8-EFE8C30FB333@.microsoft.com...
> Requirement:
> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
> Need to design the database/process to maintain so huge data?
>
> - R|||> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
With an average row size of 100 bytes, this calculates to about 18 TB of
usable space not including index overhead. You'll also need to sustain a
rate of over 10 thousand inserts per second 24x7.
Very large tables are often partitioned for manageability reasons and to
address backup issues like Tony mentioned. Attention to detail is very
important. Unless you have experience working with very large databases, I
suggest you engage consultants with VLDB experience to help you out. We can
help you with specific questions but a project of such magnitude requires
dedicated resources with specialized experience.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:CE1870BB-68DB-47B8-A1B8-EFE8C30FB333@.microsoft.com...
> Requirement:
> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
> Need to design the database/process to maintain so huge data?
>
> - R

Make subtotal column widths bigger than normal columns?

The reason I say this is because a subtotal of a dollar amount will take up more space than other values. Right now, I'm forced to make all columns the same larger width because it appears to be all wrapped into 1 column width setting. I can try to change the value of the subtotal column, "matrixcolumn4", but it reverts to the other value after I press enter to apply the changes.

Sorry there is no good solution at this point. One alternative approach is to use side-by-side matrices to a subtotal column with different width, however you would need to "hide" the row headers of the second matrix.

For the next major SSRS release we are looking into significantly improved support of these scenarios.

-- Robert

Make subtotal column widths bigger than normal columns?

The reason I say this is because a subtotal of a dollar amount will take up more space than other values. Right now, I'm forced to make all columns the same larger width because it appears to be all wrapped into 1 column width setting. I can try to change the value of the subtotal column, "matrixcolumn4", but it reverts to the other value after I press enter to apply the changes.

Sorry there is no good solution at this point. One alternative approach is to use side-by-side matrices to a subtotal column with different width, however you would need to "hide" the row headers of the second matrix.

For the next major SSRS release we are looking into significantly improved support of these scenarios.

-- Robert

make selection of your query result

i will try to explain what i want with an example

if have a query that returns

user code amount

user1 A 10

user1 B 100

user1 C 10

user2 B 50

user2 D 10

user3 A 10

user3 C 20

what i want in my report is the following as result

Sum of code A and C Sum of code B and D

user1 20 100

user2 60

user3 30

so can you make variables on the scope user that do a selection on the 'code'

in words like a variable that give me the sum of the amount where code in A and C in the scope user

Maybe this is not the best choice but it works.

We have:

Usuario Code Amount
--

Alejandro C 200
Erika A 50
Erika B 50
Karla D 100
Marcos A 100
Marcos B 50
Marcos C 25
Marcos D 70
Pedro A 100
Pedro C 70

and the code Returns

usuario F_Group S_Group
- - -
Alejandro 200 0
Erika 50 50
Karla 0 100
Marcos 125 120
Pedro 170 0

Code Snippet

--declare the parameters
declare @.F_group_1 varchar(2)
declare @.F_group_2 varchar(2)
declare @.S_group_1 varchar(2)
declare @.S_group_2 varchar(2)
--set value to parameters or define scoupe
select @.F_group_1='A',
@.F_group_2='C',
@.S_group_1='B',
@.S_group_2='D'
--sum both result
select qry.usuario, sum(qry.F_group) F_Group, sum(qry.S_group) S_Group
from (
-- sum the first group and in the 2nd we put 0
select usuario, sum(amount) F_group , 0 as S_group
from tabla
where code in (@.F_group_1, @.F_group_2)
group by usuario
union
-- sum the 2nd group and in the 1st we put 0
select usuario, 0 as F_group, sum(amount) S_group
from tabla
where code in (@.S_group_1, @.S_group_2)
group by usuario
) as qry
group by qry.usuario

Regards,

Marcos

|||

your solution is done at/in the query / datasource and i thought about that too

but is there a solution possible in report itself ?

like in businesobject you can make variables who are sum of an amount in a scope and with a where condition

on the report

|||

Krisje,

If I understand you correctly the following should work.

1. Use a table with 3 columns

2. Insert a group and set it to group on user

3. On the group row set column 1 expesression to fields!user

4. On the group row set column 2 to

=sum(iif(Fields!code.Value="A" or Fields!code.Value="C",Fields!amount.Value,0))

5. On the group row set column 2 to

=sum(iif(Fields!code.Value="A" or Fields!code.Value="C",0,Fields!amount.Value))

make a record display the numbers

I am looking for a query that counts the amount of pictures the user has in
the table, then select the 1st 20 pictures and create a record number. e.g.,
results would be summit like = 1, ~/pix/1.jpg, mypic. where 1 is the
autonumber from the count and ~/pix/1.jpg is the image location and mypic is
the caption. the table select script is below with all the colums to be
returned.
SELECT Pic, Cap
FROM Pics
WHERE UName = @.UName
there are only two columns in the table except for the key which is pId.
If anyone can help I would be grateful.Wow, this has come up a half dozen times day.
First off, how do you define the "first" 20 pictures?
SELECT TOP 20 Pic, Cap
FROM Pics
WHERE UName = @.UName
ORDER BY ...'...
Next, why can't these rankings be generated by the presentation layer (e.g.
ASP code or what have you), which has to loop through each row anyway,
instead of forcing the database to /also/ do this iteration?
http://www.aspfaq.com/2427
"Eamon" <eamon@.nuvola.co.uk> wrote in message
news:%23weQ$ppoFHA.3408@.tk2msftngp13.phx.gbl...
>I am looking for a query that counts the amount of pictures the user has in
> the table, then select the 1st 20 pictures and create a record number.
> e.g.,
> results would be summit like = 1, ~/pix/1.jpg, mypic. where 1 is the
> autonumber from the count and ~/pix/1.jpg is the image location and mypic
> is
> the caption. the table select script is below with all the colums to be
> returned.
> SELECT Pic, Cap
> FROM Pics
> WHERE UName = @.UName
> there are only two columns in the table except for the key which is pId.
> If anyone can help I would be grateful.
>|||Eamon, what determines "first"?
If it's pId, then:
DECLARE @.T TABLE
(
rn INT NOT NULL IDENTITY,
Pic <datatype>,
Cap <datatype>
);
INSERT INTO @.T
SELECT Pic, Cap
FROM Pics
WHERE UName = @.UName
ORDER BY pId;
SELECT * FROM @.T;
BG, SQL Server MVP
www.SolidQualityLearning.com
"Eamon" wrote:

> I am looking for a query that counts the amount of pictures the user has i
n
> the table, then select the 1st 20 pictures and create a record number. e.g
.,
> results would be summit like = 1, ~/pix/1.jpg, mypic. where 1 is the
> autonumber from the count and ~/pix/1.jpg is the image location and mypic
is
> the caption. the table select script is below with all the colums to be
> returned.
> SELECT Pic, Cap
> FROM Pics
> WHERE UName = @.UName
> there are only two columns in the table except for the key which is pId.
> If anyone can help I would be grateful.
>
>|||I meant, SELECT TOP 20 Pic, Cap...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Itzik Ben-Gan" wrote:
> Eamon, what determines "first"?
> If it's pId, then:
> DECLARE @.T TABLE
> (
> rn INT NOT NULL IDENTITY,
> Pic <datatype>,
> Cap <datatype>
> );
> INSERT INTO @.T
> SELECT Pic, Cap
> FROM Pics
> WHERE UName = @.UName
> ORDER BY pId;
> SELECT * FROM @.T;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Eamon" wrote:
>

Maintinence Jobs vs Defag Jobs

Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I also
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.
Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:

>Quick question, I have a maintinence job that optimizes (reorganizes back to
>original size my databases back to the original amount of free space). I also
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.
|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
to[vbcol=seagreen]
also[vbcol=seagreen]
that
>

Maintinence Jobs vs Defag Jobs

Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I also
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:
>Quick question, I have a maintinence job that optimizes (reorganizes back to
>original size my databases back to the original amount of free space). I also
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
> >Quick question, I have a maintinence job that optimizes (reorganizes back
to
> >original size my databases back to the original amount of free space). I
also
> >have jobs that defrag specific tables.
> >
> >Will the maintinence job defrag as well as reorgainize.
> >
> >I would like to get rid of my defrag jobs and have one maintinence job
that
> >does both.
>

Maintinence Jobs vs Defag Jobs

Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I als
o
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:

>Quick question, I have a maintinence job that optimizes (reorganizes back t
o
>original size my databases back to the original amount of free space). I al
so
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.
4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
>
to[vbcol=seagreen]
also[vbcol=seagreen]
that[vbcol=seagreen]
>