Currently Executing SQL Queries

Here is some handy SQL to determine the queries currently executing on your SQL Server (limited to the longest running 10 queries).  It samples only an instant in time, so there may be fewer rows returned than you might expect. The Activity Monitor in SQL Management Studio has a similar view.  However, I like keeping this handy because when the server is really in trouble, Activity Monitor may take a very long time to start up.


SELECT TOP 10
  Substring(TEXT, (statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN Datalength(TEXT) ELSE statement_end_offset END - statement_start_offset)/2)+1) AS query_text
  ,logical_reads
  ,reads
  ,writes
  ,er.total_elapsed_time
  ,Db_name(database_id) AS [database]
  ,Object_name(objectid, database_id) AS object_name
  , *
FROM sys.dm_exec_requests er
CROSS APPLY sys.Dm_exec_sql_text(sql_handle)
ORDER BY er.total_elapsed_time DESC