SqlServer 从执行计划缓存发现问题sql

注意以下sql都较复杂，在数据库中执行时间可能较长，不要执行过于频繁。

一、 隐式转换sql

版本1

-- 找到含有隐式转换且会导致index seek变为index scan的语句
–find all the plans and query which are being compared with wrong data type in queries and thus casuing the conversion implicit or explicit on column which has index and this in turn is causing the index scan instead of index seek
 
declare @databasename sysname
set @databasename= quotename(db_name())
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select top 100
ColDetails.value('(@Schema)[1]','sysname') as SchemaName
,ColDetails.value('(@Table)[1]','sysname') as TableName
,ColDetails.value('(@Column)[1]','sysname') as ColumnName
,ColDetails.value('(@Alias)[1]','sysname') as TableAliasInQuery
,PhysicalOperator.value('(.//Object/@Index)[1]','sysname') as IndexName
,Cnvrt.value('(@DataType)[1]','sysname') as ConvertedDataType
,ty.name as ColumnDataType
--,Cnvrt.value('(@Implicit)[1]','sysname') as IsImplicit
--,qs.execution_count * ps.total_row_count as MostImportantFactor_total_row_processed
--,ps.total_row_count
--,qs.execution_count * used_page_count as MostImportantFactor_possible_number_of_logical_reads
--,ps.used_page_count
--,PhysicalOperator.value('(.//Predicate/ScalarOperator/@ScalarString)[1]','nvarchar(max)') as PredicateHavingFunction
,cast(substring(cast(st.text as nvarchar(max)),qs.statement_start_offset/2 + 1,(case when qs.statement_end_offset = -1 then datalength(cast(st.text as nvarchar(max))) else qs.statement_end_offset end - qs.statement_start_offset)/2) as nvarchar(max)) as QueryText
--,datalength(st.text) SQLtextLength
,st.text FullSqltext
,qp.query_plan as QueryPlan
,cp.usecounts,cp.refcounts,cp.objtype
,qs.sql_handle,
qs.creation_time,
qs.last_execution_time,
qs.total_worker_time,
qs.total_elapsed_time
  /*     qs.statement_start_offset,
       qs.statement_end_offset,
       qs.plan_handle,
       qs.creation_time,
       qs.last_execution_time,
       qs.execution_count,
       qs.query_hash,
       qs.query_plan_hash,
       qs.total_worker_time,
       qs.total_elapsed_time,
       qs.total_logical_reads,
       qs.total_physical_reads,
       qs.total_logical_writes,
       qs.total_rows,
       qs.total_worker_time/ qs.execution_count as worktime_per_exec,
       qs.total_elapsed_time / qs.execution_count as elasp_per_exec,
       qs.total_logical_reads / qs.execution_count as logiread_per_exec,
       qs.total_physical_reads / qs.execution_count as phyread_per_exec,
       qs.total_rows / qs.execution_count as rows_per_exec */
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
cross apply query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') as Statements(IndividualStatement)
cross apply IndividualStatement.nodes('.//RelOp[@PhysicalOp="Index Scan" or @PhysicalOp="Clustered Index Scan"]') as RelOp(PhysicalOperator)
cross apply PhysicalOperator.nodes('.//ScalarOperator/Convert') as Conversion(Cnvrt)
cross apply Cnvrt.nodes('./ScalarOperator/Identifier/ColumnReference') as Cols(ColDetails)
inner join sys.tables t on quotename(t.name) = ltrim(rtrim(ColDetails.value('(@Table)[1]','sysname'))) and t.type = 'U'
inner join sys.indexes id on t.object_id = id.object_id
and quotename(id.name) = ltrim(rtrim(PhysicalOperator.value('(.//Object/@Index)[1]','sysname')))
inner join sys.all_columns ac on t.object_id = ac.object_id and ac.name = ltrim(rtrim(ColDetails.value('(@Column)[1]','sysname')))
left join sys.types ty on ac.user_type_id = ty.user_type_id
left join sys.dm_exec_query_stats qs on cp.plan_handle= qs.plan_handle
--left join (select object_id,index_id,sum(used_page_count) as used_page_count, sum(row_count) as total_row_count from sys.dm_db_partition_stats group by object_id,Index_id)    as ps on id.object_id = ps.object_id and id.index_id = ps.index_id
outer apply sys.dm_exec_sql_text(cp.plan_handle) st
where
Cnvrt.value('(@Implicit)[1]','sysname')=1
and ColDetails.exist('.[@Database=sql:variable("@databasename")]') =1
and cp.cacheobjtype = 'Compiled Plan'
order by qs.total_worker_time desc

