Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Return Query Text Along With sp_who2 Using Dynamic Management Views

By Ken Simmons,

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.

Total article views: 11808 | Views in the last 30 days: 4
 
Related Articles
BLOG

DMV-12 : Retaion in Session & Transaction……..sys.dm_tran_session_transactions

sys.dm_tran_session_transactions DMV (Dynamic Management View), described by BOL as follows: http://...

BLOG

DMV-3 : What is currently going on ?……..sys.dm_exec_requests

sys.dm_exec_requests DMV (Dynamic Management View), described by BOL as follows: http://msdn.microso...

BLOG

Monitoring a Rollback and sys.dm_exec_requests

The dynamic management view (DMV) sys.dm_exec_requests returns information about each request that i...

BLOG

DMV-2 : Explore the secrets of session…… sys.dm_exec_sessions

sys.dm_exec_sessions DMV (Dynamic Management View), delineated by BOL as follows: http://msdn.micros...

BLOG

Using sys.dm_exec_requests to find a blocking chain

A query that calls sys.dm_exec_requests and pulls the statement text out of the request is a handy s...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones