See What Queries Are Currently Running in SQL Server

sqlSQLtipssql-serverperformance

Find out which statement is running on a SQL Server instance, how long it has been running, which session and database it belongs to, and what it is waiting for. Useful when a query has been running for a very long time. Uses the dm_exec_requests view (SQL Server 2005 and later) and needs the VIEW SERVER STATE permission.

SELECT
	r.start_time StartTime,
	DATEDIFF(SECOND,r.start_time ,GETDATE()) Duration ,
	r.session_ID,
	DB_NAME(database_id) [Database],
	SUBSTRING(t.text,(r.statement_start_offset/2)+1,
	CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
	THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
	ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
	END) [Executing SQL],
	Status,command,wait_type,wait_time,wait_resource,
	last_wait_type
FROM
	sys.dm_exec_requests r
	OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE
	--session_id != @@SPID AND  -- don't show this query
	session_id > 50 -- don't show system queries
ORDER BY
	r.start_time

Comments

Loading comments…