Key Lookup Elimination

Analyzes the plan cache for key lookups and recommends adding INCLUDE columns to eliminate them. Catches performance problems that the missing index DMVs completely miss.

What Is a Key Lookup?

A key lookup (also called a bookmark lookup) occurs when the query optimizer:

1. Seeks a nonclustered index to find the matching rows efficiently
2. Then goes back to the clustered index (or heap RID) for each row to retrieve additional columns that aren't in the nonclustered index

This means every qualifying row requires two I/O operations instead of one. For a query returning 10,000 rows, that's 10,000 extra random reads against the clustered index.

How It Detects Key Lookups

Plan Cache Scanning

The tool makes a single server-wide pass through the plan cache using sys.dm_exec_query_stats joined to sys.dm_exec_query_plan(). This captures every actively-used execution plan with its accumulated execution counts since the last service restart.

XQuery Detection

The showplan XML represents key lookups as IndexScan[@Lookup="1"], not as LogicalOp="Key Lookup". The "Key Lookup" label only appears in SSMS's graphical plan display. The proc uses .exist() with the showplan XML namespace for reliable detection:

-- This is how the proc finds key lookups in plan XML
-- The string 'Key Lookup' does NOT appear in the raw XML
query_plan.exist('
  declare namespace p = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
  //p:IndexScan[@Lookup="1"]
') = 1

Database Identification

The database name is extracted from the plan XML's Object/@Database attribute on the nonclustered index seek node — not from sys.dm_exec_query_plan.dbid, which is unreliable for parameterized queries and stored procedures that cross database boundaries.

Analysis Pipeline

sp_KeyLookupRecommendations(@RunAnalysis = 1)
  |
  +--> sp_KeyLookupAnalysis(@DatabaseName, @MinExecutions)
  |      |
  |      +--> Step 1: Clear unresolved results (preserve resolved history)
  |      |
  |      +--> Step 2: Collect top plans from plan cache
  |      |      - sys.dm_exec_query_stats ranked by total_logical_reads
  |      |      - Filter: only plans containing IndexScan[@Lookup="1"]
  |      |      - One server-wide pass (no per-database loop)
  |      |
  |      +--> Step 3: XQuery extraction per plan
  |      |      - From each Nested Loops node:
  |      |        - Database: NC index seek's Object/@Database
  |      |        - NC Index: IndexScan[not(@Lookup="1")]/Object/@Index
  |      |        - Lookup Cols: IndexScan[@Lookup="1"]/OutputList/ColumnReference
  |      |
  |      +--> Step 4: Filter to target databases
  |      |
  |      +--> Step 5: Consolidate
  |      |      - GROUP BY database + table + NC index
  |      |      - UNION all lookup columns across plans
  |      |      - SUM execution stats (reads, executions, plan count)
  |      |
  |      +--> Step 6: Post-process per result (dynamic SQL per database)
  |             - Query sys.indexes + sys.index_columns for current definition
  |             - Filter out columns already in NC index keys or includes
  |             - Calculate current size from sys.dm_db_partition_stats
  |             - Sample AVG(DATALENGTH) for each new column (10K rows)
  |             - Generate index name: IX_Table_Keys[_INCLUDE|_INCLUDES]
  |             - Generate CREATE + DROP statements
  |             - Delete results where all lookups already covered
  |
  +--> Generate ScriptText ranked by total logical reads

Naming Convention

Generated index names follow a strict convention: IX_TableName_KeyColumns[_INCLUDE|_INCLUDES]

Size Estimation

The tool uses proportional scaling with actual data sampling — not max_length guesses.

How It Works

1. Get actual average row size from sys.dm_db_index_physical_stats (SAMPLED mode) for the existing nonclustered index
2. Sample each new column being added: loops through each recommended INCLUDE column and runs AVG(DATALENGTH(column)) on 10,000 rows from the actual table
3. Calculate proportionally:

new_size = current_size × (avg_row + avg_new_bytes) / avg_row

Accuracy Safeguards

• Floor enforcement: The estimate is never less than the current index size + 5%. Adding columns can never shrink an index.
• Real data, not metadata: Previous versions used max_length / 4 to guess average column width, which wildly overestimated for nullable NVARCHAR columns. The v2.0 sampling approach eliminated 10x-100x overestimates.
• Display in MB: All sizes shown in MB for consistency with existing index size display.

Enterprise Features

ONLINE = ON

On Enterprise and Developer editions, all generated CREATE INDEX statements include WITH (ONLINE = ON). This allows index creation without blocking concurrent queries — critical for production systems.

@RunnableUnder: Edition-Aware Thresholds

The @RunnableUnder parameter supports split thresholds for Standard vs Enterprise:

Indexes estimated above the active threshold are wrapped in /* */ comments for manual DBA review. The UPDATE ResolvedDate statement is also commented out so it doesn't mark the finding as resolved until the DBA actually runs it.

Parameters

sp_KeyLookupAnalysis (analysis engine)

sp_KeyLookupRecommendations (reporting)

Results Table: indexing.KeyLookupResults

One row per nonclustered index that has key lookups. ResolvedDate is set when the recommendation is applied.

Output Script Features

Each recommendation in the generated script includes:

• Current index definition (keys + includes + size in MB)
• Columns to add and estimated new size
• Impact metrics: total reads, executions, avg reads/exec, plan count
• EXEC sp_helpIndexUsage for manual review
• ONLINE = ON on Enterprise/Developer editions
• /* */ commenting for indexes exceeding @RunnableUnder threshold
• UPDATE ResolvedDate after each fix for history tracking

Usage Examples

-- Analyze all databases, then view recommendations
EXEC indexing.sp_KeyLookupRecommendations @RunAnalysis = 1;

-- Analyze a specific database group
EXEC indexing.sp_KeyLookupRecommendations
    @RunAnalysis = 1,
    @DatabaseName = 'MyApp_%',
    @RunnableUnder = '1,5e',
    @MinExecutions = 1000;

-- View existing results without re-analyzing
EXEC indexing.sp_KeyLookupRecommendations;

-- Filter to a specific table
EXEC indexing.sp_KeyLookupRecommendations
    @DatabaseName = 'MyDatabase',
    @TableName = 'audit_trail';

-- Check the results table directly
SELECT DatabaseName, TableName, NCIndexName,
       RecommendedIncludes, TotalLogicalReads, PlanCount
FROM indexing.KeyLookupResults
WHERE ResolvedDate IS NULL
ORDER BY TotalLogicalReads DESC;

-- History: what's been resolved
SELECT DatabaseName, TableName, NCIndexName, ResolvedDate
FROM indexing.KeyLookupResults
WHERE ResolvedDate IS NOT NULL
ORDER BY ResolvedDate DESC;