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've posted this over at ServerFault but didn't have much luck getting a response.

I have a fairly large SQL Server database (~2TB). Most of the data is in one table (~6 billion rows).

Two weeks ago I dropped two non-clustered indexes on the large table and migrated the data onto a single 6TB RAID SSD array. I then recreated the two indexes which took quite some time (assuming because I currently have the data (for table and indexes) and log on the same array and it seems that with the RAID I can't have fast sequential AND random r/w at the same time).

Anyway after recreating the indexes it ran very well for about a week. During the week I have been slowly running a clean on the large table which just removes old unneeded rows. So far I've removed about 300 million out of the 6 billion, and at a guess I still have a lot more to go.

Now after about a week of running like this it is now running very slow and I'm not sure which would be best to do.

Current situation:

  • Dual Xeon
  • 192GB RAM
  • Windows Server 2012 with SQL Server 2012
  • CPU is hitting 100% (16 cores) - was only using about 50% prior to the slowdown
  • IO doesn't seem to working too hard (no queue)

Large Table currently has (I don't have any fragmentation info prior to now):

  • 1x Clustered index: 48% fragmentation
  • 1x Non-clustered index: 36% fragmentation
  • 1x Non-clustered index: 10% fragmentation
  • I used to have two more indexes on this table but dropped them a while ago

What do you think would best fix my problem

  • Rebuild the non-clustered indexes on the same array (assume that this should fix the problem but will take a long time to do as it did before. Will probably have the same problem in the near future as I'm still cleaning the table)
  • Rebuild the non-clustered indexes on a new RAID array (should fix as above but may be faster)
  • Move the non-clustered indexes to a new RAID array (quickest option)
  • Recreate the two old indexes on a new RAID array (not sure if this relieves CPU or IO pressure)

Do fragmented indexes cause higher CPU usage?

Is there anything else I could be missing?

TIA

share|improve this question
3  
Have you tried updating statistics? –  Jon Seigel Oct 4 '13 at 13:05
    
You can try to recaculte the execution path. This can help if the execution path was created when the statisics where not upto date. –  peer Oct 4 '13 at 13:36
    
How much of the two TB is used on a day to day basis? –  Sebastian Meine Oct 4 '13 at 13:36
    
@SebastianMeine the full 2TB is used day to day –  A-Kay Oct 4 '13 at 23:09
    
@JonSeigel I actually haven't but for the indexes I created it is set to auto recompute statistics –  A-Kay Oct 4 '13 at 23:24
show 2 more comments

2 Answers

Instead of looking at indexes, start looking at what queries are causing the high CPU use. Start with Michael J. Swart's DMV queries to find the top CPU-using queries. Look at what indexes the queries are using, whether they're doing things like implicit conversion, etc. But generally no, fragmentation by itself probably isn't doing this.

share|improve this answer
    
The problem I have is all queries were written by a 3rd party and cannot be changed. It was working great for a week, just want to get back to this point. Would adding indexes reduce CPU load? –  A-Kay Oct 4 '13 at 12:48
3  
@A-Kay Nobody can answer that question for you. You need to look in the query stats and see what your top CPU consuming queries are, and by looking at the plan you can determine whether or not DDL can alleviate CPU consumption. –  Thomas Stringer Oct 4 '13 at 13:08
1  
Went through and had a proper look at those queries and they're great. Lots of useful info in there. Thanks! –  A-Kay Oct 4 '13 at 23:14
add comment

You could have a look at the missing indexes info, and the current usage of existing index.
I use this sproc from Karaszi myself.

Also take the advice from Brent and look at the SQL that use CPU. You may improve their execution plan by adding (or updating existing) index.

share|improve this answer
    
Great, thanks Henrik. Very useful. –  A-Kay Oct 5 '13 at 0:02
add comment

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.