Plan Cache Key Lookups: Finding the Worst Ones With XQuery

Plan cache key lookups are easy to find if you rank statements by work first and only then read their plans with XQuery. That order keeps you from chasing a harmless lookup.

Why a key lookup is not always a villain

A key lookup happens when an index finds the rows but lacks a column the query wants. SQL Server then goes back to the clustered index for each row. One lookup is cheap. Thousands per call add up.

Someone on your team will say, “Add INCLUDE columns to every index.” Please don’t. Wide indexes cost space and slow writes. Find the lookups that really hurt first. The plan cache can tell you, and a little XQuery does the digging.

Build a table that needs a lookup

The demo creates a database called SqlAuthorityDemo and drops it at the end. The table has 50,000 rows in 2,000 groups, so each group holds 25 rows. The index on GroupId does not contain Payload. The procedure asks for both.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.LookupDemo (
    Id      int NOT NULL CONSTRAINT PK_LookupDemo PRIMARY KEY,
    GroupId int NOT NULL,
    Payload char(100) NOT NULL
);
CREATE INDEX IX_LookupDemo_Group ON dbo.LookupDemo (GroupId);

INSERT dbo.LookupDemo (Id, GroupId, Payload)
SELECT value, value % 2000, 'x' FROM GENERATE_SERIES(1, 50000);
GO
CREATE PROCEDURE dbo.GetGroup @GroupId int AS
SELECT Id, Payload FROM dbo.LookupDemo WHERE GroupId = @GroupId;

Now call the procedure 20 times, so the cache has something to report. I send the rows to a temp table so they do not flood your screen.

DROP TABLE IF EXISTS #Sink;
CREATE TABLE #Sink (Id int, Payload char(100));

DECLARE @i int = 0;
WHILE @i < 20
BEGIN
    INSERT #Sink EXEC dbo.GetGroup @GroupId = 7;
    SET @i += 1;
END;

Rank by work first

This query lists the statements that did the most logical reads in the current database. It uses the statement offsets to cut each statement out of its batch text.

SELECT TOP (5) qs.execution_count, qs.total_logical_reads,
       qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
       SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
           (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(st.text)
                 ELSE qs.statement_end_offset END
            - qs.statement_start_offset) / 2 + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE EXISTS (SELECT 1 FROM sys.dm_exec_plan_attributes(qs.plan_handle) AS a
              WHERE a.attribute = N'dbid' AND CONVERT(int, a.value) = DB_ID())
ORDER BY qs.total_logical_reads DESC, qs.plan_handle;

The first row is the load script I just ran. Ignore it, it is setup. The row starting with INSERT #Sink is my loop calling the procedure. The real subject is the SELECT inside the procedure: 20 executions and about 131 logical reads each on average. A few system housekeeping statements may appear too.

Logical reads belong to the whole statement. They are not a separate bill for the lookup alone. Keep that in mind when you read the numbers.

Find the lookups with XQuery

Now the XQuery. It takes the top 25 statements by reads, fetches each statement’s own plan, and looks for a lookup into the clustered index. Using the offsets matters. Without them you would attribute every lookup in a batch to every statement in it.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
Candidates AS (
    SELECT TOP (25) qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset,
           qs.execution_count, qs.total_logical_reads
    FROM sys.dm_exec_query_stats AS qs
    WHERE EXISTS (SELECT 1 FROM sys.dm_exec_plan_attributes(qs.plan_handle) AS a
                  WHERE a.attribute = N'dbid' AND CONVERT(int, a.value) = DB_ID())
    ORDER BY qs.total_logical_reads DESC, qs.plan_handle
),
Plans AS (
    SELECT c.*, TRY_CONVERT(xml, p.query_plan) AS PlanXml
    FROM Candidates AS c
    CROSS APPLY sys.dm_exec_text_query_plan(c.plan_handle,
                c.statement_start_offset, c.statement_end_offset) AS p
)
SELECT p.execution_count, p.total_logical_reads,
       n.r.value('(Object/@Table)[1]', 'nvarchar(256)') AS TableName,
       n.r.value('(Object/@Index)[1]', 'nvarchar(256)') AS IndexName
FROM Plans AS p
CROSS APPLY p.PlanXml.nodes('//RelOp/IndexScan[@Lookup="1" and Object/@IndexKind="Clustered"]') AS n(r)
ORDER BY p.total_logical_reads DESC, p.plan_handle;

One row comes back: LookupDemo, with the clustered primary key PK_LookupDemo as the index, 20 executions and 2,620 reads in total. That is your lead. It is not a verdict.

Two cautions. The cache is temporary, and its numbers cover only the time since that plan entered the cache. And an empty result can simply mean nothing in the cache has a lookup.

Test the smallest fix

Look at one call on its own. STATISTICS IO shows the reads for a single execution.

SET STATISTICS IO ON;
EXEC dbo.GetGroup @GroupId = 7;
SET STATISTICS IO OFF;

In my run the call took 77 logical reads. Now make the index cover the query by adding Payload as an included column. This is the narrowest change I can think of. It keeps the same key, so the seek stays the same.

CREATE INDEX IX_LookupDemo_Group ON dbo.LookupDemo (GroupId)
    INCLUDE (Payload) WITH (DROP_EXISTING = ON);
GO
SET STATISTICS IO ON;
EXEC dbo.GetGroup @GroupId = 7;
SET STATISTICS IO OFF;

The second call needed 3 logical reads in my run. The lookups are gone. That looks like a big win, but the index is now wider. Before you copy the idea, measure the write cost on a test copy and check that the results are identical.

A lookup is a plan shape, not a defect in the index. Fix the ones that cost a lot per call or run all day. Leave the rest alone. Here is the cleanup.

DROP TABLE IF EXISTS #Sink;
USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Rank the statements first, then read their plans, and you will fix fewer things for better reasons.

A key lookup is not a defect, it is repeated work worth checking against the workload.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.