Track down high CPU queries

Some CPU spikes on our server provided the opportunity for me to hone-in on my query investigation skills.   I have known a long time about finding high CPU queries using DMVs (sys.dm_exec_query_stats).  The downside is that you can’t see exactly when they were running, other than the last execution time.  There is not much context in terms of time.

I have a new favorite way to track the queries – server side trace.    Set a filter of > 10,000 CPU, or something appropriate to the system.

Use a query to read the results of your trace in real-time:

SELECT TOP 1000 LoginName, TextData, duration / 1000 AS Dur, 
CONVERT(VARCHAR, StartTime,109), Writes, CPU, Reads, EndTime
FROM fn_trace_gettable(N'C:\Traces\HighCPUQueries.trc',DEFAULT)
ORDER BY StartTime desc;

Open up Performance Monitor, and look at CPU in real-time.  And see how those high queries correlate with any spikes in the system.

I used this to focus on some queries that needed to be optimized.  Also, I adjusted the cost threshhold for parallelism from 5 to 20, to avoid smaller queries from using all of the processors.

This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s