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

蔚落 2022-06-12 10:20 278阅读 0赞

[原文链接][Link 1]

摘要： \# 背景引入 执行计划缓存是SQL Server内存管理中非常重要的特性，这篇文章是巧用执行计划缓存系列文章之四，探讨什么是Key Lookup操作，如何从执行计划缓存中发现Key Lookup问题，以及如何解决这个问题。 \# 什么是Key Lookup Key Lookup操作是指执行计划通过表的索引查找字段列的书签查找方式。Key Lookup发生在当查询语句使用Index Se

# 背景引入 #

执行计划缓存是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%，占了整个查询语句性能开销的一半。开销算是很高的了。  
![01.png][]

## 执行计划缓存中查找 ##

我们除了可以通过执行计划图形展示的方式来发现Key Lookup操作以外，我们还可以查找执行计划缓存来让Key Lookup操作无所遁形。比如，下面截图中信息是刚才执行语句的执行计划缓存中Key Lookup操作的XML节点。  
![02.png][]

所以，我们只需要搜索执行计划缓存，就可以找出哪些执行过的语句使用了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

比如，刚才的执行语句就被抓出来了，展示如下截图：  
![03.png][]

# 解决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操作，说明这个问题已经得到了解决。详情参见以下截图：  
![04.png][]

# 友情提醒 #

如果使用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属性

![Center][]

[原文链接][Link 1]

[Link 1]: http://click.aliyun.com/m/26465/
[01.png]: /images/20220612/b6f3b5ec082d47b48bd8bbdeb0918459.png
[02.png]: /images/20220612/6ab28fe7a5d84ed4afbd70df4e91a2ef.png
[03.png]: /images/20220612/b1e59a25a4d242e594b4dbe841f6147e.png
[04.png]: /images/20220612/4cf3010cbeb14f0283885798ad20ef8f.png
[Center]: /images/20220612/4515c378d71540d09cd455a74749ae6f.png