版本2

USE testdb
GO
DECLARE
    @db_name SYSNAME
;
SET
    @db_name = QUOTENAME(DB_NAME())
;

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
, planCache
AS(
    SELECT 
       stmt.value('(@StatementText)[1]', 'varchar(max)') AS stmt, 
       n.t.value('(ScalarOperator/Identifier/ColumnReference/@Database)[1]', 'sysname') AS DatabaseName,
       REPLACE(REPLACE(n.t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'sysname'), '[', ''), ']', '') AS SchemaName, 
       REPLACE(REPLACE(n.t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'sysname'), '[', ''), ']', '') AS ObjectName, 
       REPLACE(REPLACE(n.t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'sysname'), '[', ''), ']', '') AS ColumnName, 
       n.t.value('(@DataType)[1]', 'sysname') AS ConvertTo, 
       n.t.value('(@Length)[1]', 'int') AS ConvertToLength, 
       PhysicalOperator.value('(.//Predicate/ScalarOperator/@ScalarString)[1]','nvarchar(max)') AS ScalarString,
       query_plan,
       cp.plan_handle
    FROM sys.dm_exec_cached_plans AS cp 
        CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
        CROSS APPLY stmt.nodes('//ScalarOperator/Compare/ScalarOperator/Convert[@Implicit="1"]') AS n(t) 
        CROSS APPLY batch.stmt.nodes('.//RelOp[@PhysicalOp=''Index Scan'' or @PhysicalOp=''Clustered Index Scan'']') as RelOp(PhysicalOperator)
    WHERE n.t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@db_name")][@Schema != ''[sys]'']') = 1
)
SELECT 
    pc.stmt,
    pc.DatabaseName,
    pc.SchemaName,
    pc.ObjectName,
    pc.ColumnName,
    ps.UsedPageCount,
    ix.name AS IndexName,
    CAST(ps.UsedPageCount/ 128. AS decimal(12,2)) AS UsedSizeMB,
    ps.TotalRowCount,
    qs.execution_count * UsedPageCount AS MostLogicalRead,
    cols.DATA_TYPE AS ConvertFrom, 
    cols.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
    pc.ConvertTo,
    pc.ConvertToLength,
    pc.ScalarString,
    pc.query_plan,
    qs.creation_time
    ,qs.last_execution_time
    ,qs.execution_count
    ,qs.total_worker_time
    ,qs.last_worker_time
    ,qs.min_worker_time
    ,qs.max_worker_time
    ,qs.total_physical_reads
    ,qs.last_physical_reads
    ,qs.min_physical_reads
    ,qs.max_physical_reads
    ,qs.total_logical_writes
    ,qs.last_logical_writes
    ,qs.min_logical_writes
    ,qs.max_logical_writes
    ,qs.total_logical_reads
    ,qs.last_logical_reads
    ,qs.min_logical_reads
    ,qs.max_logical_reads
    ,qs.total_clr_time
    ,qs.last_clr_time
    ,qs.min_clr_time
    ,qs.max_clr_time
    ,qs.total_elapsed_time
    ,qs.last_elapsed_time
    ,qs.min_elapsed_time
    ,qs.max_elapsed_time
    ,qs.total_rows
    ,qs.last_rows
    ,qs.min_rows
    ,qs.max_rows
FROM planCache AS pc
    INNER JOIN INFORMATION_SCHEMA.COLUMNS as cols WITH(NOLOCK)
    ON pc.SchemaName = cols.TABLE_SCHEMA
    AND pc.ObjectName = cols.TABLE_NAME
    AND pc.ColumnName = cols.COLUMN_NAME
    INNER JOIN sys.tables as tb WITH(NOLOCK)
    ON tb.schema_id = schema_id(pc.SchemaName)
        AND tb.name = pc.ObjectName
    INNER JOIN sys.indexes as ix WITH(NOLOCK)
    ON tb.object_id = ix.object_id
    LEFT JOIN (
                SELECT 
                    object_id,
                    index_id,
                    sum(used_page_count) AS UsedPageCount,
                    sum(row_count) AS TotalRowCount 
                FROM sys.dm_db_partition_stats as dps WITH(NOLOCK)
                GROUP BY object_id,Index_id
                ) as ps 
    ON ix.object_id = ps.object_id and ix.index_id = ps.index_id
    left join sys.dm_exec_query_stats qs on pc.plan_handle= qs.plan_handle
--DBCC freeproccache

版本3

-- 按各种消耗排序
 
