sql 慢语句查询

排查历史慢查询：
SELECT TOP 20
  [Total IO] = (qs.total_logical_reads + qs.total_logical_writes)
  , [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /
                                            qs.execution_count
  , qs.execution_count
  , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,    
  ((CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
  , qt.text AS [Parent Query]
  , DB_NAME(qt.dbid) AS DatabaseName
  , qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [Average IO]  DESC

查询当前正在执行的慢查询：
SELECT  TOP 1 ST.transaction_id AS TransactionID ,
        st.session_id ,
        DB_NAME(DT.database_id) AS DatabaseName ,
        ses.host_name ,
        ses.login_name ,
        ses.status,
        AT.transaction_begin_time AS TransactionStartTime ,
        s.text ,
        c.connect_time ,
        DATEDIFF(second, AT.transaction_begin_time, GETDATE()) "exec_time(s)" ,
        DATEDIFF(minute, AT.transaction_begin_time, GETDATE()) AS Tran_run_time ,
        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
        LEFT JOIN sys.dm_exec_connections AS C ON st.session_id = c.session_id
        LEFT JOIN sys.dm_exec_sessions AS ses ON c.session_id = ses.session_id
        CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_Handle) s
WHERE   DATEDIFF(second, AT.transaction_begin_time, GETDATE()) > 2

查询死锁

-- 查询死锁
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks
where resource_type='OBJECT'