Friday, October 5, 2012

Currently executing queries

It is important to know what is currently executing in SQL Server at any given time. You may need this for troubleshooting purposes. The below query is useful in such occasions;

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
s.login_name,
s.host_name,
req.wait_type,
req.wait_resource,
SUBSTRING(sqltext.text, (req.statement_start_offset/2)+1,
        ((
CASE req.statement_end_offset
         
WHEN -1 THEN DATALENGTH(sqltext.text)
        
ELSE req.statement_end_offset
        
END - req.statement_start_offset)/2) + 1) AS statement_text,
req.plan_handle,
sqltext.text
FROM
sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions s
   
ON req.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
ORDER BY req.cpu_time DESC

No comments:

Post a Comment

How to fix cardinality estimation anomalies [Video]

Use the link mentioned below to watch the presentation that I delivered for PASS DBA Virtual Chapter about Filtered Statistics.  http:...