The query mention below will list down
top 10 top resource (CPU) consuming queries. This can be a good tool for a SQL
Server database professional to get to know how is your database doing and what
are the queries which are taking maximum CPU of the server.
Also, you can get to know if any query
is getting recompiled using the columns “execution_count” and
“plan_generation_num”.
I use this query to find the
database queries which require performance optimization and hoping this will
help to others as well.
SELECT TOP 10
Substring(qt.TEXT, ( qs.statement_start_offset /
2 ) + 1,
(
( CASE qs.statement_end_offset WHEN
-1
THEN Datalength(qt.TEXT) ELSE qs.statement_end_offset
END - qs.statement_start_offset
) / 2 ) + 1)
AS sql_text,
qs.execution_count,
qs.plan_generation_num,
qs.total_logical_reads,
qs.last_logical_reads,
qs.total_logical_writes,
qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time
/ 1000000 total_elapsed_time_in_S,
qs.last_elapsed_time
/ 1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats
qs
CROSS APPLY sys.Dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.Dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC
Query Result
