SQL: Get the TSQL for Longest Running Requests
Author: jason
Date: 2014-02-07 13:20:18
Category: Musing

Use the Dynamic Management View sys.dm_exec_requests to get running requests. But that isn't too interesting. There are a lot of requests that DBAs might not really care about. Add in a list of commands to ignore. Then use the SQL Handle with the sys.dm_exec_sql_text view to see what the TSQL is.





But if we want to get really clever, and only need to see the longest, we can scavenge from the previous OPENTRAN query code to get this.





Code

SELECT percent_complete, (estimated_completion_time/1000)/60 Estimated_compMins,
(total_elapsed_time/1000)/60 Total_Elap_Mins ,DB_NAME(Database_id) DBName,
dm.session_id, dm.command, blocking_session_id, wait_type, sql_handle, *
FROM sys.dm_exec_requests dm
WHERE dm.command NOT IN ('BRKR EVENT HNDLR', 'BRKR TASK', 'DB MIRROR',
'FSAGENT TASK', 'TASK MANAGER', 'SIGNAL HANDLER', 'TRACE QUEUE TASK',
'RESOURCE MONITOR', 'LOG WRITER', 'LOCK MONITOR', 'LAZY WRITER',
'CHECKPOINT', 'XE DISPATCHER', 'XE TIMER',
'HADR_AR_MGR_NOTIFICATION_WORKER', 'SYSTEM_HEALTH_MONITOR',
'UNKNOWN TOKEN', 'RECOVERY WRITER', 'RECEIVE')
ORDER BY dm.command ASC;

SELECT *
FROM sys.dm_exec_sql_text(0x020000006B40A91DE37E8A55FF70BFA5A8F333277B7694950000000000000000000000000000000000000000)


Code

DECLARE @InputBuffer AS TABLE (EventType VARCHAR(255), Parameters VARCHAR(255), EventInfo VARCHAR(MAX));
DECLARE @Commands AS TABLE (commands VARCHAR(255))
INSERT INTO @Commands VALUES ('BRKR EVENT HNDLR'), ('BRKR TASK'), ('DB MIRROR'),
('FSAGENT TASK'), ('TASK MANAGER'), ('SIGNAL HANDLER'), ('TRACE QUEUE TASK'),
('RESOURCE MONITOR'), ('LOG WRITER'), ('LOCK MONITOR'), ('LAZY WRITER'),
('CHECKPOINT'), ('XE DISPATCHER'), ('XE TIMER'), ('HADR_AR_MGR_NOTIFICATION_WORKER'),
('SYSTEM_HEALTH_MONITOR'), ('UNKNOWN TOKEN'), ('RECOVERY WRITER'), ('RECEIVE')
DECLARE @SPID INT = ISNULL((SELECT TOP 1 session_id FROM sys.dm_exec_requests dm
WHERE dm.command NOT IN (SELECT commands FROM @Commands)
ORDER BY total_elapsed_time DESC),1)
INSERT INTO @InputBuffer EXEC('DBCC INPUTBUFFER(' + @SPID + ');');
DECLARE @tSQL VARCHAR(MAX) = (SELECT EventInfo FROM @InputBuffer)
SELECT DB_NAME(database_id) AS [Database], Session_ID AS SPID, Blocking_Session_ID AS Blocking,
[Status], Command, Wait_Type, Percent_Complete AS [%Comp],
(estimated_completion_time/1000)/60 AS Est_Comp_Mins,
(total_elapsed_time/1000)/60 AS Total_Mins,
USER_NAME([user_id]) AS [User], @tSQL AS [tSQL]
FROM sys.dm_exec_requests dm
WHERE session_id = @SPID


http://technet.microsoft.com/en-us/library/ms177648.aspx



jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009