Tuesday, August 13, 2013

T-SQL script to get long running queries in SQL Server


Background:
The purpose of this article is to provide a simple way of getting a list of all the concurrently running queries on the SQL Server using T-SQL script.
T-SQL Script:

SELECT
      DMST.[TEXT] AS SQLQuery,
      DMR.SESSION_ID AS SessionID,
      DMR.[STATUS] AS [Status] ,
      DMR.COMMAND,
      DMR.CPU_TIME,
      DMR.TOTAL_ELAPSED_TIME
FROM SYS.DM_EXEC_REQUESTS DMR
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS DMST
WHERE DMR.SESSION_ID <> @@SPID


Unite Testing:
Run some of the long running queries on management studio in various window and execute this script. This script will provide you the list of all the running queries on SQL Server along with time taken by query.

Query O/P:

 

No comments:

Post a Comment