Thursday, 29 September 2011

How to see all running SQL statements

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

image

 

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