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?