DECLARE @dbname SYSNAME  
SET @dbname = QUOTENAME(DB_NAME());  
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')  
SELECT top 100 stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text ,  
         t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS SchemaName ,  
         t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS TableName ,  
         t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS ColumnName ,  
         ic.DATA_TYPE AS ConvertFrom ,  
         ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength ,  
         t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo ,  
         t.value('(@Length)[1]', 'int') AS ConvertToLength ,  
         query_plan,
         qs.sql_handle,
       qs.statement_start_offset,
       qs.statement_end_offset,
       qs.plan_handle,
       qs.creation_time,
       qs.last_execution_time,
       qs.execution_count,
       qs.query_hash,
       qs.query_plan_hash,
       qs.total_worker_time,
       qs.total_elapsed_time,
       qs.total_logical_reads,
       qs.total_physical_reads,
       qs.total_logical_writes,
       qs.total_rows,
       qs.total_worker_time/ qs.execution_count as worktime_per_exec,
       qs.total_elapsed_time / qs.execution_count as elasp_per_exec,
       qs.total_logical_reads / qs.execution_count as logiread_per_exec,
       qs.total_physical_reads / qs.execution_count as phyread_per_exec,
       qs.total_rows / qs.execution_count as rows_per_exec
--FROM sys.dm_exec_cached_plans AS cp  
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp  
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')AS batch ( stmt )  
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n ( t )  
JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')  
    AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]','varchar(128)')  
    AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]','varchar(128)')  
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1 
and ic.DATA_TYPE != t.value('(@DataType)[1]', 'varchar(128)')
order by qs.total_worker_time desc

 

二、 带有索引列使用函数的sql

-- 找出列上使用函数且会导致index seek变为index scan的语句
-- find all the plans and query which are using the function call on the column which has index and this in turn is causing the index scan instead of index seek
 
 
declare @databasename sysname
set @databasename= quotename(db_name())
--find all the plans and query which are using the function call on the column which has index and this in turn is causing the index scan instead of index seek
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select top 100
ColDetails.value('(@Schema)[1]','sysname') as SchemaName
,ColDetails.value('(@Table)[1]','sysname') as TableName
,ColDetails.value('(@Column)[1]','sysname') as ColumnName
,ColDetails.value('(@Alias)[1]','sysname') as TableAliasInQuery
,PhysicalOperator.value('(.//Object/@Index)[1]','sysname') as IndexName
,Intrinsic.value('(@FunctionName)[1]','varchar(200)') as FunctionNameUsed
,PhysicalOperator.value('(.//IndexScan/Predicate/ScalarOperator/@ScalarString)[1]','nvarchar(max)') as PredicateHavingFunction
,qs.execution_count * ps.total_row_count as MostImportantFactor_total_row_processed
,ps.total_row_count
,qs.execution_count * used_page_count as MostImportantFactor_possible_number_of_logical_reads
,ps.used_page_count
,cast(substring(cast(st.text as nvarchar(max)),qs.statement_start_offset/2 + 1,(case when qs.statement_end_offset = -1 then datalength(cast(st.text as nvarchar(max))) else qs.statement_end_offset end - qs.statement_start_offset)/2) as nvarchar(max)) as QueryText
,datalength(st.text) SQLtextLength
,st.text FullSqltext
,qp.query_plan as QueryPlan
,cp.usecounts,cp.refcounts,cp.objtype
,qs.sql_handle,
       qs.statement_start_offset,
       qs.statement_end_offset,
       qs.plan_handle,
       qs.creation_time,
       qs.last_execution_time,
       qs.execution_count,
       qs.query_hash,
       qs.query_plan_hash,
       qs.total_worker_time,
       qs.total_elapsed_time,
       qs.total_logical_reads,
       qs.total_physical_reads,
       qs.total_logical_writes,
       qs.total_rows,
       qs.total_worker_time/ qs.execution_count as worktime_per_exec,
       qs.total_elapsed_time / qs.execution_count as elasp_per_exec,
       qs.total_logical_reads / qs.execution_count as logiread_per_exec,
       qs.total_physical_reads / qs.execution_count as phyread_per_exec,
       qs.total_rows / qs.execution_count as rows_per_exec
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
cross apply query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') as Statements(IndividualStatement)
cross apply IndividualStatement.nodes('.//RelOp[@PhysicalOp="Index Scan" or @PhysicalOp="Clustered Index Scan"]') as RelOp(PhysicalOperator)
cross apply PhysicalOperator.nodes('.//ScalarOperator/Intrinsic') as Conversion(Intrinsic)
cross apply Intrinsic.nodes('./ScalarOperator/Identifier/ColumnReference') as Cols(ColDetails)
inner join sys.tables t on quotename(t.name) = ltrim(rtrim(ColDetails.value('(@Table)[1]','sysname'))) and t.type = 'U'
inner join sys.indexes id on t.object_id = id.object_id
and quotename(id.name) = ltrim(rtrim(PhysicalOperator.value('(.//Object/@Index)[1]','sysname')))
left join sys.dm_exec_query_stats qs on cp.plan_handle= qs.plan_handle
left join (select object_id,index_id,sum(used_page_count) as used_page_count, sum(row_count) as total_row_count from sys.dm_db_partition_stats group by object_id,Index_id)    as ps
on id.object_id = ps.object_id and id.index_id = ps.index_id
outer apply sys.dm_exec_sql_text(cp.plan_handle) st
where
1=1
and ColDetails.exist('.[@Database=sql:variable("@databasename")]') =1
and cp.cacheobjtype = 'Compiled Plan'
order by qs.total_worker_time desc

 

