SQL Server get current executing statements - SP_WHO or SP_WHO2 or sys.dm_exec_requests

Published 30 July 08 09:43 AM | SQL Master 

As a SQL Server user or DBAs using SP_WHO or undocumented stored procedure SP_WHO2 is a common occurrence.

For this there are times when you will need to run sp_who on your SQL Server to figure out who is on and what are they doing. The output is quite clear to get a list of processes with SPID, but how about more detailed information on these processes. Until SQL Server 2005 it is bit tricky to get such detailed information, so what 2005 version offers you with system catalogs such as sys.dm_exec_requests. Here is the script you can obtain information that is freely available on Technet site too:

select r.session_id
,status
,substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2)
as query_text   --- this is the statement executing right now
,qt.dbid
,qt.objectid
,r.cpu_time
,r.total_elapsed_time
,r.reads
,r.writes
,r.logical_reads
,r.scheduler_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
order by r.scheduler_id, r.status, r.session_id

Until SQL Server 2005 SP_WHO2 was my favourite and using system catalogs & DMVs is much flexible to find out the culprit.

 

Comments

# Other SQL Server Blogs around the Web said on July 30, 2008 9:54 AM:

As a SQL Server user or DBAs using SP_WHO or undocumented stored procedure SP_WHO2 is a common occurrence

# SQL Server Transact-SQL (SSQA.net) : SQL Server get current executing statements - SP_WHO or SP_WHO2 or sys.dm_exec_requests said on July 30, 2008 10:53 AM:

PingBack from http://sqlserver-qa.net/blogs/t-sql/archive/2008/07/30/4671.aspx

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.

Search

Go

This Blog

«July 2008»
SMTWTFS
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

Syndication