Residual Predicates
Overview
An index seek is not always good news. It is a range. An index on CustomerID seeking for customer 42 and then testing Status = 'Open' reads every order customer 42 ever placed to hand back the three that are open. The plan still says Index Seek, and nothing warns about it.
The test the seek could not use is a residual predicate: a column the index carries, but not in a place the seek can use. The waste shows in two numbers on the operator, the rows it read (EstimatedRowsRead) beside the rows it kept (EstimateRows), and the reason is in its predicate.
This report reads the busiest plans Query Store holds and, for every seek and scan that tested rows after reading them, says:
- which index was read, and which columns the seek used,
- which column was tested only after reading,
- how many rows were read for every row kept,
- what it cost to read the ones thrown away,
- what would fix it: a longer key, a second index, or a rewritten predicate.
Plan Warnings lists the warnings a plan raises, and a residual predicate raises none. Key Lookups names the columns a lookup went back for, a different operator with a different fix: an included column cannot be sought on.
Why key order matters
A seek can only use an index key from the front. An index on (CustomerID, OrderDate, Status) can seek on CustomerID, or on CustomerID and OrderDate, but not on Status alone, and a seek stops at the first key compared with a range. So the same column can be tested after reading for four different reasons, each with a different fix:
| Where the column is | Fix |
|---|---|
| After a key the predicate does not mention | A second index with the keys in the order the predicate uses them. |
| After a key sought with a range | A second index with the equality column in front of the range. |
| In the INCLUDE list, on an index whose seek used every key for equality | Move it to the end of the key, in place. Every other seek on the index keeps working. |
| Inside a function or a conversion | No key order helps. Rewrite the predicate. |
The page extends a key in place only when that is safe for everything else that uses the index: a plain nonclustered index, not unique, not behind a constraint, whose seek used every key for equality. A longer unique key would start accepting duplicates, and a clustered key is carried in every other index, so those always get a second index instead.
Where to find it
In the tree, under a database, Real Time → Query Store → Residual Predicates.
The report needs SQL Server 2016 or newer. EstimatedRowsRead is written into plans from SQL Server 2016 SP1, so on SQL Server 2016 RTM seeks are counted as unmeasured.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2016 SP1 or newer | Query Store arrived in SQL Server 2016, and the rows read attribute in 2016 SP1. |
| 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 update counts quoted in the verdict. Without it the page still loads. |
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 row read and thrown away 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. |
| 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 read most wastefully, how many rows it read for each row kept, why the seek could not skip them, and what the fix is. When nothing needs a key change it names a predicate to rewrite, or says the busiest plans read little they throw away.
Four tiles follow:
| Tile | What it is |
|---|---|
| Spent on rows thrown away | What reading the discarded rows cost, and its share of the window. |
| Rows read, then discarded | Estimated rows thrown away, out of the rows read. |
| Keys to change | Indexes that need a longer key or a second index. Click to show only those. |
| Predicates to rewrite | Tests hidden inside a function or a data type conversion. Click to filter. |
The key reach diagram
Each row of the chart is one index drawn as its definition:
- The key columns in key order, a divider, then the included columns. On a clustered index or a heap, the tested columns are shown with a “rest of the row” box.
- A bar under the keys shows how far the seek reached. It always starts at the first key and stops at the first key the seek did not use.
- A dotted line runs from where the seek stopped to the furthest column tested after reading: the rows read and thrown away.
- An arc runs from each tested column to the place in the key where it would let the seek skip those rows, with a tick at that place.
- On the right, the rows read and kept per run, and a bar for the cost, on one scale for every row.
The boxes differ by shape, not color:
| Box | Meaning |
|---|---|
| Filled | Sought for equality. |
| Lighter, with a chevron | Sought as a range. The seek goes no further. |
| Dashed outline with a filter mark | Tested after reading: solid mark for equality, hollow for a range. |
| Hatched, dashed outline | Tested inside a function or a conversion. It never gets an arc. |
| Plain outline | Nothing happened to this column. |
Color grades the row: orange is a key to change, red is urgent (over 5% of the window), blue is a predicate to rewrite, green is already keyed, and gray costs too little to matter. Rows whose filter keeps most of what it reads are left off the chart. Up to twelve rows are drawn; the rest are in the grid. Hover over a row for its columns, the proposed change and the reason.
Reading the grid
One row per index read one way: the same columns sought and the same columns tested. Two plans reading an index the same way are one row, because one change serves both.
| Column | What it is |
|---|---|
| # | Rank by what reading the discarded rows cost. |
| Index | The index read, or (heap). |
| Table | The table. |
| Finding | Add a column to the key, needs an index keyed a certain way, a function or data type mismatch hides the column, keyed since the plans compiled, the filter keeps most of what it reads, or rows thrown away that cost little. |
| Proposed key | The key a seek could use. |
| Notes | The reason, what cannot be fixed by a key, wide keys, filters, and when the plans last ran. |
| Read per kept | Rows read for every row kept. Under 4 is a filter doing its job. |
| Logical reads (or the chosen measure) | The cost of reading the rows thrown away: the plan’s cost, times the operator’s share of the plan, times the share of its rows discarded. |
| Share | That cost as a share of everything every plan in the window spent. |
| Rows read / Rows kept | Estimated over the window. |
| Queries | How many distinct queries read the index this way. |
| Sought on | The columns the seek used, with (range) on a range, or “nothing, a scan”. |
| Tested after reading | The residual columns, with (range), (in a function) or (converted). |
The Notes column takes the width the others leave, so it grows with the window. The last columns (Rows read, Rows kept, Queries, Sought on and Tested after reading) sit past the edge of a 1280 pixel screen; scroll right for them, or hover over a row, whose tooltip lists every column in full.
Double-click a row for an explanation. Right-click for:
| Action | What it does |
|---|---|
| Explain this index | The columns, the proposed key, the rows and the reason, in words. |
| Show the busiest statement that reads it this way | The statement in the query window. |
| Copy the key extension script / Copy the new index script | The statement, copied to the clipboard. Nothing is run. |
| Copy the rewrite advice | For a predicate no index can help: how to rewrite it. |
| Copy the recompile statement | For an index already keyed correctly: sp_recompile, commented out. |
| Go to Key Lookups | The companion report. |
| Copy the query behind this report | The batches the page runs, ready for SSMS. |
About the script
- A key extension rebuilds the index with
DROP_EXISTING = ONand the new columns on the end of the key. A column moving out of the INCLUDE list is listed once, in the key. - A second index is keyed equality columns first and at most one range column last. Beside a nonclustered index it includes that index’s other columns, so the plans reading the old index could read the new one. Nothing is dropped.
- A filter is copied, with
SET QUOTED_IDENTIFIER ONfirst. Data compression is written out. ONLINE = ONis written only on Enterprise and Developer Edition.- A proposed key declared wider than 1,700 bytes carries a warning.
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 |
Each index’s keys in order, included columns, and column types. |
sys.dm_db_partition_stats, sys.partitions |
Rows, size and compression. |
sys.dm_db_index_usage_stats |
Updates since SQL Server last started. |
How the plans are read
The documents are returned as text and read by Database Health Monitor, not with XQuery on the server, because a plan nested deeper than 128 levels cannot be shredded with the XML methods. For every seek and scan the reader takes:
- the seek columns from the seek keys, where a range names its column twice (start and end) and counts once, and where the values sought, which can be another table’s columns in a join, are ignored,
- the residual columns from the operator’s predicate, marked as a function when a function, arithmetic or conversion sits between the column and the comparison, and as a data type mismatch when that conversion is implicit,
EstimatedRowsReadandEstimateRows, over every run of the operator.
Lookups are left to Key Lookups. Columnstore scans are skipped, because a predicate pushed into a columnstore scan is how that storage is meant to be read. A scan without EstimatedRowsRead is taken as reading the whole table; a seek without it is counted as unmeasured.
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.
- Rows read and kept are the optimizer’s estimates from the plan Query Store captured first.
- Predicates on temporary tables, system tables and tables in other databases are counted in the footer and left out of the grid.
Messages you may see
None of the busiest plans tests rows after reading them. The healthy case. Read more plans or rank by executions to look further down.
Every index read this way keeps most of what it reads. Seeks with residual predicates exist, but none reads four rows or more for each row it keeps.
N seeks with a residual predicate did not record EstimatedRowsRead. Plans compiled before SQL Server 2016 SP1 leave it out, and those seeks cannot be measured.
Keyed since these plans compiled. The index already has the tested columns where a seek can use them. The plans were compiled before it changed and will seek properly on their next recompile.
Related reports
| Report | Why you would go there |
|---|---|
| Key Lookups | Seeks that go back to the table for more columns. |
| Missing | The indexes the optimizer asked for while planning. |
| Page Reads by Query | The queries doing the reading, whatever the operator. |
| Plan Warnings | Implicit conversions the plans warn about. |
| Statistics | When the row estimates themselves look wrong. |
Frequently asked questions
The plan says Index Seek. Why is it on this page? A seek reads a range. If the range holds a thousand rows and the predicate keeps ten, the seek read nine hundred and ninety rows for nothing, and only the rows read and rows kept numbers show it.
Why a second index rather than changing the one that is there? Changing the key order of an index changes it for every other query that uses it. The page only extends a key in place when every existing seek keeps working, and otherwise proposes a new index beside it.
Why does a column inside YEAR() get no arc? No index on the column can seek on an expression over it. Written as a range on the bare column, the same test can be sought.
Does the report change anything? No. Scripts are copied to the clipboard for you to read and run yourself.