title: RDS SQL Server - 专题分享 - 巧用执行计划缓存之索引缺失

author: 风移

摘要

执行计划缓存是MSSQL Server内存管理十分重要的部分，同样如何巧用执行计划缓存来解决我们平时遇到的一系列问题也是一个值得深入研究的专题。这篇文章是如何巧用执行计划缓存的开篇，分享如何使用执行计划缓存来分析索引缺失（Missing Indexes）。

问题引入

缺失索引是SQL Server CPU使用率居高不下的第一大杀手，也是SQL Server数据库非常大的潜在风险点。在之前的高CPU使用率系列文章中，我们分享了使用系统动态视图的方法来获取索引缺失的方法，详情请戳：。那么有没有其他的方法既可以获取到缺失索引，还能够展示相应查询语句执行计划中有价值的详细信息呢？这篇文章从执行计划缓存的角度和视野来获取缺失索引，并且对相应执行计划有价值的信息进行了详细展示，包括单不仅限于：

创建缺失索引对查询性能的提升预估百分比

执行计划针对的查询语句、数据库对象

执行计划创建时间和最后使用时间

执行计划缓存大小、编译时间、CPU和内存消耗

最小、最大、最后一次和总共消耗CPU的时间

最小、最大、最后一次和总共IO物理、逻辑读写

最小、最大、最后一次和总共影响的行数

......

场景分析

MSSQL Server引擎，在执行特定语句时，需要对语句进行语法检查、语义分析、编译、最佳执行路径选择、生成执行计划和缓存执行计划，以便下次执行相同语句时，可以直接从执行计划缓存中获取执行计划，以节约性能开销和提升查询语句执行性能。执行计划缓存中有非常多有价值的信息，那么我们如何有效利用执行计划缓存来帮助我们分析系统存在的潜在风险和性能问题呢？本篇文章分享巧用执行计划缓存来获取缺失索引。

测试环境

测试环境搭建和相应查询语句参见之前的文章中的测试环境和执行查询部分，在此不再累述。

解决方法

前面做了很多铺垫关于背景的介绍，执行计划缓存基础理论，终于到了激动人心的解决方法部分了。一言不合，直接上代码：

USE masterGODECLARE    @EngineEdition INT = CAST(SERVERPROPERTY(N'EngineEdition') AS INT);;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),planCacheAS(    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), analyedPlanCacheAS(    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_rowsFROM analyedPlanCache

执行完毕以后的结果展示如下，由于结果集太长，人为分为四段结果集：

第一段结果集截图