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?


  • 3
    Have you tried updating statistics?
    – Jon Seigel
    Oct 4, 2013 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, 2013 at 13:36
  • How much of the two TB is used on a day to day basis? Oct 4, 2013 at 13:36
  • @SebastianMeine the full 2TB is used day to day
    – A-Kay
    Oct 4, 2013 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, 2013 at 23:24

2 Answers 2


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.

  • 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, 2013 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. Oct 4, 2013 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, 2013 at 23:14

You could have a look at the missing indexes info, and the current usage of existing index.

I use this stored procedure 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.


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.