Archive

Posts Tagged ‘blocking query’

SQL Server – Finding currently running queries

10.08.2011 5 comments

You can use sys.dm_exec_requests DMV to list all requests. This view also contains a hash map of the SQL text – sql_handle, which can be passed to DMF sys.dm_exec_sql_text to get the query text:

SELECT      REQUEST.session_id, REQUEST.start_time, QUERY.text

FROM        sys.dm_exec_requests REQUEST

CROSS APPLY

            sys.dm_exec_sql_text(REQUEST.sql_handle) QUERY

Result Set:

session_id start_time               text

———- ————             ——————

54         2011-08-10 20:28:17.840  SELECT REQUEST.session_id, REQUEST.start_time,
                      QUERY.text
                     
FROM   sys.dm_exec_requests REQUEST
                     
CROSS APPLY

                                    sys.dm_exec_sql_text(REQUEST.sql_handle) QUERY

56         2011-08-10 20:27:02.380  USE SqlAndMe SELECT ProductID, Name
                                                               FROM   ProductList

 

(2 row(s) affected)

 

In case you are trying to find the query which is causing blocking, you can find the blocking session id by using sp_who or sp_who3.

Once you locate the blocking session id, it can be used to filter above results:

SELECT      REQUEST.session_id, REQUEST.start_time, QUERY.text

FROM        sys.dm_exec_requests REQUEST

CROSS APPLY

            sys.dm_exec_sql_text(REQUEST.sql_handle) QUERY

WHERE REQUEST.session_id = 58

Result Set:

session_id start_time               text

———- ————             ——————

58         2011-08-10 20:39:17.773  UPDATE ProductList  SET  Name = 'Bearing Ball'

(1 row(s) affected)

 

Hope This Helps!

Vishal