Top Queries by Average CPU Time
When it comes to CPU, for me Perfmon is still the primary tool for tracking down fundamental CPU usage and I feel it should remain so for you. However, from time to time we need to track down which process is using a physical CPU. When DBA’s ask me why SQL Server using all the CPU, I ask them the first question – are you sure SQL Server is the process utilizing CPU onserver.
Just for the records, I typically read and used the kernel debugger to determine what process is using which exact CPU. For a primer take a look at how to use XPerf tool on MSDN Blogs.
Though these tools are powerful, there is a considerable learning curve for a novice DBA. So let me come back to favorite tool of choice – SQL Server Management Studio. Let us start to look at the Top Queries by Average CPU Time report. The output has two sections: a) Graph Section and b) Details Section.
The first Graph section is a color palette sorted in descending order the Top queries that are consuming Average CPU Time and Total CPU Time. For me the next section has more interesting details to analyze. Before I jump there – there is an important note on the top of the report that is worth a look. It reads as:
Note: This report identifies the queries currently residing in the plan cache that have consumed the most total CPU time over the course of all their executions. This data is aggregated over the lifetime of the plan in the cache and is available only for plans currently in the cache.
This means that the values will get reset if SQL Server has been restarted because the cache gets flushed and emptied. In your active transactional system this will never be the case and I am sure you will know where to start your tuning exercise when SQL Server is utilizing higherCPUs.
As I mentioned, the second section is of higher interesting values to look at. As you can see, the Top 10 CPU consuming queries are listed and we can start investigating what is the resource utilization of each of these queries and how we can tune them. The label of the colors (1, 2, 3 etc.) are “Query No.” column in the table. It is quite possible that the order in the second graph may not be same as first graph.
In the above picture we can see that the Top most expensive query is utilizing close to 1.774 seconds to execute and on an average of 2 executions it is taking close to 0.887 seconds for each execution. The query under question is also highlighted under the Query Text.
Top Queries by Total CPU Time
Since the output is almost similar to what we discussed before. The queries are sorted by Top CPU time now. That is the only difference. The rough DMV’s used for this report would be:
SELECT TOP(10)creation_time, last_execution_time, (total_worker_time+0.0)/1000 AS total_worker_time, (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime], execution_countFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(sql_handle) stWHERE total_worker_time > 0ORDER BY total_worker_time DESC
Before I sign off on CPU utilization, I have always relied on this report for CPU workloads. But as a primer there are obvious candidates that I always look out to when CPU is high like:
- Query Execution and Parallelism
- Compiles and recompiles on the server
- Any tracing if enabled in the system – includes running of Activity Monitor somewhere or Profiler (we can use sys.traces to check traces which are configured to run)
- If any anti-virus is running in the system.
- Invalid drivers for SAN, BIOS or other components
These are some great starting points to eliminate before going into tuning queries for a CPU deprived system.
Performance – Top Queries by Average IO
As described for the CPU output, the output is almost the same here too with two sections. The difference is, it has been sorted by Average IO utilization.
The meat of information is available in section 2 as usual.
This section apart from CPU values, has also the values of Logical Reads, Logical Writes. As you can see, Total Logical IO = Logical Reads + Logical Writes
The query to get this information would be:
SELECT TOP 10
creation_time, last_execution_time, total_logical_reads AS [LogicalReads], total_logical_writes AS [LogicalWrites], execution_count, total_logical_reads+total_logical_writes AS [AggIO], (total_logical_reads+total_logical_writes)/(execution_count+0.0) AS[AvgIO], st.TEXT, DB_NAME(st.dbid) AS database_name, st.objectid AS OBJECT_IDFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(sql_handle) stWHERE total_logical_reads+total_logical_writes > 0AND sql_handle IS NOT NULLORDER BY [AggIO] DESC
It is important to understand that IO for a query is a SUM of both Logical IO and Physical IO. Irrespective of where the data resides the IO is calculated as a Union of these two values.
Performance – Top Queries by Total IO
This output is identical to the previous output with the only difference of it being sorted by the Total IO parameter. The sections and output are exactly same as above reports.
0 comments:
Post a Comment