Get High CPU Sessions & Queries from SQL Server


–CREATE

–procedure HighCpuSessions_in_Production

–as

 

INSERT

INTO dbo.HighCpuSessions

SELECT

GETDATE(),r.session_id,status,SUBSTRING(qt.text,

r

.statement_start_offset/2,

(

CASE

WHEN

r.statement_end_offset =1

THEN

LEN(CONVERT(NVARCHAR(MAX), qt.text))* 2

ELSE

r.statement_end_offset

END

r.statement_start_offset)/2)

AS

query_text

— this is the statement executing right now

 

,

qt.dbid

,

qt.objectid

,

r.cpu_time

,

r.total_elapsed_time

,

r.reads

,

r.writes

,

r.logical_reads

,

r.scheduler_id

FROM

sys.dm_exec_requests r

CROSS

APPLYsys.dm_exec_sql_text(sql_handle)AS qt

WHERE

r.session_id >50

order

by CPU_time desc

————————————————————————————————————————————————-

–create

–procedure TOP50ExecutedQueries

–as

 

select

top 50

(

total_logical_reads + total_logical_writes)as total_logical_io,

(

total_logical_reads/execution_count)as avg_logical_reads,

(

total_logical_writes/execution_count)as avg_logical_writes,

(

total_physical_reads/execution_count)as avg_phys_reads,

substring

(st.text,(qs.statement_start_offset/2)+1,

((

case qs.statement_end_offset

when

1 thendatalength(st.text)

else

qs.statement_end_offset

end

qs.statement_start_offset)/2)+ 1)as statement_text,

*

 

from

 

sys

.dm_exec_query_statsas qs

cross

applysys.dm_exec_sql_text(qs.sql_handle)as st

order

by

total_logical_io

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: