sqlserver查询当前运行的sql

sqlserver查询当前运行的sql

WITH sess AS (     SELECT         es.session_id,         database_name = DB_NAME(er.database_id),         er.cpu_time,         er.reads,         er.writes,         er.logical_reads,         login_name,         er.status,         blocking_session_id,         wait_type,         wait_resource,         wait_time,         individual_query = SUBSTRING (qt.text, (er.statement_start_offset/2)+1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2)+1),         parent_query = qt.text,         program_name,         host_name,         nt_domain,         start_time,         DATEDIFF(MS,er.start_time,GETDATE()) as duration,         (SELECT query_plan FROM sys.dm_exec_query_plan(er.plan_handle)) AS query_plan     FROM         sys.dm_exec_requests er         INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id         CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt     WHERE         es.session_id > 50         AND es.session_Id NOT IN (@@SPID) ) SELECT     * FROM     sess UNION ALL SELECT     es.session_id,     database_name = '',     0,     0,     0,     0,     login_name,     es.status,     0,     '',     '',     '',     qt.text,     parent_query = qt.text,     program_name,     host_name,     nt_domain,     es.last_request_start_time,     DATEDIFF(MS,es.last_request_start_time,GETDATE()) as duration,     NULL AS query_plan FROM     sys.dm_exec_sessions es     INNER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id     CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt WHERE     ec.most_recent_session_id IN     (         SELECT blocking_session_id FROM sess WHERE blocking_session_id NOT IN(SELECT DISTINCT session_id FROM sess)     ) ORDER BY     1, 2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30182853/viewspace-2131407/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30182853/viewspace-2131407/