What queries are currently running on my database server?

"Dig" The Server, General 1 Minute

Whenever database server is not responding well, as a DBA, we need to find what’s going on the server.One of the common things, we would be interested in looking is the queries currently executing on the sql server. While checking this, capturing their execution plan is also a good idea.Below query returns the sql text and execution plan of queries currently executing on the server.
Query(Works on SQL2005 and above):
Select a.session_id,A.Status,A.Command,B.Text,c.query_plan,DB_Name(a.database_id) as DatabaseName,a.percent_complete,a.logical_reads,a.blocking_session_id,a.wait_type
From sys.dm_exec_requests a cross apply sys.dm_exec_sql_text(a.sql_handle) b
cross apply sys.dm_exec_query_plan(a.plan_handle) c
Where a.session_id<>@@SPID
Sample Output :
Capture

Published by SQLWhisperer

Hello Everyone. My intention of having this site is to have one repository location for my SQL Server Learning's. Most of the information here is already available on Web but I want to have it in one global location, so I can reference whenever I need . I would be more than happy, if you find anything on this blog useful. Also, Please use your own caution when running any code on your systems.Thank you for visiting.

Published