Data World

SQL Server Tuning : Top 10 CPU Intensive Queries

Posted by Pramod Singla on March 12, 2017


Problem Statement: Script to find top 10 CPU intensive queries.

Solution:

SELECT TOP 10 Object_name(qt.objectid)
AS ‘SP Name’,
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 statement_text,
total_logical_reads,
qs.execution_count
AS ‘Execution Count’,
total_logical_reads / qs.execution_count
AS ‘AvgLogicalReads’,
qs.execution_count / Datediff(minute, qs.creation_time, Getdate())
AS
‘Calls/minute’,
qs.total_worker_time / qs.execution_count
AS ‘AvgWorkerTime’,
qs.total_worker_time
AS ‘TotalWorkerTime’,
qs.total_elapsed_time / qs.execution_count
AS ‘AvgElapsedTime’,
qs.total_logical_writes,
qs.max_logical_reads,
qs.max_logical_writes,
qs.total_physical_reads,
qt.dbid,
qp.query_plan
FROM   sys.dm_exec_query_stats AS qs
CROSS apply sys.Dm_exec_sql_text(qs.sql_handle) AS qt
OUTER apply sys.Dm_exec_query_plan(qs.plan_handle) qp
WHERE  qt.dbid = Db_id() — Filter by current database
ORDER  BY totalworkertime DESC

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: