List of T-SQL Running at the moment with Sys.dm_exec_requests and sys.dm_exec_sql_text in SQL Server 2008

This is something we may need many times as a administrator. Sometime when we don’t have profiler running and don’t want to go for any other route to troubleshoot  server performance, I would execute on simple T-SQL statement with the help of DMV (Sys.Exec_Requests) and DMF (Sys.dm_exec_sql_text) which can list out all the T-SQL Statement running at the moment in our database. There are many different ways to go for but this is something very quick and efficient so I keep this simple query handy all time.

SELECT

req.session_id,

req.command,

txt.text,

req.start_time,

req.status,

req.command

FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) txt;

Sometime, when you suddenly started facing low performance on the server, you can run above query as a quick glance and look at the insight of SQL Server whether any heavy query is going on right now or not. This is not the only solution to performance issue, there are lot more tools and way but this could be first and quick step.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com