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 single query which is consuming a larger amount of CPU than I would expect in SQL 2008. I am not able to determine why the CPU is spiking for this single query.

I can trigger an application to make a query and watch the CPU spike, typically between 40-80%, which is much higher than any other similar queries executing at the same time or when I execute this same query by hand. In the Activity Monitor I see the spike occur and I see this single query pop up to the top of the Recent Expensive Queries list. It will show only a handful of executions/min but will show several hundred for its CPU ms/sec (250-500 on average). The logical reads is somewhere around several thousand. The duration is typically 600 or so. I have ran SQL Profiler and examined the logs to find the query being executed. However, I'm not able to replicate the issue calling this query myself. I can execute the query just as SQL Profile shows it, or execute it manually, and the CPU will not spike and the query never shows as a Recent Expensive Query. It only spikes when the consuming applications makes the call.

The query is a sproc that joins a few large select statements together. When I run this sproc manually I see very little cost in terms of CPU and I get results in less than a second. Examining the Profiler logs this sproc appears to be taking roughly 4 seconds to execute.

I have gone as far as to fire up duplicate images of the database and the application server. However, making these requests from this duplicate environment doesn't replicate the issue, nor does testing in our staging, dev or local environments.

Has SQL improperly cached the query execution or something to this effect? I recently added a couple indexes to the tables involved in this query and the execution plan I see from the Activity Monitor shows that the execution plan was updated for this change (no longer recommends these indexes). This sounds dumb, but do I need to restart the SQL Server or something? Or is there a way to better replicate the query that is coming into the server than pulling the query from the Profile trace?

share|improve this question
    
Could it be this: sommarskog.se/query-plan-mysteries.html –  Aaron Bertrand Sep 21 '12 at 22:18
1  
Yup. That's exactly what was happening, Aaron. –  Sleepwalker Sep 21 '12 at 22:37

1 Answer 1

up vote 1 down vote accepted

Perhaps we can pull out the execution plan when it seems that particular query is running slow and compare it to the execution plan for when you run it. If you recently updated indexes it might still have the old exec plan cached, however, it does not answer the question as to why it runs fast when you execute the sproc from SQL Server, unless you're using different parameters.

Did you check if AUTO_UPDATE stats is on?

Is your tempdb optimized? Sometimes PAGELATCH_SH issues will be intermittent.

Can you view the wait stats, clear them, run the query, and then see the wait stats for when it was slow?

Viewing the plan cache: http://www.sqlteam.com/article/what-query-plans-are-in-sql-server-memory

share|improve this answer
    
Auto Update Statistics is ON. tempdb is pretty much in its default settings. I have viewed the waits and don't see anything holding the query up. What were you thinking I should look for specifically in the plan cache? I see the query in the cache and it is about 3MB in size. –  Sleepwalker Sep 21 '12 at 21:53
    
Actually I re-read your question and TempDB contention wouldn't make sense. Your query consistently runs slow from 1 host with very high cpu cost, however the exact same query consistently runs fast without the CPU spike. If you've done sufficient testing to stand by that, then I'm stumped as to why SQL would require more CPU when it's coming from a certain host. Have you tried executing on the trouble host, executing the query directly against the SQL server using OSQL or SSMS? If that is consistently fast we're getting closer to the root cause. –  Ali Razeghi Sep 21 '12 at 21:58
    
I just got it fixed. I cleared the cache for that query and it is now performing optimally again. Thanks for all the suggestions above. This was very helpful in getting to the root of the problem. –  Sleepwalker Sep 21 '12 at 22:33
    
Very happy to hear clearing the cache resolved the issue! –  Ali Razeghi Oct 19 '12 at 21:49

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.