Key Lookups
Overview
A key lookup is an index that nearly covered a query. SQL Server found the rows through the index, then went back to the clustered index once for every one of them to fetch a column or two the index did not carry. On a query returning three rows that costs nothing. On a query returning three thousand rows, ten thousand times an hour, it is most of what the query costs.
The fix is usually to add the missing columns to the index as included columns, and the columns are written in the plan and nowhere else. This report reads the busiest plans Query Store holds, finds every lookup, and says:
- which index the seek used before it went back to the table,
- which columns the lookups fetched or tested,
- how much those lookups cost, as a share of the whole window,
- how far to widen the index before it stops paying for itself.
Plan Warnings does not list lookups, because a lookup is not a warning. Missing reads what the optimizer wished for when it costed an index it did not have, which is not what it fetched through the index it chose. Inefficient grades the indexes that exist and never widens one.
Widening is not free
Every column added to an index is stored a second time and written again on every insert, and on every update to that column. A lookup fetching a one byte status column is worth an INCLUDE on almost any table. A lookup fetching a four thousand character description is a second copy of the table, and the lookup was the cheaper of the two.
So for every index the page builds a chain of options. Each step adds the set of columns that removes the most cost per byte still to be added, and counts every other lookup those columns now cover. The page then picks where to stop:
| Rule | Why |
|---|---|
| The index row stays under 80% of the table row’s declared size | Past that the index is a second copy of the table. |
| No step returns less than a tenth of what the first step returned per byte | Past that the index is paying for the column rather than being paid by it. |
Sizes are declared widths: an nvarchar(60) column is counted as 120 bytes a row even when the values are short, and a MAX column is counted as 8,000 bytes. That overstates variable length columns on purpose. An estimate that understates how large an index will grow is found out after the rebuild.
Where to find it
In the tree, under a database, Real Time → Query Store → Key Lookups.
The report needs SQL Server 2016 or newer. The Query Store folder is hidden below SQL Server 2016, and on master and tempdb, where Query Store cannot be turned on.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2016 or newer | Query Store arrived in SQL Server 2016. |
| Query Store on for the database | The plans and their runtime statistics live in Query Store. |
VIEW DATABASE STATE |
To read the Query Store catalog views and sys.dm_db_partition_stats. |
VIEW SERVER STATE (optional) |
For the index usage counters in the Writes column. Without it the page still loads and the counters read zero. |
The toolbar
| Control | What it does |
|---|---|
| Logical reads / CPU / Duration / Executions | What “busiest” means, and the currency every cost on the page is in. Logical reads is the default, because a lookup is a read. |
| 1 h / 6 h / 24 h / 7 d | The window of Query Store history. It ends at the last completed interval. |
| 25 / 50 / 100 / 200 plans | How many of the busiest plans are read. Plan documents are large, so more plans take longer. |
| Turn Query Store on | Only when Query Store is off and a setting would fix it. |
| Refresh | Re-reads Query Store. |
The choices are remembered for the next visit.
Reading the chart
At the top, a verdict names the index worth widening first, what the lookups through it cost, how large the change would be, and what it leaves out. When no index is worth widening it says so, and counts any index that already carries the columns its old plans go back for.
Five tiles follow:
| Tile | What it is |
|---|---|
| Spent on lookups | What every lookup in the plans read came to, and its share of the window. |
| Rows looked up | The optimizer’s estimate of rows fetched, and how many of the plans do any lookups. |
| Indexes to widen | Indexes with a change worth making. Click to show only those in the grid. |
| Would copy the table | Indexes whose lookups are real but covering them would make the index most of the row. Click to filter. |
| Lookups into heaps | Indexes on tables with no clustered index, where every lookup is by row id. |
Click a filtering tile again to clear the filter.
The tradeoff chart
Each index is a chain, one mark per option, from the cheapest return to the widest:
- The horizontal axis is the declared size the option adds to the index, in MB.
- The vertical axis is how much of the chosen measure the lookups it removes were spending.
- Both axes are logarithmic, and a factor of ten is the same distance on both.
- The dashed diagonals are lines of equal return per MB. A step steeper than the diagonals returns more per MB than the options before it; a flatter step returns less.
The marks say where to stop by shape, not color:
| Mark | Meaning |
|---|---|
| Solid | Worth adding. |
| Solid with a ring | The last option worth adding: where to stop. |
| Hollow, joined by a broken line | Wider than it is worth. |
| A short bar beside a mark | The option was drawn at the previous one’s position, because a wider option cannot cost less. |
Color grades the index: orange is worth widening, red is urgent (over 5% of the window), blue would copy the row, and gray costs too little to matter. Up and to the left is better. Up to ten indexes are drawn; the rest are in the grid. Hover over a mark for every option in its chain.
Reading the grid
One row per nonclustered index the lookups came through.
| Column | What it is |
|---|---|
| # | Rank by what the lookups cost. |
| Finding | Cover it, cover part of it, covering it would copy the row, covered since the plans compiled, or lookups that cost little. |
| Add to INCLUDE | The columns up to where to stop. |
| Notes | What was left out and why, heaps, constraints, filters, and when the plans last ran. It takes the width the other columns leave, at least about 200 px. |
| Rows looked up | Estimated rows fetched over the window. |
| Logical reads (or the chosen measure) | What the lookups cost, charged by the lookup’s share of each plan’s estimated cost. |
| Share | That cost as a share of everything every plan in the window spent. |
| Adds | Declared size of those columns over every row of the index. |
| Index size | Space the index uses now. |
| Writes | Updates to the index since SQL Server last started. |
| Index | The index the seek used before the lookup. |
| Table | The table the lookup went back to. |
| Queries | How many distinct queries look up through this index. |
| Columns fetched | Every column the lookups fetched or tested that the index does not carry. |
The recommendation and its explanation come first so they are on screen without scrolling. Text that is wider than its column is cut off, and hovering over a row shows every column in full.
Double-click a row for an explanation: the keys and included columns, every option in the chain, and the plans behind it. Right-click for:
| Action | What it does |
|---|---|
| Explain this index | The same explanation. |
| Show the busiest statement that looks up through it | The statement in the query window. |
| Copy the INCLUDE script | The CREATE INDEX ... WITH (DROP_EXISTING = ON) statement, copied to the clipboard. Nothing is run. |
| Copy the columns fetched | The column list. |
| Go to Residual Predicates | The companion report for seeks that read too much. |
| Copy the query behind this report | The batches the page runs, ready for SSMS. |
About the script
- An ordinary index is rebuilt in place with
DROP_EXISTING = ON, which keeps its name, keys, filter and every hint that names it. Existing included columns are kept, and data compression is written back out so the rebuild does not drop it. - An index behind a
PRIMARY KEYorUNIQUEconstraint cannot carry included columns, so the script creates a second index on the same keys instead, and drops nothing. - A filtered index gets
SET QUOTED_IDENTIFIER ONfirst, whichsqlcmdneeds. ONLINE = ONis written only on Enterprise and Developer Edition. On other editions the script says the rebuild blocks writes while it runs.- An index whose finding is “would copy the row” still gets a script, marked NOT RECOMMENDED, so the size of the change can be judged.
Where the data comes from
| Source | What it gives |
|---|---|
sys.query_store_runtime_stats and ..._interval |
Executions and totals per plan in the window. |
sys.query_store_plan |
The plan document, query_plan, for the busiest plans only. |
sys.query_store_query, ..._query_text |
The statement and its object. |
sys.indexes, sys.index_columns, sys.columns |
What each index carries and how wide each column is declared. |
sys.dm_db_partition_stats, sys.partitions |
Rows, size and compression. |
sys.dm_db_index_usage_stats |
Updates to each index. |
How the plans are read
The busiest plans are ranked from the runtime statistics first, and sys.query_store_plan is joined only to the plans that made the list. The documents are returned as text and read by Database Health Monitor, not with XQuery on the server: a plan nested deeper than 128 levels cannot be shredded with the XML methods, and shredding hundreds of plans is CPU on the server being diagnosed. A document over 16 MB is counted and not fetched.
A key lookup is a Clustered Index Seek marked as a lookup; a RID lookup names the table and no index. The columns a lookup returns are in its output list. The columns it only tests are in its predicate and are not repeated in the output list, so an INCLUDE built from the output list alone would keep every lookup. The page adds both.
What the numbers are
- Totals count regular executions only, weighted by executions. Aborted and failed executions are counted in the footer.
- The window ends at the last completed Query Store interval.
- The cost of a lookup is the plan’s measured cost multiplied by the lookup’s share of the plan’s estimated cost, the only per operator figure a plan carries.
- Rows looked up and the plan shape come from the plan Query Store captured first. A plan that has recompiled to the same shape keeps its first document.
- Lookups on temporary tables, table variables, system tables and tables in other databases are counted in the footer and left out of the grid.
Messages you may see
Nothing ran in the last 24 hours. Query Store has no runtime statistics in the window. Widen the window, or check Query Store Health.
None of the busiest plans looks rows up. The healthy case. A lookup only cheap queries do is not on this page by construction; read more plans or rank by executions to look further down.
The busiest plans do look rows up, and every one of them is on a temporary table… Every lookup found is on something the catalog here cannot describe.
N plans were too large or malformed to read to the end. The lookups read before the break are real, and nothing is claimed about the rest of those plans.
Covered since these plans compiled. The index already carries every column the plans go back for. The plans were compiled before it was widened and lose the lookup when they recompile.
Related reports
| Report | Why you would go there |
|---|---|
| Residual Predicates | Seeks that read many rows and keep few, which an INCLUDE cannot fix. |
| Missing | The indexes the optimizer asked for while planning. |
| Inefficient | Indexes that cost more writes than they save reads. |
| Page Reads by Query | The queries doing the reading, whatever the operator. |
| Plan Warnings | Warnings the plans raise, such as implicit conversions. |
Frequently asked questions
Why is the index size added so large? Sizes are declared widths over every row of the index. A variable length column that usually holds short values costs much less than this, and the page errs on the side that is not found out after the rebuild.
Why does the page leave out a column the queries need? Adding it would widen the index by more than its lookups cost, or would make the index most of the row. The notes and the script both name what was left out.
The lookup is gone, but the page still shows it. Query Store keeps the plan document from first capture. The finding changes to “Covered since these plans compiled” once the index carries the columns, and the rows go away when the plans age out of the window.
Does the report change anything? No. Scripts are copied to the clipboard for you to read and run yourself.