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 ?

  • 5
    How slow is slow? Slow to return 1 row, slow to return 1000 rows, slow to return 100k rows? Aug 30, 2011 at 16:12
  • I'd consider finding out when I could start deleting (or moving) obsolete or unnecessary rows out of the table. (I'm also really curious to know what's in that table.) I would have made this a comment, but I don't have the rep. Sorry. Aug 30, 2011 at 22:49

3 Answers 3


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?


Once you're ready to start building reports on that data an alternative (or supplemental) approach would be to pre-aggregate your data. If the reports don't need the same level of detail as the table then it would be a good idea to do periodic loads to some snapshot tables or an SSAS cube that the reports would directly connect to.

Directly reporting off of a table that size with that much growth I suspect will be problematic no matter how much tuning you do. Performance tuning with indexes & partitions will only get you so far and will work best if the table is used primarily for either writing or reading, not both as you'll have conflicting needs.

  • For AdHoc reporting SSAS is a good solution, but from my past experience SSRS off SSAS is possible but a pain (the integration is still not good) as soon as you start with reports that go beyond basics (parameter and so on...)
    – nojetlag
    Aug 31, 2011 at 6:29
  • Out of curiosity are you referring to 2005 or 2008? With the most recent version of SQL Server I haven't come across major issues like that and have actually found that a well designed SSRS report pulling from a well designed SSAS cube can be extremely fast. Aug 31, 2011 at 18:16

Check your setting for parallelism. Check your volume set up on the SAN, know how your volumes map to spindles. How's your tempdb set up?

Don't forget to check to obvious stuff too, make sure your NIC's are giving you the speed you think they should, I've lost track of the times I've seen GBit cards set to auto that negotiated 100MB speed. And your HBA settings too.

Depending on your processor, are you using hyper-threading? There's still a lot of debate whether it is good/bad.

What else is the server doing? Is there some other query causing your cache to get dumped? Perfmon the server for some extended periods.

  • I query the data from another server that has an 10GBE interconnect to the database server. So far I haven't seen anything that points to the network connection being a bottleneck. As far as I can see hyper-treading is turned off (4 cpus on Node 0 and 4 on Node 1). SAN I will investigate
    – nojetlag
    Aug 31, 2011 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.