执行计划常用的查询分析

--特定语句的最后运行时间
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT DISTINCT TOP 20 qs.last_execution_time AS LastExectionTime
,qt.text AS ParentQuery
,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 
ORDER BY qs.last_execution_time DESC      

--查看被缓存的查询计划

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;
WITH TEMP AS(
SELECT
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 IndividualQuery 
, qt.text AS ParentQuery
, 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 )

SELECT TOP 20*  FROM TEMP
WHERE TEMP.IndividualQuery LIKE '%指定查询包含的字段%'

--查看数据库中跑的最慢的前20个查询以及它们的执行计划

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT TOP 20 
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))  AS TotalDurationSeconds 
,CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2)) AS CPUPersent 
,CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2)) AS WaitingPersent 
,qs.execution_count AS ExecutionCount
, CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count AS DECIMAL(28, 2)) AS AverageDurationSec 
, 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 IndividualQuery
,qt.text AS ParentQuery 
,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;
WITH TEMP AS(
SELECT CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) AS BlockedTotalSeconds 
,CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS CPUPersent 
,CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2)) AS WaitingPersent
,qs.execution_count AS ExecutionCount
,CAST((qs.total_elapsed_time  - qs.total_worker_time) / 1000000.0 / qs.execution_count AS DECIMAL(28, 2)) AS AverageBlockingSeconds 
, 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 IndividualQuery 
, qt.text AS ParentQuery 
, 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)

SELECT TOP(20) * FROM TEMP
ORDER BY TEMP.BlockedTotalSeconds DESC

--最耗费CPU的前20个查询以及它们的执行计划 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH TEMP AS(
SELECT CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) AS TotalSecondsForCPUTime 
,CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time  AS DECIMAL(28,2)) AS CPUPersent
,CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2)) AS WaitingPersent 
,qs.execution_count ExecutionCount
,CAST((qs.total_worker_time) / 1000000.0 / qs.execution_count AS DECIMAL(28, 2)) AS AvgSecondsForCPUTime
,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 IndividualQuery
,qt.text AS ParentQuery 
,DB_NAME(qt.dbid) AS DatabaseName 
,qp.query_plan QueryPlan
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 )

SELECT TOP(20)* FROM TEMP
ORDER BY TEMP.TotalSecondsForCPUTime DESC

--最占IO的前20个查询以及它们的执行计划
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH TEMP AS(
SELECT (qs.total_logical_reads + qs.total_logical_writes) AS TotalIO 
,(qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count AS AvgIO
, qs.execution_count AS ExecutionCount
, 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 IndividualQuery 
, qt.text AS ParentQuery
, DB_NAME(qt.dbid) AS DatabaseName 
, qp.query_plan AS QueryPlan
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)

SELECT TOP(20)* FROM TEMP
ORDER BY TEMP.TotalIO DESC
                                 

--查找被执行次数最多的查询以及它们的执行计划
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT TOP 20 qs.execution_count AS ExectionCount
,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 IndividualQuery
,qt.text AS ParentQuery
,DB_NAME(qt.dbid) AS DatabaseName 
,qp.query_plan AS QueryPlan
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;
WITH TEMP AS(
SELECT qs.execution_count AS ExecutionCount
,(qs.total_logical_reads + qs.total_logical_writes) AS TotalIO 
,(qs.total_logical_reads + qs.total_logical_writes) 
    / qs.execution_count AS AvgIO
    
,CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) AS TotalSecondsForCPUTime 
,CAST((qs.total_worker_time) / 1000000.0 
    / qs.execution_count AS DECIMAL(28, 2)) AS AvgSecondsForCPUTime

,CAST((qs.total_elapsed_time - qs.total_worker_time) 
    / 1000000.0 AS DECIMAL(28,2)) AS BlockedTotalSeconds 
,CAST((qs.total_elapsed_time  - qs.total_worker_time) / 1000000.0 
    / qs.execution_count AS DECIMAL(28, 2)) AS AverageBlockingSeconds 
    
,CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))  AS TotalDurationSeconds 
, CAST(qs.total_elapsed_time / 1000000.0 
    / qs.execution_count AS DECIMAL(28, 2)) AS AverageDurationSec 
,qs.last_execution_time
,qs.creation_time
, 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 IndividualQuery 
, qt.text AS ParentQuery
, DB_NAME(qt.dbid) AS DatabaseName 
, qp.query_plan AS QueryPlan
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)

SELECT TOP(20)* FROM TEMP
ORDER BY TEMP.TotalIO DESC

--==============================================================
--查找没有参数化的执行计划最多的前20条SQL
SELECT  *
FROM    ( SELECT TOP ( 20 )
qs.query_hash ,
COUNT(1) AS PlanCount ,
SUM(qs.execution_count) AS ExecCount
FROM      SYS.dm_exec_query_stats AS qs
WHERE     execution_count < 10
GROUP BY  query_hash
ORDER BY  COUNT(1) DESC
) AS T1
CROSS APPLY ( SELECT TOP ( 1)
qs.last_execution_time ,
qs.creation_time ,
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 IndividualQuery ,
qt.text AS ParentQuery ,
DB_NAME(qt.dbid) AS DatabaseName,
qp.query_plan AS QueryPlan
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.query_hash = T1.query_hash
ORDER BY  qs.last_execution_time DESC
) AS T2