My Favorite Query For Investigating SQL Server Performance

  • Article

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, 
 s.host_name, 
 r.status,
 r.wait_type,
 r.wait_time,
 r.last_wait_type,
 r.total_elapsed_time,
 r.logical_reads,
 r.reads,
 r.writes,
 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,
 qp.query_plan
 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',
 'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
 'DISPATCHER_QUEUE_SEMAPHORE',
 'SQLTRACE_FILE_BUFFER',
 'BROKER_RECEIVE_WAITFOR',
 'XE_DISPATCHER_WAIT',
 'CHECKPOINT_QUEUE',
 'BROKER_EVENTHANDLER',
 'CLR_AUTO_EVENT',
 'FT_IFTS_SCHEDULER_IDLE_WAIT',
 'ONDEMAND_TASK_QUEUE',
 'XE_TIMER_EVENT',
 'REQUEST_FOR_DEADLOCK_SEARCH',
 'WAITFOR',
 'LAZYWRITER_SLEEP',
 'BROKER_TASK_STOP',
 'LOGMGR_QUEUE',
 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
 'SLEEP_TASK',
 'BROKER_TO_FLUSH',
 'WAIT_XTP_OFFLINE_CKPT_NEW_LOG','SP_SERVER_DIAGNOSTICS_SLEEP') 
 and (s.wait_time_ms - t.wait_time_ms) > 0
 order by wait_time_ms_per_sec desc