title: RDS SQL Server - 专题分享 - 巧用执行计划缓存之索引缺失
author: 风移
摘要
执行计划缓存是MSSQL Server内存管理十分重要的部分，同样如何巧用执行计划缓存来解决我们平时遇到的一系列问题也是一个值得深入研究的专题。这篇文章是如何巧用执行计划缓存的开篇，分享如何使用执行计划缓存来分析索引缺失（Missing Indexes）。
问题引入
缺失索引是SQL Server CPU使用率居高不下的第一大杀手，也是SQL Server数据库非常大的潜在风险点。在之前的高CPU使用率系列文章中，我们分享了使用系统动态视图的方法来获取索引缺失的方法，详情请戳：RDS SQL Server - 最佳实践 - 高CPU使用率系列之索引缺失。那么有没有其他的方法既可以获取到缺失索引，还能够展示相应查询语句执行计划中有价值的详细信息呢？这篇文章从执行计划缓存的角度和视野来获取缺失索引，并且对相应执行计划有价值的信息进行了详细展示，包括单不仅限于：
创建缺失索引对查询性能的提升预估百分比
执行计划针对的查询语句、数据库对象
执行计划创建时间和最后使用时间
执行计划缓存大小、编译时间、CPU和内存消耗
最小、最大、最后一次和总共消耗CPU的时间
最小、最大、最后一次和总共IO物理、逻辑读写
最小、最大、最后一次和总共影响的行数
......
场景分析
MSSQL Server引擎，在执行特定语句时，需要对语句进行语法检查、语义分析、编译、最佳执行路径选择、生成执行计划和缓存执行计划，以便下次执行相同语句时，可以直接从执行计划缓存中获取执行计划，以节约性能开销和提升查询语句执行性能。执行计划缓存中有非常多有价值的信息，那么我们如何有效利用执行计划缓存来帮助我们分析系统存在的潜在风险和性能问题呢？本篇文章分享巧用执行计划缓存来获取缺失索引。
测试环境
测试环境搭建和相应查询语句参见之前的文章RDS SQL Server - 最佳实践 - 高CPU使用率系列之索引缺失中的测试环境和执行查询部分，在此不再累述。
解决方法
前面做了很多铺垫关于背景的介绍，执行计划缓存基础理论，终于到了激动人心的解决方法部分了。一言不合，直接上代码：
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执行完毕以后的结果展示如下，由于结果集太长，人为分为四段结果集：
第一段结果集截图
第二段结果集截图
第三段结果集截图
第四段结果集截图
点开第一个张截图中的其中一行query_plan xml，我们查看到的Missing Indexes信息节点：
从截图中，我们同样可以找到非常有用的信息，包括：
创建索引后的性能提升为99.8369%（第11行）
缺失索引的数据库对象，包括数据库名，架构名和表名称（第12行）
相等谓词使用的缺失索引列（第13行）
不相等谓词使用的缺失索引列（第16行）
覆盖字段的缺失索引列（第19行）
注意事项
由于执行计划缓是保存在SQL OS的内存中，所以会随着以下动作被自动或被动清空：
SQL Server Service重启
操作系统重启
人为清空缓存
系统感觉到内存压力自动回收等
当这些动作发生以后，再通过执行计划缓存来获取有效信息，可能会导致信息获取不完整。所以，使用本篇文章方法获取缺失索引信息之前，请确保你的SQL Server系统已经充分Warm Up。
最后总结
这篇文章是巧用执行计划缓存系列文章的开篇，详细讲解了如何使用执行计划缓存来获取缺失索引信息以及执行计划的一些有价值的详细信息，以此来破解RDS SQL Server高CPU使用率的问题。
本文的视频演示,我已经上传到Youku，详情请戳 MSSQL Server巧用执行计划缓存获取缺失索引信息