1

I have a fairly large MS SQL 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

A-Kay
  • 19
  • 1

1 Answers1

0

Based on the amount of fragmentation you've got on your indexes you should go ahead and rebuild them. Any index with greater than 30% fragmentation should be rebuilt. I would also make sure that you update the statistics on the table regularly (rebuilding the index will do this for you automatically).

After doing that if you're still seeing really high CPU and you've confirmed that it is the SQLServr.exe process then you'll want to narrow down which queries are using so much of the CPU and troubleshoot them from there.

You could run something like the following query to get some aggregate data about the queries that have been using the most CPU: 

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
total_logical_writes as [Total Writes],
total_logical_reads as [Total Reads],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

For real-time data you could also run something like this:

SELECT er.session_id, er.cpu_time, er.reads, er.writes, 
SUBSTRING(st.text, (er.statement_start_offset/2)+1, 
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as st
WHERE session_id > 50
AND status = 'runnable'
ORDER BY cpu_time desc

You could also run both to compare the real-time to aggregate data. Both should give you an idea about what is using so much CPU. From there you'd want to find out why they're running so long. Are they doing a ton of reads or a ton of writes? If they're doing a ton of reads it may mean they're missing some indexes. Tons of writes could mean the indexes are actually the problem.

Either way keeping an eye on those statements can give you a starting point.

shiitake
  • 379
  • 1
  • 7