SQL Server 2008, cpu 100% until recreate an index

Question

I have a 4000000 lines table (SQL Server 2008). This table has a private key composed of two int columns. One corresponds to a PK in another table of the same database. There is a relation between them. The other to a PK of a table in another database. (I don't know if it is important)

Sometimes, there are the same amount of queries as usualy, but the CPU usage grows to 90-100%. And some stored procedures on this table are too long. At this moment, just SELECT queries run on this table.

If I wait, the CPU usage gets normal after 1 or 2 hours. But I also can drop and recreate the PK index. And the CPU usage gets normal immediately. So I think the problem is related to this index. Am I wrong ?

But the index is not fragmented. I rebuild it every night in a maintenance plan. I even tried to reorganise it after each big insert or delete operations. I also tried to update its statistics. And I also tried to do nothing at all.

I get this problem randomly every day but at different hours, without any way to reproduce it. So I don't know how I could compare the execution plans.

Thanks,

Loic

EDIT : This table is populated with a SSIS package. It inserts missing lines and delete deprecated lines (no update). The insert is done by fast load within an oledb destination limited to 500 lines per batch. It does a bulk insert. I tried adding several things at the end of this package including reorganize indexes and update statistics.

The issue does not occure during the execution of this SSIS package. It can be for example 15 minutes after its end. It can occure even if there ar few lines manipulated by the package (50 lines for example).

Answer 1

My gut guess is that the CPU usage dropping after recreating the PK is coincidental, especially since you believe the index is not fragmented. My guess is there's nothing wrong with the index, per se. More likely, it's the lack of indexes to satisfy the particular queries / procs executing against that table.

What you need to do is monitor the server. This is 2008 so you have many tools available to you and you can go about this a couple different ways.

It sounds like this is happening frequently throughout the day, so observing a Profiler trace and sorting on CPU can be the quickest way to identify what queries / procs are hammering CPU at that particular moment. (SQL Trace is the server side version of the Profiler GUI - this might be a better option if you need to trace for an extended period of time, but that is a different conversation.)

Along the same lines and a bit more accurate would be to correlate a Perfmon trace with a Profiler trace and then you can see exactly what was running when your CPU spiked. (You can google that up for instructions.)

Another approach would be to look at the execution DMV's (see link below) and again sort descending on total_worker_time as that relates to CPU cost.

These approaches will narrow down the queries / procs you need to focus on and from there you need to look at the excecution plan and determine if you can add indexes, break up the query into smaller pieces, etc...

Here are some links to get you started:

http://sqlserverperformance.wordpress.com/2012/02/28/sql-server-20082008-r2-diagnostic-queries-for-march-2012/

http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx

Answer 2

If sounds to me like your statistics are becoming stale which is causing the SQL Server to use a crappy execution plan to get to the data. Can you post the execution plan for when the query is working well and when it is working poorly?

As the table has 4M rows in it, the statistics won't update automatically until ~800,500 rows have been inserted/update/deleted so if you pump in 600k rows the stats will stay the same and the execution plan won't be correct for the current data distribution anymore.

By dropping the index and recreating it you are effectively updating the statistics.

Next time the problem happens try updating the statistics manually using the UPDATE STATISTICS statement to see if that does the trick. If it does then that's the problem and you need to look into writing a job which updates the statistics on a regular basis.