My colleague Paul McMillan left me with this lovely SQL view that you can install on your database to discover all the currently executing SQL (with the full TSQL statement text, not just a buffer from a dbcc inputbuffer call).
It also gives you a link to click to open the query plan as well
CREATE VIEW [Current_Running_SQL]
AS
SELECT CASE
WHEN Requests.sql_handle IS NULL
THEN ' '
ELSE SubString
(
Statements.text,
(Requests.Statement_Start_Offset+2)/2,
(
CASE
WHEN Requests.Statement_End_Offset = -1
THEN LEN(CONVERT(nvarchar(MAX),Statements.text))*2
ELSE Requests.Statement_End_Offset
END
-
Requests.Statement_Start_Offset
)
/
2
)
END AS StatementText,
QueryPlans.query_plan AS QueryPlan,
Statements.Text AS Batch_Text,
Sessions.Session_ID,
Sessions.Login_Name,
Sessions.Host_Name,
Sessions.Program_Name,
Sessions.Client_Interface_Name,
Requests.Wait_Time,
Requests.Cpu_Time,
Requests.Total_Elapsed_Time,
Requests.Reads,
Requests.Writes,
Requests.Logical_Reads,
Requests.Row_Count,
Requests.Granted_Query_Memory*8/1024 AS Granted_Query_Memory_MB,
LEN(Statements.text) AS Batch_Text_Length,
Requests.Statement_Start_Offset/2 AS Statement_Start_Offset,
CASE
WHEN Requests.Statement_End_Offset = -1
THEN LEN(CONVERT(nvarchar(MAX),Statements.Text))*2
ELSE Requests.Statement_End_Offset
END
/
2 AS Statement_End_Position,
(
CASE
WHEN Requests.Statement_End_Offset = -1
THEN LEN(CONVERT(nvarchar(MAX),Statements.text))*2
ELSE Requests.Statement_End_Offset
END
-
Requests.Statement_Start_Offset
)
/
2 AS Statement_Text_Length
FROM sys.dm_exec_sessions Sessions
INNER JOIN sys.dm_exec_requests Requests
ON Sessions.session_id = Requests.Session_ID
CROSS APPLY sys.dm_exec_sql_text(sql_handle) Statements
CROSS APPLY sys.dm_exec_query_plan(plan_handle) QueryPlans
WHERE Sessions.Session_ID != @@SPID
GO
No comments:
Post a Comment