三、 找到带全表扫描的sql

下面的语句查询执行计划中带有Table Scan的sql，这个语句非常容易改为查带别的扫描方式的sql，例如Cluster index scan,key lookup等。

-- Generate all query SQL text with "table scan" in cached query plan 
;WITH  XMLNAMESPACES 
    (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'   
            ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)
,EQS 
AS (
    SELECT EQS.plan_handle 
           ,SUM(EQS.execution_count) AS ExecutionCount 
           ,SUM(EQS.total_worker_time) AS TotalWorkTime 
           ,SUM(EQS.total_logical_reads) AS TotalLogicalReads 
           ,SUM(EQS.total_logical_writes) AS TotalLogicalWrites 
           ,SUM(EQS.total_elapsed_time) AS TotalElapsedTime 
           ,MAX(EQS.last_execution_time) AS LastExecutionTime 
     FROM sys.dm_exec_query_stats AS EQS 
     GROUP BY EQS.plan_handle
), info
AS(
    SELECT DISTINCT
        EQS.[ExecutionCount] 
        ,EQS.[TotalWorkTime] 
        ,EQS.[TotalLogicalReads] 
        ,EQS.[TotalLogicalWrites] 
        ,EQS.[TotalElapsedTime] 
        ,EQS.[LastExecutionTime] 
        ,ScanObject = StmtSimple.Node.value('(QueryPlan/RelOp//RelOp//TableScan/Object/@Database)[1]','sysname') + '.' +
                    StmtSimple.Node.value('(QueryPlan/RelOp//RelOp//TableScan/Object/@Schema)[1]','sysname') + '.' +
                    StmtSimple.Node.value('(QueryPlan/RelOp//RelOp//TableScan/Object/@Table)[1]','sysname')
        ,Statement = StmtSimple.Node.value('(@StatementText)[1]', 'nvarchar(max)')
        ,EST.text
        ,ECP.[objtype] AS [ObjectType] 
        ,ECP.[cacheobjtype] AS [CacheObjectType]
        ,EQS.plan_handle
    FROM sys.dm_exec_cached_plans AS ECP
        INNER JOIN EQS
        ON ECP.plan_handle = EQS.plan_handle
        CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST
        CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP
        CROSS APPLY EQP.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS StmtSimple(Node)
    WHERE StmtSimple.Node.exist('data(QueryPlan/RelOp//RelOp[@PhysicalOp="Table Scan"])') = 1
)
SELECT A.*, EQP.query_plan
FROM info AS A
    CROSS APPLY sys.dm_exec_query_plan(A.[plan_handle]) AS EQP
ORDER BY A.TotalElapsedTime DESC, A.ExecutionCount

 

四、 查询索引缺失sql

版本1

USE master
GO

DECLARE
    @EngineEdition INT = CAST(SERVERPROPERTY(N'EngineEdition') AS INT)
;

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,planCache
AS(
    SELECT 
        *
    FROM sys.dm_exec_query_stats as qs WITH(NOLOCK)
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
    WHERE qp.query_plan.exist('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex')=1
), analyedPlanCache
AS(
    SELECT 
        sql_text = T.C.value('(@StatementText)[1]', 'nvarchar(max)')
        ,[impact%] = T.C.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]', 'float')
        ,cachedPlanSize = T.C.value('(./QueryPlan/@CachedPlanSize)[1]', 'int')
        ,compileTime = T.C.value('(./QueryPlan/@CompileTime)[1]', 'int')
        ,compileCPU = T.C.value('(./QueryPlan/@CompileCPU)[1]', 'int')
        ,compileMemory = T.C.value('(./QueryPlan/@CompileMemory)[1]', 'int')
        ,database_name = T.C.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]','sysname')
        ,schema_name = T.C.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Schema)[1]','sysname')
        ,object_name = T.C.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]','sysname')
        ,equality_columns = (
            SELECT 
                DISTINCT REPLACE(REPLACE(tb.col.value('(@Name)[1]', 'sysname'), N']', N''), N'[', N'') + ','
            FROM T.c.nodes('./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup') AS T(cg)
                CROSS APPLY T.cg.nodes('./Column') AS tb(col)
            WHERE T.cg.value('(@Usage)[1]', 'sysname') = 'EQUALITY'
            FOR  XML PATH('')
        )
        ,inequality_columns = (
            SELECT 
                DISTINCT REPLACE(REPLACE(tb.col.value('(@Name)[1]', 'sysname'), N']', N''), N'[', N'') + ','
            FROM T.c.nodes('./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup') AS T(cg)
                CROSS APPLY T.cg.nodes('./Column') AS tb(col)
            WHERE T.cg.value('(@Usage)[1]', 'sysname') = 'INEQUALITY'
            FOR  XML PATH('')
        )
        ,include_columns = (
            SELECT 
                DISTINCT REPLACE(REPLACE(tb.col.value('(@Name)[1]', 'sysname'), N']', N''), N'[', N'@') + ','
            FROM T.c.nodes('./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup') AS T(cg)
                CROSS APPLY T.cg.nodes('./Column') AS tb(col)
            WHERE T.cg.value('(@Usage)[1]', 'sysname') = 'INCLUDE'
            FOR  XML PATH('')
        )
        ,pc.*
    FROM planCache AS pc
        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS T(C)
    WHERE C.exist('./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex') = 1
)
SELECT 
    plan_handle
    ,query_plan
    ,query_hash
    ,query_plan_hash
    ,sql_text
    ,[impact%]
    ,cachedplansize
    ,compileTime
    ,compileCPU
    ,compileMemory
    ,object = database_name + '.' + schema_name + '.' + object_name
    ,miss_index_creation = 
            N'CREATE NONCLUSTERED INDEX ' + QUOTENAME(N'IX_' + REPLACE(LEFT(equality_columns, len(equality_columns) - 1), N',', N'_') + '_'
            + REPLACE(LEFT(inequality_columns, len(inequality_columns) - 1), N',', N'_') + '_'
            + REPLACE(LEFT(include_columns, len(include_columns) - 1), N',', N'_'), '[]')
            + N' ON ' + database_name + '.' + schema_name + '.' + object_name 
            + QUOTENAME(
                CASE 
                    WHEN equality_columns is not null and inequality_columns is not null 
                        THEN equality_columns + LEFT(inequality_columns, len(inequality_columns) - 1)
                    WHEN equality_columns is not null and inequality_columns is null 
                        THEN LEFT(equality_columns, len(equality_columns) - 1)
                    WHEN inequality_columns is not null 
                        THEN LEFT(inequality_columns, len(inequality_columns) - 1)
                END
                , '()')
            + CASE 
                    WHEN include_columns is not null 
                    THEN ' INCLUDE ' + QUOTENAME(REPLACE(LEFT(include_columns, len(include_columns) - 1), N'@', N''), N'()')
                    ELSE ''
                END
            + N' WITH (FILLFACTOR = 90'
            + CASE @EngineEdition 
                WHEN 3 THEN N',ONLINE = ON' 
                ELSE ''
                END + ');'
    ,creation_time
    ,last_execution_time
    ,execution_count
    ,total_worker_time
    ,last_worker_time
    ,min_worker_time
    ,max_worker_time
    ,total_physical_reads
    ,last_physical_reads
    ,min_physical_reads
    ,max_physical_reads
    ,total_logical_writes
    ,last_logical_writes
    ,min_logical_writes
    ,max_logical_writes
    ,total_logical_reads
    ,last_logical_reads
    ,min_logical_reads
    ,max_logical_reads
    ,total_clr_time
    ,last_clr_time
    ,min_clr_time
    ,max_clr_time
    ,total_elapsed_time
    ,last_elapsed_time
    ,min_elapsed_time
    ,max_elapsed_time
    ,total_rows
    ,last_rows
    ,min_rows
    ,max_rows
FROM analyedPlanCache

版本2

USE TestDb
GO

