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.