有关查询和执行计划的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            

 

结果如图: