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.
- 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?