A high level view of the most costly queries by CPU

I want to see at a high level i.e at batch or stored procedure level, not at the individual statement leve, which queries are most costly in terms of CPU. To do that I need to aggregate CPU used by all statements within a batch or stored procedure (i.e. sharing same plan_handle). And then in next step I will drill down to individual statements within them with the largest overall CPU usage.

Note: The total_worker_time/cpu time is in microseconds so 1000000 = One Second

Also note that for demo purpose I am only looking at top 5 queries with total execution count of at least one and have their execution plans still cached. Generally I would look for queries that are executed more often so you may want to adjust it fit activity level/type in your particular SQL instance.

IF Object_id('tempdb..#top_costly_queries') IS NOT NULL DROP TABLE #top_costly_queries;
go 
;WITH cte AS 
(
SELECT TOP 5 
Sum(qs.total_worker_time)  total_cpu_time,
Sum(qs.execution_count) total_execution_count,
Sum(qs.total_worker_time) / Sum(qs.execution_count) [avg_cpu_time],

Sum(qs.total_elapsed_time) / Sum(qs.execution_count) [avg_elapsed_time],

Count(*) number_of_statements,
qs.plan_handle
FROM sys.dm_exec_query_stats qs
GROUP BY qs.plan_handle
HAVING Sum(qs.execution_count) > 1
)
SELECT
Db_name(qp.dbid) db_name,
COALESCE(Quotename(Db_name(qp.dbid)) + N'.' + Quotename(Object_schema_name(qp.objectid, qp.dbid)) +
N'.' + Quotename(Object_name(qp.objectid, qp.dbid)), '') obj_name,
qp.objectid,
qp.query_plan,
cte.*
INTO #top_costly_queries
FROM cte
 CROSS apply sys.Dm_exec_query_plan(cte.plan_handle) qp
ORDER BY total_cpu_time DESC;

Since a batch or stored procedure may have more than one query, using that list (that I stored into a temp table), I now need to drill down and find the specific queries with the highest CPU usage.

-- lets drill down to the statement level
SELECT t.*,
       Substring(st.text, ( qs.statement_start_offset / 2 ) + 1,(
(CASE qs.statement_end_offset WHEN -1 THEN Datalength(st.text)
ELSE qs.statement_end_offset END -
qs.statement_start_offset ) / 2 ) + 1)  statement_text,
qs.total_worker_time / qs.execution_count [avg_cpu_time_stmt],
       qs.*,
       st.*
FROM   sys.dm_exec_query_stats qs
       INNER JOIN #top_costly_queries t ON t.plan_handle = qs.plan_handle
       CROSS apply sys.Dm_exec_sql_text(qs.sql_handle) AS st
ORDER  BY qs.total_worker_time DESC

Partial Results:

So there is one outlier that needs further investigation and for that the first step for me would be to click on the query_plan link and review its execution plan.

Generally, whether the results show an actual problem query depends on overall and average elapsed per execution, overall time spent by the query on CPU, I/O etc. and overall performance of the server and the SQL server queries in general. I know it gets old to say it but it takes a wholistic approach to keep your SQL Server running optimally.

A high level view of the most costly queries by CPU

Rate

Share

Share

Rate