一些有用的DMV



---查看数据库中正在运行的SQL语句的执行情况（包括执行计划和等待类型等等）

SELECT
 sp.ecid,
    DB_NAME(sp.dbid) AS [Database],
 QP.query_plan,
 sp.nt_username,
    p.session_id ,
    p.request_id ,
    p.start_time ,
    p.status ,
    p.command ,
 sp.lastwaittype,
    p.blocking_session_id ,
    p.wait_type ,
    p.wait_time ,
    p.wait_resource ,
    p.total_elapsed_time ,
    p.open_transaction_count ,
    p.transaction_isolation_level ,
    SUBSTRING(qt.text, p.statement_start_offset / 2, ( CASE WHEN p.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                            ELSE p.statement_end_offset
                                                       END - p.statement_start_offset ) / 2) AS "SQL stament" ,
    p.statement_start_offset ,
    p.statement_end_offset ,
    sp.program_name,
 sp.Hostname,
 sp.nt_domain,
    batch = qt.text
FROM
    master.sys.dm_exec_requests AS p
INNER JOIN sys.sysprocesses sp ON p.session_id=sp.spid
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) AS QP
WHERE
    p.session_id > 50 and p.session_id<>@@SPID
  

--监控当前打开的游标

SELECT  S.host_name AS ClientMachine ,
        S.program_name AS ApplicationName ,
        S.original_login_name AS LoginName ,
        C.name AS CursorName ,
        C.properties AS CursorOptions ,
        C.creation_time AS CursorCreatinTime ,
        ST.text AS SQLQuery ,
        C.is_open AS IsCursorOpen ,
        C.worker_time / 1000 AS DurationInMiliSeconds ,
        C.reads AS NumberOfReads ,
        C.writes AS NumberOfWrites
FROM    sys.dm_exec_cursors (0) AS C
        INNER JOIN sys.dm_exec_sessions AS S ON C.session_id = S.session_id
        CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) AS ST
ORDER BY DurationInMiliSeconds DESC
GO

--监控当前正在运行的事务
SELECT  ST.transaction_id AS TransactionID ,
        DB_NAME(DT.database_id) AS DatabaseName ,
        AT.transaction_begin_time AS TransactionStartTime ,
        DATEDIFF(SECOND, AT.transaction_begin_time, GETDATE()) AS TransactionDuration ,
        CASE AT.transaction_type
          WHEN 1 THEN 'Read/Write Transaction'
          WHEN 2 THEN 'Read-Only Transaction'
          WHEN 3 THEN 'System Transaction'
          WHEN 4 THEN 'Distributed Transaction'
        END AS TransactionType ,
        CASE AT.transaction_state
          WHEN 0 THEN 'Transaction Not Initialized'
          WHEN 1 THEN 'Transaction Initialized & Not Started'
          WHEN 2 THEN 'Active Transaction'
          WHEN 3 THEN 'Transaction Ended'
          WHEN 4 THEN 'Distributed Transaction Initiated Commit Process'
          WHEN 5 THEN 'Transaction in Prepared State & Waiting Resolution'
          WHEN 6 THEN 'Transaction Committed'
          WHEN 7 THEN 'Transaction Rolling Back'
          WHEN 8 THEN 'Transaction Rolled Back'
        END AS TransactionState
FROM    sys.dm_tran_session_transactions AS ST
        INNER JOIN sys.dm_tran_active_transactions AS AT ON ST.transaction_id = AT.transaction_id
        INNER JOIN sys.dm_tran_database_transactions AS DT ON ST.transaction_id = DT.transaction_id
ORDER BY TransactionStartTime
GO

--查询被阻塞和正在阻塞的查询
SELECT  R.session_id AS BlockedSessionID ,
        S.session_id AS BlockingSessionID ,
        Q1.text AS BlockedSession_TSQL ,
        Q2.text AS BlockingSession_TSQL ,
        C1.most_recent_sql_handle AS BlockedSession_SQLHandle ,
        C2.most_recent_sql_handle AS BlockingSession_SQLHandle ,
        S.original_login_name AS BlockingSession_LoginName ,
        S.program_name AS BlockingSession_ApplicationName ,
        S.host_name AS BlockingSession_HostName
FROM    sys.dm_exec_requests AS R
        INNER JOIN sys.dm_exec_sessions AS S ON R.blocking_session_id = S.session_id
        INNER JOIN sys.dm_exec_connections AS C1 ON R.session_id = C1.most_recent_session_id
        INNER JOIN sys.dm_exec_connections AS C2 ON S.session_id = C2.most_recent_session_id
        CROSS APPLY sys.dm_exec_sql_text(C1.most_recent_sql_handle) AS Q1
        CROSS APPLY sys.dm_exec_sql_text(C2.most_recent_sql_handle) AS Q2

--建议需要更新的统计信息
SELECT DISTINCT
        OBJECT_NAME(SI.object_id) AS Table_Name ,
        SI.name AS Statistics_Name ,
        STATS_DATE(SI.object_id, SI.index_id) AS Last_Stat_Update_Date ,
        SSI.rowmodctr AS RowModCTR ,
        SP.rows AS Total_Rows_In_Table ,
        'UPDATE STATISTICS [' + SCHEMA_NAME(SO.schema_id) + '].['
        + OBJECT_NAME(SI.object_id) + ']' + SPACE(2) + SI.name AS Update_Stats_Script
FROM    sys.indexes AS SI( NOLOCK )
        INNER JOIN sys.objects AS SO( NOLOCK ) ON SI.object_id = SO.object_id
        INNER JOIN sys.sysindexes SSI( NOLOCK ) ON SI.object_id = SSI.id
                                                    AND SI.index_id = SSI.indid
        INNER JOIN sys.partitions AS SP ON SI.object_id = SP.object_id
WHERE   SSI.rowmodctr > 0
        AND STATS_DATE(SI.object_id, SI.index_id) IS NOT NULL
        AND SO.type = 'U'
ORDER BY RowModCTR DESC