Labels

TOP 5 CPU-CONSUMING STATEMENTS In SQL Server




               To quickly identifying which query is consuming more CPU can be found using below query.


/************************************/
SELECT TOP 5
qs.total_worker_time/(qs.execution_count*60000000) as [Avg CPU Time in mins],
qs.execution_count,
qs.min_worker_time/60000000 as [Min CPU Time in mins],
--qs.total_worker_time/qs.execution_count,
SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as query_text,
dbname=db_name(qt.dbid),
object_name(qt.objectid) as [Object name]
FROM
sys.dm_exec_query_stats qs cross apply
sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
[Avg CPU Time in mins] DESC
/************************************/


1 comment:
Write comments
  1. This lists those that have top 5 AVERAGE cpu consumption from all executions -- so if there's a statement that has been executed once and took 2 minutes it's higher in the list than statement executed 1000 times and taking 1 minute each time -- so to my mind this is the wrong order to look at things, but of course you could use this for something.

    ReplyDelete

Please do not enter spam links

Services

More Services