Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
4  
How slow is slow? Slow to return 1 row, slow to return 1000 rows, slow to return 100k rows? –  Mark Storey-Smith Aug 30 '11 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. –  Witness Protection ID 44583292 Aug 30 '11 at 22:49

3 Answers 3

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.

share|improve this answer
    
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 '11 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. –  Matt Donahue Aug 31 '11 at 18:16

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?

share|improve this answer
2  
+1 for look at the execution plan. Without that everyone here is guessing. –  mrdenny Aug 31 '11 at 0:34

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.

share|improve this answer
    
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 '11 at 7:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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