SQLServer大事务巡检

  SELECT top 10 
        b.session_id 'Session ID',
        CAST(Db_name(a.database_id) AS VARCHAR(20)) 'Database Name',
        c.command,
        Substring(st.TEXT, ( c.statement_start_offset / 2 ) + 1,
        ( (
        CASE c.statement_end_offset
            WHEN -1 THEN Datalength(st.TEXT)
            ELSE c.statement_end_offset
        END 
        -
        c.statement_start_offset ) / 2 ) + 1)                                                             
        statement_text,
        Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(
        Object_schema_name(st.objectid,
                    st.dbid)) +
                    N'.' + Quotename(Object_name(st.objectid, st.dbid)), '')    
        command_text,
        c.wait_type,
        c.wait_time,
        a.database_transaction_log_bytes_used / 1024.0 / 1024.0                 'MB used',
        a.database_transaction_log_bytes_used_system / 1024.0 / 1024.0          'MB used system',
        a.database_transaction_log_bytes_reserved / 1024.0 / 1024.0             'MB reserved',
        a.database_transaction_log_bytes_reserved_system / 1024.0 / 1024.0      'MB reserved system',
        a.database_transaction_log_record_count                           
        'Record count'
    FROM   sys.dm_tran_database_transactions a
        JOIN sys.dm_tran_session_transactions b
            ON a.transaction_id = b.transaction_id
        JOIN sys.dm_exec_requests c
            CROSS APPLY sys.Dm_exec_sql_text(c.sql_handle) AS st
            ON b.session_id = c.session_id
        -- where a.database_transaction_log_bytes_used / 1024.0 / 1024.0 > 5  -- 一般建议只监控超过5MB的事务
    ORDER  BY 'MB used' DESC ;

-- 下面的sql里面限定的阈值5秒
SELECT
        r.session_id,
        r.start_time,
        TotalElapsedTime_ms = r.total_elapsed_time,           
        r.[status],           
        r.command,           
        DatabaseName = DB_Name(r.database_id),           
        r.wait_type,
        r.last_wait_type,           
        r.wait_resource,           
        r.cpu_time,           
        r.reads,           
        r.writes,           
        r.logical_reads,           
        t.[text] AS [executing batch],           
        SUBSTRING(
                        t.[text], 
                        r.statement_start_offset / 2, 
                        ( CASE 
                            WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text]) 
                            ELSE r.statement_end_offset  
                            END - r.statement_start_offset 
                        ) / 2 
                ) AS [executing statement],
    p.query_plan
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
    CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS p
    where r.total_elapsed_time >5000
    ORDER BY r.total_elapsed_time DESC;