USE [master]; GO SELECT DB_NAME(r.database_id) AS [DB_NAME] ,r.command AS [COMMAND] ,CONVERT( VARCHAR ,DATEADD( ms ,r.cpu_time ,0 ) ,114 ) AS [CPU_TIME] ,CONVERT( VARCHAR ,DATEADD( ms , r.total_elapsed_time ,0 ) ,114 ) AS [TOTAL_ELAPSED_TIME] ,r.percent_complete AS [PERC_CMPLT] ,CASE r.transaction_isolation_level WHEN 0 THEN N'Unspecified' WHEN 1 THEN N'ReadUncomitted' WHEN 2 THEN N'ReadCommitted' WHEN 3 THEN N'Repeatable' WHEN 4 THEN N'Serializable' WHEN 5 THEN N'Snapshot' ELSE N'' END AS [ISOLATION_LEVEL] ,r.logical_reads AS [LOGICAL_READS] ,r.estimated_completion_time as ect ,ss.host_process_id AS [PID] ,r.session_id AS [SSID] ,r.blocking_session_id AS [BLOCK_SSID] ,ISNULL( r.wait_type,N'None' ) AS [WAIT_TYPE] ,r.wait_time AS [WAIT_TIME] ,CASE st.encrypted WHEN 0 THEN N'NO' ELSE N'YES' END AS [ENCRYPTED] ,SUBSTRING( st.text , r.statement_start_offset / 2 + 1 , ( ( CASE WHEN r.statement_end_offset = -1 THEN ( LEN( CONVERT( NVARCHAR(MAX) , st.text )) * 2 ) ELSE r.statement_end_offset END ) - r.statement_start_offset ) / 2 + 1 ) AS [STATEMENT_TEXT] ,st.text AS [BATCH_TEXT] ,r.status AS [STATUS] ,ss.login_name AS [LOGIN_NAME] ,ss.login_time ,ss.host_name AS [HOST_NAME] ,ss.program_name AS [PROGRAM_NAME] ,ss.client_interface_name , [ss].[is_user_process] AS [IS_USER_PROCESS] , [qp].[query_plan] AS [QUERY_PLAN] ,r.* FROM sys.dm_exec_requests [r] LEFT JOIN sys.dm_exec_sessions AS [ss] ON ss.session_id = r.session_id CROSS APPLY sys.dm_exec_sql_text( r.sql_handle ) AS [st] OUTER APPLY sys.dm_exec_query_plan( r.plan_handle ) AS [qp] WHERE --r.session_id > 50 --AND r.session_id <> @@SPID --AND ss.is_user_process = 1 AND ss.program_name<>'SQLAgent - TSQL JobStep (Job 0x189C80989E75B74DBB00BA6F2789E3BE : Step 2)' ORDER BY r.cpu_time DESC OPTION (RECOMPILE); GO
