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

share|improve this question

migrated from stackoverflow.com Mar 16 '12 at 13:26

This question came from our site for professional and enthusiast programmers.

2 Answers 2

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

share|improve this answer
    
Thank you Henry I don't think CPU drop after re-creation is a coincidence. If I don't do that the CPU usage remains high for hours. If I do that after for example a few minutes it drops. I did that several times. And every time the cpu usage drops immediatly. But I agree with you : perhaps it is not the index itself. Perhaps the re-creation does something that reset something... My indexes are all created using profiler, configuration wizard and execution plan suggestions. I will try your links, it may be very useful to identify the source of my problem, thank you –  Loic T Mar 16 '12 at 14:38
    
The other thing a re-index does is update the statistics for the columns involved in the indexes. The optimizer uses statistics to build the plan. If the statistics are out of date the optimizer may build what it thinks is a good plan but in reality is not. This can lead to SQL doing extra work and thus higher CPU. This is visible in an execution plan by comparing "estimated number of rows" to "actual number of rows". –  Henry Lee Mar 16 '12 at 17:41
    
Yes I also tried to update statistics after each insertion. I edit the original message with this information –  Loic T Mar 16 '12 at 18:16

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.

share|improve this answer
    
I also tried to update stats at the end of each SSIS package execution. There is no change. I will try to update it during the problem. –  Loic T Mar 19 '12 at 10:41

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.