SQL Server Blogs

Voices from the UK SQL Server Community

Jason Massie's SQL blog

A quick and dirty WTF???!!!11one

You can use this to get a quick and dirty picture of what is going on with the server. We basically grab a snapshot, wait a second and then get a diff. Sort as needed.

 select r.cpu_time


                , r.session_id

into #temp

from sys.dm_exec_requests as r


waitfor delay '00:00:01'


select substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)  else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text

                , r.cpu_time-t.cpu_time as CPUDiff

                , r.logical_reads-t.logical_reads as ReadDiff

                , r.wait_type, r.wait_time

                , r.last_wait_type

                , r.wait_resource

                , r.command

                , r.database_id

                , r.blocking_session_id

                , r.granted_query_memory,r.session_id

                , r.reads

                , r.writes

                , r.row_count

                , s.[host_name]

                , s.program_name

                , s.login_name

from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r

on s.session_id =r.session_id and s.last_request_start_time=r.start_time

left join #temp as t on t.session_id=s.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h

where is_user_process = 1

order by 3 desc


drop table #temp



cross posted from



No Comments

About JasonMassie

Jason is a SQL Server Consultant for the professional services organization of Terremark (Formerly Data Return LLC) where he has worked for the last 8 1/2 years. Jason is an MCITP Database Administrator\Database Developer as well as an MCDBA on 7.0 and 2000. You can read his blog at He lives in Irving, TX. He enjoys time with his wife and three daughters as well as making electronic music. He can be reached at or
Powered by Community Server (Commercial Edition), by Telligent Systems