RDS SQL Server - 专题分享 - 巧用执行计划缓存之Key Lookup

2022-02-11 17:08:08

背景引入

执行计划缓存是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 'http://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

码农公寓