My Favorite Query For Investigating SQL Server Performance

I work in a lab environment, often examining running SQL workloads for performance analysis.  This is a query I've used many times to see what's running _right now_ on a server and what kind of resources it's using.  It dumps the running queries and query plans and takes a snapshot of the system wait stats.  One cool thing is that it adds CPU_TIME as a "wait stat".  It's kind of sideways to think about CPU_TIME as a wait stat, but that's what eats up the time when a session isn't in any other wait.  And is really helps you get a feel for whether the other waits you see are really a problem.  For instance 6000ms of PAGEIOLATCH_SH dosn't look so important next to 90,000ms of CPU_TIME.

The batch should take 5sec to run, as it as a 5sec WAITFOR, used seperate two snapshots of sys.dm_os_wait_stats, but it's not an expensive query.

One caviat is that I run this query on lab systems.  The mechanism it uses to aggregate CPU time relies on the @@CPU_BUSY function which doesn't return the correct value on systems that have been running for a long time.

Anyway here it is, and let me know if you can think of a more robust replacement for @@CPU_BUSY:


 select s.session_id, 
 SUBSTRING(st.text, (r.statement_start_offset/2)+1, 
   ((CASE r.statement_end_offset 
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE r.statement_end_offset END 
   - r.statement_start_offset)/2) + 1) AS statement_text,
 from sys.dm_exec_requests r
 join sys.dm_exec_sessions s
  on r.session_id = s.session_id 
 outer apply sys.dm_exec_query_plan(r.plan_handle) qp
 outer apply sys.dm_exec_sql_text(r.sql_handle) st
 where r.plan_handle is not null
   and r.session_id <> @@spid
 order by logical_reads desc
 declare @interval int = 5
 declare @waitfor varchar(50) =      cast(@interval/(60*60) as varchar(10)) +':' 
 + cast((@interval%(60*60))/60 as varchar(10)) +':' 
 + right('0' + cast(@interval%60 as varchar(10)),2)
 declare @t table (wait_type varchar(50) collate SQL_Latin1_General_CP1_CI_AS, wait_time_ms bigint )
 insert into @t
 select wait_type, wait_time_ms
 from sys.dm_os_wait_stats
 union all 
 select 'CPU_USED', @@CPU_BUSY * (@@TIMETICKS / 1000.)
 order by wait_time_ms desc
 waitfor delay @waitfor
 select t.wait_type, (s.wait_time_ms - t.wait_time_ms) / cast(@interval as float) wait_time_ms_per_sec
 from (
   select wait_type, wait_time_ms
   from sys.dm_os_wait_stats
   union all 
   select 'CPU_USED', @@CPU_BUSY * (@@TIMETICKS / 1000.)
 ) s
 join @t t
 on s.wait_type collate SQL_Latin1_General_CP1_CI_AS 
  = t.wait_type collate SQL_Latin1_General_CP1_CI_AS
 where s.wait_type not in ('DIRTY_PAGE_POLL',
 and (s.wait_time_ms - t.wait_time_ms) > 0
 order by wait_time_ms_per_sec desc