SELECT TOP 100 
    c.equality_columns
    , c.inequality_columns
    , c.included_columns
    , create_Index = 'CREATE INDEX IX_' + REPLACE(REPLACE(REPLACE(equality_columns, '[', ''), ']',''), ',', '')
        + '_' + REPLACE(REPLACE(REPLACE(inequality_columns, '[', ''), ']',''), ',', '') 
        + '_' +REPLACE(REPLACE(REPLACE(included_columns, '[', '@'), ']',''), ', ', '_')  + char(10)
        + 'ON ' + SCHEMA_NAME(tb.schema_id) + '.' + object_name(tb.object_id) 
        + '('
            + case 
                when c.equality_columns is not null and c.inequality_columns is not null 
                    then c.equality_columns + ',' + c.inequality_columns
                when c.equality_columns is not null and c.inequality_columns is null 
                    then c.equality_columns
                when c.inequality_columns is not null 
                    then c.inequality_columns
            end
        + ')' + char(10)
        + case 
            when c.included_columns is not null 
            then 'INCLUDE (' + c.included_columns + ')'
            else ''
        end + char(10)
        + N'WITH (FILLFACTOR = 85);'
FROM sys.dm_db_missing_index_group_stats a
    INNER JOIN sys.dm_db_missing_index_groups b
        ON a.group_handle = b.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details c
        ON c.index_handle = b.index_handle
    INNER JOIN sys.tables as tb
        ON c.object_id = tb.object_id
WHERE db_name(database_id) = db_name()
    AND equality_columns is not null
    AND tb.object_id = object_id('dbo.SalesOrder', 'U')
ORDER BY a.avg_total_user_cost * a.avg_user_impact * (a.user_seeks + a.user_scans) DESC

 

五、 统计信息缺失sql

Use master
GO

;WITH XMLNAMESPACES
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,PlanCache 
AS(
    SELECT 
        EQP.query_plan,
        EQS.plan_handle,
        EST.text, 
        EQS.creation_time, 
        EQS.last_execution_time,
        EQS.execution_count,
        EQS.total_worker_time,
        EQS.last_worker_time,
        EQS.min_worker_time,
        EQS.max_worker_time,
        EQS.total_physical_reads,
        EQS.last_physical_reads,
        EQS.min_physical_reads,
        EQS.max_physical_reads,
        EQS.total_logical_writes,
        EQS.last_logical_writes,
        EQS.min_logical_writes,
        EQS.max_logical_writes,
        EQS.total_logical_reads,
        EQS.last_logical_reads,
        EQS.min_logical_reads,
        EQS.max_logical_reads,
        EQS.total_elapsed_time,
        EQS.last_elapsed_time,
        EQS.min_elapsed_time,
        EQS.max_elapsed_time,
        EQS.total_rows,
        EQS.last_rows,
        EQS.min_rows,
        EQS.max_rows
    FROM sys.dm_exec_query_stats AS EQS
      CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST
      CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP
)
SELECT
    batch.stmt.value('(@StatementText)[1]', 'varchar(max)') AS stmt,
    cast(T.C.query('local-name(..)') as varchar) AS warning, 
    total_Worker_time,
    Refer_Columns = 
        T.C.value('(@Database)[1]', 'sysname') + '.' +
        T.C.value('(@Schema)[1]', 'sysname') + '.' +
        T.C.value('(@Table)[1]', 'sysname') + '.' +
        T.C.value('(@Column)[1]', 'sysname'),
    QP.*
FROM PlanCache AS QP
    CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/Warnings/ColumnsWithNoStatistics/ColumnReference') T(C)
    CROSS APPLY QP.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)

 

六、 查询高编译时间的sql

WITH XMLNAMESPACES 
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP 10
CompileTime_ms,
CompileCPU_ms,
CompileMemory_KB,
qs.execution_count,
qs.total_elapsed_time/1000 AS duration_ms,
qs.total_worker_time/1000 as cputime_ms,
(qs.total_elapsed_time/qs.execution_count)/1000 AS avg_duration_ms,
(qs.total_worker_time/qs.execution_count)/1000 AS avg_cputime_ms,
qs.max_elapsed_time/1000 AS max_duration_ms,
qs.max_worker_time/1000 AS max_cputime_ms,
SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
(CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset) / 2 + 1) AS StmtText,
query_hash,
query_plan_hash
FROM
(SELECT 
c.value('xs:hexBinary(substring((@QueryHash)[1],3))', 'varbinary(max)') AS QueryHash,
c.value('xs:hexBinary(substring((@QueryPlanHash)[1],3))', 'varbinary(max)') AS QueryPlanHash,
c.value('(QueryPlan/@CompileTime)[1]', 'int') AS CompileTime_ms,
c.value('(QueryPlan/@CompileCPU)[1]', 'int') AS CompileCPU_ms,
c.value('(QueryPlan/@CompileMemory)[1]', 'int') AS CompileMemory_KB,
qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS n(c)) AS tab
JOIN sys.dm_exec_query_stats AS qs
ON tab.QueryHash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY CompileTime_ms DESC
OPTION(RECOMPILE, MAXDOP 1);

 

