893
技術社區[雲棲]
RDS SQL Server - 專題分享 - 巧用執行計劃緩存之Key Lookup
背景引入
執行計劃緩存是SQL Server內存管理中非常重要的特性，這篇文章是巧用執行計劃緩存係列文章之四，探討什麼是Key Lookup操作，如何從執行計劃緩存中發現Key Lookup問題，以及如何解決這個問題。
什麼是Key Lookup
Key Lookup操作是指執行計劃通過表的索引查找字段列的書簽查找方式。Key Lookup發生在當查詢語句使用Index Seek（或者Index Scan）的同時，又需要查找Index中沒有完全包含的額外字段列，這時SQL Server必須回過頭來獲取額外的字段列的值。通常情況下Key Lookup操作是通過表聚集索引來查找字段列的值，因此，可能會導致昂貴的查詢性能開銷，在性能優化過程中，需要引起我們足夠的重視。
如何發現Key Lookup
在性能優化過程中，執行計劃中的Key Lookup操作是我們優化的重點之一，那麼我們如何發現Key Lookup操作呢？本文介紹兩種方法：
執行計劃圖形展示
執行計劃緩存中查找
執行計劃圖形展示
SQL Server客戶端工具SSMS可以圖形化方式直觀的展示執行計劃圖形，我們可以通過這個直觀的做圖來發現Key Lookup操作。比如，我們有如下查詢語句，在執行之前，我們打開實際執行計劃采集開關（可以使用快捷鍵CTRL + M）。
USE [AdventureWorks2008R2];
GO
SELECT
NationalIDNumber,
HireDate,
MaritalStatus
FROM HumanResources.Employee WITH(NOLOCK)
WHERE NationalIDNumber = N'519899904';
GO
語句執行完畢後，會有Execution Plan窗口，從這裏我們可以很輕易的看到Key Lookup事件操作，將鼠標懸停在Key Lookup事件上，會有彈窗展示更為詳細的信息。詳情參見如下截圖，我們可以看到Key Lookup的性能開銷為50%，占了整個查詢語句性能開銷的一半。開銷算是很高的了。
執行計劃緩存中查找
我們除了可以通過執行計劃圖形展示的方式來發現Key Lookup操作以外，我們還可以查找執行計劃緩存來讓Key Lookup操作無所遁形。比如，下麵截圖中信息是剛才執行語句的執行計劃緩存中Key Lookup操作的XML節點。
所以，我們隻需要搜索執行計劃緩存，就可以找出哪些執行過的語句使用了Key Lookup操作，就可以針對性的進行性能優化。查找執行計劃緩存的方法如下：
;WITH XMLNAMESPACES (DEFAULT 'https://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
--T.C.query('.')
database_name = T.C.value('(IndexScan/Object/@Database)[1]','sysname')
,Schema_name = T.C.value('(IndexScan/Object/@Schema)[1]','sysname')
,Table_name = T.C.value('(IndexScan/Object/@Table)[1]','sysname')
,Index_name = T.C.value('(IndexScan/Object/@Index)[1]','sysname')
,index_type = T.C.value('(IndexScan/Object/@IndexKind)[1]','sysname')
,sql_text = T.C.value('(../../../../@StatementText)[1]','nvarchar(max)')
,output_columns =STUFF(( SELECT DISTINCT ', ' + cr.n.value('(@Column)[1]', 'sysname')
FROM T.C.nodes('../../OutputList/ColumnReference') as cr(n)
FOR XML PATH('')
), 1, 2, '' )
,seek_columns =STUFF(( SELECT DISTINCT ', ' + cr.n.value('(@Column)[1]', 'sysname')
FROM T.C.nodes('IndexScan/SeekPredicates/SeekPredicateNew/SeekKeys/Prefix/RangeColumns/ColumnReference') as cr(n)
FOR XML PATH('')
), 1, 2, '' )
,Predicate = T.C.value('(IndexScan/SeekPredicates/SeekPredicateNew/SeekKeys/Prefix/RangeExpressions/ScalarOperator/@ScalarString)[1]', 'nvarchar(max)')
--,cp.usecounts
--,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/QueryPlan/RelOp/NestedLoops/RelOp[IndexScan[@Lookup="1"] and IndexScan/Object[@Schema!="[sys]"]]') as T(C)
WHERE T.C.exist('../RelOp[IndexScan[@Lookup="1"] and IndexScan/Object[@Schema!="[sys]"]]') = 1
比如，剛才的執行語句就被抓出來了，展示如下截圖：
解決Key Lookup問題
從以上的分析，我們知道了Key Lookup對性能的影響，以及如何發現Key Lookup操作的語句，接下來的任務就是如何解決Key Lookup問題了。通常我們有如下方法：
刪除不必要字段列
創建覆蓋索引
刪除不必要的字段列
這個解決方法很好理解，因為使用Key Lookup操作的目的就是為了查找SELECT字句中的字段列，如果我們將業務不必要，或者可要可不需要的字段列刪除的話，很可能SQL Server就不會再走Key Lookup操作了，因此也就解決了這個問題。一個非常典型的場景是，很多開發人員喜歡使用SELECT * FROM操作。最好的方式是顯示羅列所有業務必須要使用的字段名字，而不是一股腦兒全部字段都查詢出來。
創建覆蓋索引
如果萬一，SELECT中的所有字段都是你業務所必須的，無法刪除的話，我們可以考試使用覆蓋索引來解決Key Lookup問題，即創建索引的時候，使用INCLUDE字句將SELECT後的字段包含在其中（排除在ON字句中字段，比如這裏的NationalIDNumber字段列）。比如，上麵的查詢語句，我們可以創建覆蓋索引：
USE [AdventureWorks2008R2];
GO
CREATE NONCLUSTERED INDEX IX_NationalIDNumber_@HireDate_@MaritalStatus
ON HumanResources.Employee (NationalIDNumber)
INCLUDE(HireDate, MaritalStatus)
WITH(FILLFACTOR = 90, ONLINE = ON)
執行完畢後，再次執行該語句，查看執行計劃，僅一個Index Seek，沒有Key Lookup操作，說明這個問題已經得到了解決。詳情參見以下截圖：
友情提醒
如果使用SSMS查看執行SQL語句的實際執行計劃，XML 中IndexScan節點的Lookup屬性值為True，如下展示：
...
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
...
而從執行計劃緩存中獲取到的IndexScan節點中Lookup屬性值為1。如下展示：
...
<IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0">
...
因此，我們在分析執行計劃緩存中的Key Lookup操作的時候，需要檢查Lookup的值是否是1，而不是檢查它是否為true。以下是SSMS執行計劃中的XML節點，Lookup屬性值為true，而在“執行計劃緩存中查找”章節中的截圖，我們知道Lookup屬性值為1。
最後總結
這篇文章討論了在性能優化過程中經常要遇到的一個需要優化的點叫Key Lookup操作，以及我們如何發現Key Lookup，最後談到了兩種解決Key Lookup問題的方法。
引用文章
Finding Key Lookups inside the Plan Cache
最後更新：2017-07-20 11:03:11