I’m asked often for my personal set of SQL scripts I used to diagnose problems on SQL and I do take for granted the library I’ve built over the years. At one point, I even wrote a .Net application that combined all the scripts in one place, ran them and dumped the output to Excel for later analysis. I find my library of scripts to be absolutely valuable to my daily troubleshooting needs, I just couldn’t live without them. At this point they seem natural to me, like riding a bike or walking, I really don’t think about them much at this point, I just use them when I need them. But, if you are new to SQL some of these basic scripts can be extremely valuable for a variety of reasons.

I can't begin to tell you how many times I've executed sp_who2 inside SQL Server to find out who what SPID (SQL Process ID) may be blocked and who it is blocked by. I've even took the time to geek out by comparing sp_who and sp_who2 to find the differences over the years. It's amazingly convenient to run this utility and let's face it, it's just a staple in the industry. I actually prefer it over Activity Monitor inside SQL because sometimes the Activity Monitor doesn’t respond in a reasonable time frame for me, especially when you have diminished resources on the server which is the reason you opened Activity Monitor (go figure?)!

But at some point the old tools must be retired and new and improved versions of a given tool come along. With that in Mind a wonderful SQL expert named Jason Strate built a newer version of sp_who2 using the DMV queries, a post located here: http://www.jasonstrate.com/2009/02/dmv-version-of-sp_who2/. I've taken that query and altered it slightly, adding a few columns I've found helpful. Here's the query:

SELECT
es.host_process_id
,es.session_id
,ec.net_packet_size
,ec.net_transport
,ec.local_net_address
,ec.auth_scheme
,ec.client_net_address
,ec.client_tcp_port
,ec.connect_time
,es.status
,es.login_name
,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name
,er.blocking_session_id
,er.command
,es.reads
,es.writes
,es.cpu_time
,er.wait_type
,er.wait_time
,er.last_wait_type
,er.wait_resource
,CASE es.transaction_isolation_level 
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'ReadUncommitted'
    WHEN 2 THEN 'ReadCommitted'
    WHEN 3 THEN 'Repeatable'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
    END AS transaction_isolation_level
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2,
    (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) AS query_text
,ph.query_plan
FROM sys.dm_exec_connections ec
    LEFT OUTER JOIN sys.dm_exec_sessions es 
        ON ec.session_id = es.session_id
    LEFT OUTER JOIN sys.dm_exec_requests er 
        ON ec.connection_id = er.connection_id
    OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
    OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE 
    --ec.session_id <> @@SPID
    --AND es.status = 'running'
    es.host_process_id = 4636 -- X3 sadoss.exe client process id number
ORDER BY 
    --es.session_id
    cpu_time DESC

One thing that you could do with this query is place it inside a SQL job and log the results to a table for a given SPID. For example, let’s say you wanted to measure the payload of a long lived SPID over time, perhaps like a SPID from a web service you are consuming from Sage ERP X3. Once you identified that SPID you could capture interesting properties such as reads, writes, wait time at specific time intervals. You could later write a report to graph the usage over time. In other words, this query could be used for a variety of reasons to meet your administration / business needs. I’ve even used it to check the SPID in use is connecting using the protocol I intended (named pipes vs shared memory vs TCP/IP).

For on the spot troubleshooting I love that I can use this query to find what the query plan is for a given SPID so I can do some quick analysis of the execution plan’s inefficiencies. Maybe there is a Row ID lookup, or maybe the estimated row count is too high for a given portion of the query, or maybe it is performing a table scan?

Also, if you notice in the query above I’ve isolated the es.host_process_id in the filter. You can get that process id number from the user monitor in X3 so you can find the actual code running at any given time for a single X3 user, cool trick eh?

If you’d like some brief training on the query I’ve created a YouTube video here:

New SpWho TSQL

I’m definitely not a YouTube expert so pardon the 720 video quality. I’ll record future videos in a higher format.

Hope you enjoy!