in

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.logical_reads

                , 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

 

*note*

cross posted from http://statisticsio.com

 

Comments

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 http://statisticsio.com. 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 http://linkedin.com/in/jasonmassie or jmassie@terremark.com
Powered by Community Server (Commercial Edition), by Telligent Systems