Detect highest CPU consuming queries


Create

Procedure SQL_HighCPU

as

Set

NOCOUNT

ON

————————————————

— Description: This procedure helps you detect the highest consuming CPU queries

— Compatibility: SQL Server 2005 & 2008

—————————————————

SELECT

TOP 50(a.total_worker_time/a.execution_count)as [Avg_CPU_Time]

,

Convert

(Varchar,Last_Execution_Time)as‘Last_execution_Time’

,

Total_Physical_Reads

,

SUBSTRING

(b.text,a.statement_start_offset/2

,

(casewhen a.statement_end_offset =1 thenlen(convert(nvarchar(max), b.text))* 2 else

a

.statement_end_offset end a.statement_start_offset)/2)as Query_Text,

dbname

=Upper(db_name(b.dbid)),

b

.objectid as‘Object_ID’

FROM

sys.dm_exec_query_stats a

cross

apply

sys

.dm_exec_sql_text(a.sql_handle)as b

ORDER

BY

[Avg_CPU_Time]

DESC

 

— EXEC SQL_HighCPU

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: