有關查詢和執行計劃的DMV

查看被緩存的查詢計劃

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
    st.text AS [SQL]
    , cp.cacheobjtype
    , cp.objtype
    , COALESCE(DB_NAME(st.dbid),
        DB_NAME(CAST(pa.value AS INT))+'*',
        'Resource') AS [DatabaseName]
    , cp.usecounts AS [Plan usage]
    , qp.query_plan
FROM sys.dm_exec_cached_plans cp                      
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
WHERE pa.attribute = 'dbid'
  AND st.text LIKE '%這里是查詢語句包含的內容%'  

 

結果是:

 

可以根據查詢字段來根據關鍵字查看緩沖的查詢計劃。

 

查看某一查詢是如何使用查詢計劃的

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
  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
WHERE 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)
LIKE '%指定查詢包含的字段%'  

 

結果是:

 

查看數據庫中跑的最慢的前20個查詢以及它們的執行計劃

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
  CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
                                     AS [Total Duration (s)]
  , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
                               AS DECIMAL(28, 2)) AS [% CPU]
  , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
        qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
  , qs.execution_count
  , CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count
                AS DECIMAL(28, 2)) AS [Average Duration (s)]
  , 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
WHERE qs.total_elapsed_time > 0
ORDER BY qs.total_elapsed_time DESC                    

 

查看數據庫中哪個查詢最耗費資源有助于你解決問題

 

被阻塞時間最長的前20個查詢以及它們的執行計劃

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
  CAST((qs.total_elapsed_time - qs.total_worker_time) /     
        1000000.0 AS DECIMAL(28,2)) AS [Total time blocked (s)]
  , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
        AS DECIMAL(28,2)) AS [% CPU]
  , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
        qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
  , qs.execution_count
  , CAST((qs.total_elapsed_time  - qs.total_worker_time) / 1000000.0
    / qs.execution_count AS DECIMAL(28, 2)) AS [Blocking average (s)]
  , 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
WHERE qs.total_elapsed_time > 0
ORDER BY [Total time blocked (s)] DESC                      

結果如圖:

 

找出這類查詢也是數據庫調優的必須品

 

最耗費CPU的前20個查詢以及它們的執行計劃

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
  CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2))
                                           AS [Total CPU time (s)]
  , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
                                      AS DECIMAL(28,2)) AS [% CPU]
  , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
           qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
             , qs.execution_count
  , CAST((qs.total_worker_time) / 1000000.0
    / qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)]
  , 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
WHERE qs.total_elapsed_time > 0
ORDER BY [Total CPU time (s)] DESC         

 

原理同上，就不上圖了

 

最占IO的前20個查詢以及它們的執行計劃

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
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 [Total IO] DESC                                  

結果如圖:

 

能幫助找出占IO的查詢

 

查找被執行次數最多的查詢以及它們的執行計劃

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
    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 qs.execution_count DESC;    

 

結果如圖:

 

可以針對用的最多的查詢語句做特定優化。

 

特定語句的最后運行時間

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT DISTINCT TOP 20
    qs.last_execution_time
    , qt.text AS [Parent Query]
    , DB_NAME(qt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats qs                      
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE qt.text LIKE '%特定語句的部分%'
ORDER BY qs.last_execution_time DESC            

 

結果如圖: