Sp_who2 is not used by this DBA.  That info is amazingly good to have, but I want more.  Don’t get me wrong, sp_who2 is kinda hidden in this one as it’s really just a proc that does little more than “SELECT * FROM SysProcesses“, and I use SysProcesses where I couldn’t find the same info in the DMVs that were supposed to replace it.  Don’t go thinking that SysProcesses is just a depreciated dinosaur, even MVPs still use it: http://sqlblog.com/blogs/kalen_delaney/archive/2008/06/29/why-i-still-need-sysprocesses.aspx

Threads and RunningThreads are new for me here.  It’s good to see when a query is split into 100 threads and only 1 is doing anything.  It’s also easier on the eyes to see the number 100 instead of seeing 100 separate rows for one SPID.

The percent complete and estimated completion time on this script are typically blank, with the major exceptions being backups and restores where these columns are the best thing ever!

BlockedBy is something overlooked by many people.  If your query seems to be running forever when it normally runs quick, there’s about a 75% chance you’re just waiting for another process to complete. It’s also why you’ll see the notorious (nolock) in almost everything I write for myself, but that’s another conversation.  The HeadBlocker attempts find the start of a blocking chain, although I can say that I know my logic here isn’t bulletproof.

My wait types used to be perfect where if there wait time was 0 then it was lowercase, otherwise it was all caps.  It seems I broke it slightly, but it’s almost perfect.  My OCD will eventually get the best of me here, but it has to contend with time with the rest of my life.

The rest is pretty self-explanitory with the exception of query plans.  They are the last line of the SELECT and FROM clauses that are commented out.  It’s somewhat rare to need the plan, and it’s not too uncommon to have this script run noticably slower when looking for plans.  Because of that, I uncomment that column and the the cross apply that makes it possible only when needed.

SELECT SPID = er.session_id
    , ot.Threads
    , RunningThreads = coalesce(rsp.RunningThreads,0)
    , Pct_Comp = er.percent_complete
    , Est_Comp_Time = CASE er.estimated_completion_time WHEN 0 THEN NULL ELSE dateadd(minute, (er.estimated_completion_time/60000), getdate()) END 
    , er.status
    , er.command                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
    , database_name = sd.name
    , BlockedBy = wt.blocking_session_id
    , HeadBlocker = coalesce(hb5.session_id, hb4.session_id, hb3.session_id, hb2.session_id, hb1.session_id)
    , wait_type = coalesce(CASE er.wait_type WHEN 'CXPACKET' THEN 'CXPACKET - ' + sp.lastwaittype1 ELSE sp.lastwaittype1 END, lower(er.last_wait_type)) --Lowercase denotes it's not currently waiting, also noted by a wait time of 0.
    , Wait_Time_Sec = Cast(er.wait_time/1000.0 as DEC(20,3))
    , er.wait_resource
    , Duration_Sec = Cast(DATEDIFF(s, er.start_time, GETDATE()) as DEC(20,0))
    , CPU_Sec = Cast(er.cpu_time/1000.0 as DEC(20,3))
    , LogicalReads_K = Cast(er.logical_reads/1000.0 as DEC(20,0))
    , PhysicalReads_K = Cast(er.reads/1000.0 as DEC(20,3))
    , Writes_K = Cast(er.writes/1000.0 as DEC(20,3))
    , [Statement] = SUBSTRING (st.text, er.statement_start_offset/2+1,
        abs(CASE WHEN er.statement_end_offset = -1
               THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2 
            ELSE er.statement_end_offset END - er.statement_start_offset)/2+1)
    , st.text as Query
    , es.login_time
    , es.host_name
    , program_name = CASE LEFT(es.program_name, 29)
                    WHEN 'SQLAgent - TSQL JobStep (Job '
                        THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(es.program_name,32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(es.program_name, 67, len(es.program_name)-67)
                    ELSE es.program_name
                    END  
    , es.client_interface_name
    , es.login_name
    , es.status
    , es.total_scheduled_time
    , es.total_elapsed_time
    , er.start_time
    , es.last_request_start_time
    , es.last_request_end_time
    , cursor_statement = cst.text
    , cursor_create = c.creation_time
    , cursor_dormant = c.dormant_duration
    , Req_memory_mb = mg.requested_memory_kb / 1024 
    , Grant_memory_mb = mg.granted_memory_kb / 1024
    , Memory_wait_sec = cast(mg.wait_time_ms / 1000.0 as DEC(20,1))
    --, qp.query_plan 
FROM sys.dm_exec_requests (nolock) er
    INNER JOIN sys.dm_exec_Sessions (nolock) es on er.session_id = es.session_id
    LEFT JOIN sys.databases (nolock) sd on er.database_id = sd.database_id
    INNER JOIN (SELECT session_id, count(1) Threads FROM sys.dm_os_tasks (nolock) GROUP BY session_id) ot on er.session_id = ot.session_id
    LEFT JOIN (SELECT spid, LastWaitType1 = MIN(lastwaittype), LastWaitType2 = MAX(lastwaittype) FROM master.dbo.sysprocesses (nolock) sp WHERE waittime > 0 AND lastwaittype <> 'cxpacket' GROUP BY spid) sp ON er.session_id = sp.spid
    LEFT JOIN (SELECT spid, RunningThreads = COUNT(1) FROM master.dbo.sysprocesses (nolock) sp WHERE waittime = 0 GROUP BY spid) rsp ON er.session_id = rsp.spid
    LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt WHERE wt.blocking_session_id <> wt.session_id GROUP BY session_id) wt ON er.session_id = wt.session_id 
    LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb1 ON wt.blocking_session_id = hb1.session_id 
    LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb2 ON hb1.blocking_session_id = hb2.session_id 
    LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb3 ON hb2.blocking_session_id = hb3.session_id 
    LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb4 ON hb3.blocking_session_id = hb4.session_id 
    LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb5 ON hb4.blocking_session_id = hb5.session_id 
    LEFT JOIN sys.dm_exec_cursors(0) c ON er.session_id = c.session_id
    LEFT JOIN sys.dm_exec_query_memory_grants (nolock) mg ON er.session_id = mg.session_id
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st  
    OUTER APPLY sys.dm_exec_sql_text(c.sql_handle) AS cst
    --CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE er.session_id <> @@SPID
    --AND es.host_name like '%%'
    --AND er.session_id = 63
ORDER BY /*35 desc,*/ er.percent_complete DESC, er.cpu_time DESC, er.session_id
--Use the below command to get the last input of an open session id
--dbcc inputbuffer(58)

Update – 2013-08-08 – I now have physical and logical reads separate.  I made a mistake and only grabbed physical reads simply returning it as “reads”.  It was used that way for years without ever noticing the issue.  While I was in here I also added the amount of memory used by each query and a commented out section of the ORDER BY clause to sort by it; this was added during an incident with memory contention and proved to be very useful.

Update – 2013-08-29 – Estimated Completion Time is now done by dividing it by 60,000 to convert milliseconds to minutes then doing the dateadd function. The old way was right, but when your Estimated Completion Time is 3 months out then you can’t do an implicit conversion in dateadd to put your bigint holding milliseconds into an int field when it will take 3 months for your restore to complete. LOL, I’m glad I’m not the one who was doing the project where that problem came up!!!

Related articles

• SQL Server Running Slow (simplesqlserver.com)

Filed under: Scripts, SQL Server, System State Tagged: dm_exec_query_plan, dm_exec_requests, dm_exec_sessions, dm_exec_sql_text, dm_os_tasks, dm_os_waiting_tasks, Executing, Processes, Running, Sessions, sysprocesses