DataSentinel

DataSentinel

Friday, April 06, 2018

Handy - SQL Scripts to show high CPU usage

The first query will order the results based on the queries that have used the most CPU time since the SQL Server instance has been restarted (or the server has been rebooted). The second query orders the results based upon the average CPU time that each query takes.

— Find queries that take the most CPU overall
SELECT TOP 50
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,TextData = qt.text
,DiskReads = qs.total_physical_reads --The worst reads, disk reads
,MemoryReads = qs.total_logical_reads -- Logical Reads are memory reads
,Executions = qs.execution_count
,TotalCPUTime = qs.total_worker_time
,AverageCPUTime = qs.total_worker_time/qs.execution_count
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time DESC
— Find queries that have the highest average CPU usage
SELECT TOP 50
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid)
,TextData = qt.text
,DiskReads = qs.total_physical_reads — The worst reads, disk reads
,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads
,Executions = qs.execution_count
,TotalCPUTime = qs.total_worker_time
,AverageCPUTime = qs.total_worker_time/qs.execution_count
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time/qs.execution_count DESC

3 comments:

soumya said...

This blog is really good I would like to say thanks please share more content on MSBI Online Training

Abdur Rahim said...



This post is very useful for me..Nice Blog!

Server 2016 Hyper-V Experts

abdulazizcafiero said...

Wynn Las Vegas Casino & Resort - Dr.MCD
› Las-Vegas-Casinos › 구미 출장마사지 Las-Vegas-Casinos The Wynn Las Vegas is the flagship property of Wynn 서울특별 출장마사지 Resorts. 제주 출장샵 Located on the northern end of the Las Vegas Strip, it's home to some of the 안동 출장마사지 city's most fabulous 부산광역 출장안마