七、 查询缓存中存在并行的sql

SELECT TOP 10
        p.* ,
        q.* ,
        qs.* ,
        cp.plan_handle
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.Dm_exec_query_plan(cp.plan_handle) p
        CROSS APPLY sys.Dm_exec_sql_text(cp.plan_handle) AS q
        JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
WHERE   cp.cacheobjtype = 'Compiled Plan'
        AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/SQL Server/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
OPTION  ( MAXDOP 1 );

 

八、 Single-used Plans相关sql

Single-used Plans是指那些第一次执行后被缓存起来的执行计划，而后再也没有被重复利用过的执行计划缓存。其中ad hoc query（即席查询）就是典型的single-used plans中的一种。

single-used plans仅会第一次被使用（从名字也可以很好理解到这一点），所以，实际上single-used plans是对SQL Server内存空间和CPU资源的浪费，对数据库性能有一定的损害。

 

1. 如何发现single-used plans

USE master
GO

SELECT
    database_name = QUOTENAME(db.name),
    st.text,
    cp.objtype,
    cp.size_in_bytes,
    qp.query_plan,
    cp.cacheobjtype
FROM sys.dm_exec_cached_plans AS cp 
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
    INNER JOIN sys.databases AS db WITH(NOLOCK)
    ON st.dbid = db.database_id
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype IN (N'Adhoc', N'Prepared')
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC

 

2. 计划缓存中有多少Single-used Plans

当可以有效的发现single-used plans以后，我们可能又会问：到底SQL Server中有多少执行计划缓存是属于single-used plans类型呢？可以从两个维度来分析：

• Single-used plans记录总数
• Single-used plans总的执行计划占用的内存空间大小

可以使用以下的查询语句来回答这个问题。

USE master
GO
SELECT
    COUNT(1) AS [total_plans], 
    CAST(SUM(size_in_bytes)/1024. AS DECIMAL(18,2)) AS [total_size_in_kb], 
    CAST(SUM((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END))/1024. AS DECIMAL(12,2)) AS [total_size_single_used_in_kb], 
    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [total_plans_count_single_used],
    AVG(usecounts) AS [avg_used_counts], 
    CAST(AVG(size_in_bytes)/1024. AS DECIMAL(12, 2)) AS [avg_used_size_kb]
FROM sys.dm_exec_cached_plans WITH(NOLOCK)

就单单一条Single-used Plan来看的话，对数据库系统的性能影响很小，小到可以忽略的地步，所以，数据库执行计划缓存中，存在少量的Single-used Plans是很正常的，可以不用太关注。但是如果Single-used Plans大量存在的话，可能就会对系统带来比较严重的性能影响。

 

3. 定性性能分析

定性分析大量Single-used Plans对数据性能的影响，主要体现在以下几个方面：

• 总的执行计划缓存利用率不高：存在大量不能被重复利用的执行计划缓存，拉低了执行计划缓存整体利用率
• 浪费执行计划缓存中内存的开销：每条执行计划缓存或多或少会占用一定的执行计划缓存内存空间，大量的Single-used Plans导致了内存空间的浪费
• 导致CPU使用率的上升：每条SQL语句执行计划的编译、最优路径选择和执行计划缓存，都需要消耗系统CPU资源，如果大量存在Single-used Plans，会导致系统CPU使用率的上升。

举一个最为极端的例子，假设所有执行计划都是Single-used Plans的话，那么导致的严重后果是：

• 执行计划缓存利用率就是0%，因为没有任何的执行计划被重用
• 执行计划缓存这种设计就毫无意义，因为缓存起来也没有被重用
• 浪费执行计划缓存的内存开销和CPU开销

 

4. 定量性能分析

以上是定性分析Single-used Plans对系统性能的影响，那么到底Single-used Plans达到哪个数量级，占比多少的时候，我们需要密切关注呢？虽然微软没有官方的推荐数字，但是个人比较推荐的两个数字是2GB和50%，即：所有的Single-used Plans使用的内存空间超过2GB或者内存空间使用占比超过50%。当然最终也可能取决于SQL Server可以使用的最大内存数量。

USE master
GO

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp

