Sql server cpu utilisation
Days ago I wanted to know which query is taking lots of time and cpu utilisation for execution.
and I came to one blog from where I copied below query. It tells details of all query. You need to fire it on your SQL server to know more.
SELECT
substring(text,qs.statement_start_offset/2
,(CASE
WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)
,qs.plan_generation_num as recompiles
,qs.execution_count as execution_count
,qs.total_elapsed_time - qs.total_worker_time as total_wait_time
,qs.total_worker_time as cpu_time
,qs.total_logical_reads as reads
,qs.total_logical_writes as writes
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
LEFT JOIN sys.dm_exec_requests r
ON qs.sql_handle = r.sql_handle
ORDER BY 3 DESC
Be the first to rate this post
- Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5