patternMinor
Things to consider with a bigger table
Viewed 0 times
biggerthingswithconsidertable
Problem
I have a table that has around 12 billion rows (row size 32 B) which grows each month by 300 million rows. I have setup partitions for each month and each month has 8 data files (since I currently have only 8 CPU's), to store the data I have a SAN attached.
The machine (W2008R2 64bit with MSSQL2008R2) has 192 GB RAM but I still feel it is slow when retrieving data while using the clustered index. I plan later on to use this data with SSRS. What should I consider to increase performance ?
The machine (W2008R2 64bit with MSSQL2008R2) has 192 GB RAM but I still feel it is slow when retrieving data while using the clustered index. I plan later on to use this data with SSRS. What should I consider to increase performance ?
Solution
You mentioned that you have the table (technically clustered index) partitioned.
Does your query use the clustered key(s) as part of the search predicate? If not, you are going to get scans, as opposed to seeks, against the partitioned index.
Do you have any nonclustered indexes? Are they also partition-aligned?
Have you investigated your query execution plan to see what is happening under the hood of your poorly performing query?
Could you post your DDL, sample data, and your query, so we can better help you?
Does your query use the clustered key(s) as part of the search predicate? If not, you are going to get scans, as opposed to seeks, against the partitioned index.
Do you have any nonclustered indexes? Are they also partition-aligned?
Have you investigated your query execution plan to see what is happening under the hood of your poorly performing query?
Could you post your DDL, sample data, and your query, so we can better help you?
Context
StackExchange Database Administrators Q#5206, answer score: 6
Revisions (0)
No revisions yet.