查看当前运行的SQL语句

妖狐艹你老母 2021-11-17 04:18 293阅读 0赞

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

http://www.cnblogs.com/double-K/p/6264814.html

转载于:https://www.cnblogs.com/allen0118/p/6269201.html