Pages

Friday 27 April 2012

SQL SERVER - Find Currently Running Queries

Following is the SQL script to list out which queries are currently running on SQL server database.

SELECT 
 OBJECT_NAME(t.ObjectID) as ObjectName,
 SUBSTRING(t.text,(statement_start_offset/2)+1,
  (( CASE statement_end_offset
   WHEN -1 THEN DATALENGTH(t.text)
   ELSE statement_end_offset
  END - statement_start_offset)/2) + 1
 ) AS StatementText,
 db_name(database_id) as DatabaseName,
 req.blocking_session_id as blocking_session_id,
 req.session_id,
 req.start_time,
 req.[status],
 req.command,
 req.open_transaction_count,
 req.percent_complete,
 req.cpu_time,
 req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]) AS t

GO

The above query helps to identify queries that are running for long time so that an appropriate action can be taken.

more about sys.dm_exec_requests
more about sys.dm_exec_sql_text

No comments:

Post a Comment