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

4 comments:

soumya Teja said...

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

meena resma said...

I feel satisfied to read your blog, you have been delivering a useful & unique information to our vision even you have explained the concept as deep clean without having any uncertainty, keep blogging. SQL server dba Online Training

Abdur Rahim said...



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

Server 2016 Hyper-V Experts

Sukana Murtaja said...


We take the frustration out of parking management for Apartments, Home Owners Associations, Condominiums, Townhouses Co-ops, and other parking areas.Webparkingsoftware.com has reinvented how parking management should be done. Our software was built from the ground up for: Property Managers, Apartment Managers, Board members, Commercial property and anyone involved in overseeing a managed parking environment.

Some features include:

• Full Parking Management System for any size building
• Issue professional parking permits
• Assign parking spaces for tenants
• Allocate guest parking and special event parking
• Ticket Violators
• Process and Pay fines online
• Track and Manage all properties, occupants and tenants

parking permmits
text message to tenants
Cloud based parking management
software for condominiums