SELECT 
    objtype AS [cachetype], 
    COUNT(1) AS [total_plans], 
    CAST(SUM(size_in_bytes)/1024. AS DECIMAL(18,2)) AS [total_size_in_kb], 
    CAST(SUM((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END))/1024. AS DECIMAL(12,2)) AS [total_size_single_used_in_kb], 
    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [total_plans_count_single_used],
    AVG(usecounts) AS [avg_used_counts], 
    CAST(AVG(size_in_bytes)/1024. AS DECIMAL(12, 2)) AS [avg_used_size_kb]
INTO #temp
FROM sys.dm_exec_cached_plans WITH(NOLOCK)
GROUP BY objtype

SELECT 
    [cachetype],
    [total_plans],
    [total_size_in_kb],
    [total_plans_count_single_used],
    [total_size_single_used_in_kb],
    CAST(([total_plans_count_single_used]*1.0 / [total_plans]*1.0) * 100. AS DECIMAL(12, 2)) AS [single_used_plan_count%],
    CAST([total_size_single_used_in_kb]/[total_size_in_kb] * 100. AS DECIMAL(12, 2)) AS [single_used_size%],
    [avg_used_counts],
    [avg_used_size_kb]
FROM #temp
ORDER BY [total_size_single_used_in_kb] DESC

SELECT 
    SUM([total_plans]) AS total_plan_counts,
    SUM([total_size_in_kb]) AS total_plan_size_in_kb,
    SUM([total_plans_count_single_used]) AS [total_plans_counts_single_used],
    SUM([total_size_single_used_in_kb]) AS [total_plan_size_single_used_in_kb],
    CAST(SUM([total_plans_count_single_used]) * 100. / SUM([total_plans]) AS DECIMAL(12, 2)) AS [plan_counts_single_used%],
    CAST(SUM([total_size_single_used_in_kb]) * 100. / SUM([total_size_in_kb]) AS DECIMAL(12, 2)) AS [plan_size_single_used%]
FROM #temp

执行结果如下所示：

从这个执行结果来看，single-used plans占用的执行计划记录数为72条，内存空间占用14016 KB；single-used plans执行计划记录数占总的百分比为39.78%，内存空间占用比例为50.59%。

那么如何解决single-used plans问题呢？

 

5. 清空Single-used Plans

第一种方法是手动清理single-used plans，当然，你也可以很暴力的将所有执行计划缓存清空，从而也就清理了single-used plans，但是这样会误杀很多有用的无辜的执行计划缓存，从而导致这些执行计划重编译，影响性能。我们推荐使用下面的方法，仅清空哪些single-used plans，方法如下：

USE master
GO

DECLARE
    @plan_handle varbinary(64)
;

DECLARE
     cur_single_used_plan_handle CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp WITH(NOLOCK)
WHERE usecounts = 1

OPEN cur_single_used_plan_handle
FETCH NEXT FROM cur_single_used_plan_handle INTO @plan_handle
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @plan_handle

    DBCC FREEPROCCACHE (@plan_handle); 

    FETCH NEXT FROM cur_single_used_plan_handle INTO @plan_handle
END

CLOSE cur_single_used_plan_handle
DEALLOCATE cur_single_used_plan_handle
GO

 

6. optimize for ad hoc workloads配置选项

除了手动清理single-used plans执行计划缓存外，另外一个更强大的功能是修改SQL Server实例级别的配置选项optimize for ad hoc workloads。为了解决single-used plans带来的问题，从SQL Server 2008开始引入了这个选项。打开以后，ad hoc的查询语句在第一次执行时，系统会创建一个“compiled plan stub”，并不会将执行计划缓存起来，只有当第二次该语句再被执行时，执行计划才会被缓存，从而避免了single-used plans带来的问题。配置这个选项的方法很简单，参见以下语句：

EXEC sys.sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sys.sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE
GO

 

参考

https://yq.aliyun.com/articles/180914?spm=a2c4e.11155435.0.0.26865adcjVcuh5

https://yq.aliyun.com/articles/106489?spm=a2c4e.11155435.0.0.26865adcjVcuh5

https://yq.aliyun.com/articles/93785?spm=a2c4e.11155435.0.0.26865adcjVcuh5

https://yq.aliyun.com/articles/72265?spm=a2c4e.11153940.0.0.6b863e27HTvUtV

https://yq.aliyun.com/articles/225035?spm=a2c4e.11155435.0.0.1ab45adcZS12AB

https://yq.aliyun.com/articles/115975?spm=a2c4e.11155435.0.0.26865adcjVcuh5