1015
技術社區[雲棲]
RDS SQL Server - 專題分享 - 巧用執行計劃緩存之索引缺失
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 'https://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使用率的問題。
最後更新：2017-06-05 11:32:21