SQLServerCentral Article

Return Query Text Along With sp_who2 Using Dynamic Management Views

,

Introduction

A lot of times I find myself running sp_who2, looking at a SPID and then immediately running DBCC Inputbuffer(spid) to get the query text. With all the dynamic management views in 2005, this can be accomplished in a single query.

Examining the query

The sys.dm_exec_sessions view contains one row per authenticated session and will be the driving table for the query. Because not all sessions have an active request, I need to left join the sys.dm_exec_requests view. The sys.dm_exec_requests view contains one row per current request.

Now to get the blocking information, I need to join the sys.dm_tran_locks and sys.dm_os_waiting_tasks views. The sys.dm_tran_locks view holds information about lock requests and the sys.dm_os_waiting_tasks view holds information about tasks that are waiting on a resource. Because the sys.dm_tran_locks spid owns the resource address the sys.dm_os_waiting_tasks spid waiting for, I now know what spid if any is blocking a request.

Finally, I pass the sql_handle of the request to the sys.dm_exec_sql_text function to return the query text. In order to use the function in the join, I have to use the OUTER APPLY operator that allows you to use a table valued function as if it were a table.

SELECT 
D.text SQLStatement,
A.Session_ID SPID,
ISNULL(B.status,A.status) Status,
A.login_name Login,
A.host_name HostName,
C.BlkBy,
DB_NAME(B.Database_ID) DBName,
B.command,
ISNULL(B.cpu_time, A.cpu_time) CPUTime,
ISNULL((B.reads + B.writes),(A.reads + A.writes)) DiskIO,
A.last_request_start_time LastBatch,
A.program_name
FROM
sys.dm_exec_sessions A
LEFT JOIN
sys.dm_exec_requests B
ON A.session_id = B.session_id
LEFT JOIN
(
SELECT
A.request_session_id SPID,
B.blocking_session_id BlkBy
FROM sys.dm_tran_locks as A
INNER JOIN sys.dm_os_waiting_tasks as B
ON A.lock_owner_address = B.resource_address
) C
ON A.Session_ID = C.SPID
OUTER APPLY sys.dm_exec_sql_text(sql_handle) D

Conclusion

If you like the results "as is", just create a stored procedure so you can always execute it when needed. There is a lot of extra information in the views, so explore the columns and customize it to meet your needs.

Rate

4.85 (47)

You rated this post out of 5. Change rating

Share

Share

Rate

4.85 (47)

You rated this post out of 5. Change rating