Some time we are reporting that our production server getting
slow, I mean sql query are consuming more CPU. It is bit difficult to know why
CPU consumption. Which query taking more time to execute?
With the help of below sql script we can get the top CPU
consuming sql query.
SELECT TOP 20
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
|
See the Output
