Search

Monday, April 14, 2014

Details of current running query

Use below query to check the details of current running Query on SQL Server.

SELECT R.Session_ID, R.Request_ID AS [Session Request ID], CASE WHEN S.Login_Name = S.Original_Login_Name THEN S.Login_Name ELSE S.Login_Name + ' (' + S.Original_Login_Name + ')' END AS [Login Name], S.Program_Name AS [Program Name], DB_Name(R.Database_ID) AS [Database Name], R.Status, S.Host_Name AS [Host Name], C.Client_Net_Address AS [Client Net Address], R.Command, ST.Text AS [Query Text], QP.Query_Plan AS [XML Query Plan], R.Last_Wait_Type AS [Last Wait Type], R.Wait_Type AS [Current Wait Type], R.Blocking_Session_ID AS [Blocking Session ID], R.Row_Count AS [Row Count], 
R.Granted_Query_Memory AS [Granted Query Memory], R.Open_Transaction_Count AS [Open Transaction Count], R.User_ID AS [User ID], R.Percent_Complete AS [% Complete], R.Start_Time AS [Start Time], R.CPU_Time AS [CPU Time], R.Estimated_Completion_Time AS [Estimated Completion Time], CASE R.Transaction_Isolation_Level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' ELSE CAST(R.Transaction_Isolation_Level AS VARCHAR(32)) END AS [Transaction Isolation Level]
FROM SYS.DM_EXEC_REQUESTS AS R LEFT OUTER JOIN SYS.DM_EXEC_SessionS AS S ON S.Session_ID = R.Session_ID LEFT OUTER JOIN SYS.DM_EXEC_ConnectionS AS C ON C.Connection_ID = R.Connection_ID CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_Handle) AS ST CROSS APPLY SYS.DM_EXEC_Query_Plan(R.Plan_Handle) AS QP 
WHERE R.STATUS NOT IN ('Background','Sleeping')

No comments:

Post a Comment