Plan Warnings
Overview
One nvarchar parameter against a varchar column turns every seek into a scan across the whole table, forever, silently. It is the most common single fix in SQL Server tuning and nothing in this application could find it.
Nor could it find an accidental cross join, a plan that spills to tempdb, a column the optimizer had no statistics for, or the index requests the optimizer writes into the plan itself – which are richer than sys.dm_db_missing_index_details because they arrive attached to the statement that wanted them.
Where to find it
In the tree, under a database, Real Time → Plan Warnings. Hidden below SQL Server 2012.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2012 or newer | The plan warnings this report reads have been in showplan since then. |
VIEW SERVER STATE |
sys.dm_exec_query_stats and the plan lookups are server scoped. |
| Something in the plan cache | A recent restart or DBCC FREEPROCCACHE leaves nothing to read. |
How the sweep works, and why it is safe to run
The obvious implementation of this report is server side XQuery across the plan cache. On an instance with forty thousand cached plans that is a long, CPU heavy query run against a production server, and it is the reason plan scanning scripts have a bad reputation.
This is two passes:
- A cheap ranking query picks the top plans by processor time and by logical reads. Not one XQuery expression appears in it.
- Only those documents are fetched and shredded, on the workstation, where the work costs the machine running the report rather than the instance being reported on.
Plans are ranked twice, by CPU and by reads, and the union taken. A query dragged into a scan by an implicit conversion frequently has modest CPU and enormous reads, so a ranking on CPU alone misses exactly the plans this report exists to find.
The scan budget is on the page
Examined 200 of 12,481 cached plans for this database, chosen by processor time and by logical reads. That is 84% of cached CPU and 61% of cached reads.
That sentence is in the subtitle on every visit. Silently truncating at 200 plans and presenting the result as a clean bill of health is worse than no report, and it is what every version of this script does.
The budget is 100, 200 or 500 plans from the toolbar, and is remembered between sessions.
The warnings it finds
| Warning | Severity | What it means |
|---|---|---|
| Conversion, seek plan | Critical | The index on this column cannot seek. Every row is converted and read. |
| No join predicate | Critical | Two inputs joined with no condition between them. A cross join. |
| Conversion, estimate | Serious | The index still seeks; the estimate does not, which sizes the memory grant wrongly. |
| Missing index | Serious | The optimizer asked for an index that does not exist. |
| Spill to tempdb | Serious | A sort or hash ran out of granted memory and went to tempdb. |
| Columns with no statistics | Watch | The optimizer had no distribution for this column, so it guessed. |
| Unmatched index | Watch | A filtered index could not be matched because the query was parameterised. |
Why the two conversions are separate
ConvertIssue="Seek Plan" is the seek killer and gets its own severity. ConvertIssue="Cardinality Estimate" still seeks and still hurts, quietly, by feeding a wrong estimate into the memory grant.
Reporting them as one warning is why people conclude that implicit conversions do not matter much: half the rows they looked at genuinely did not.
Reading the chart
One row per warning type. The bar length is the processor time behind the plans that carry it, not the count of them.
That is the difference between this and every free script that does the same sweep. Forty plans carrying a warning on a lookup table nobody queries is a smaller problem than one plan carrying it on the statement that runs four million times a day, and a count puts them the wrong way round.
The segments are the matrix folded into a bar. One segment per object inside the type’s bar keeps both facts: which type costs the most, and whether that cost is one object or spread across twenty. A single wide segment and a row of slivers mean very different things and need very different fixes.
Segment shades come off the row’s severity colour rather than from a categorical palette. The objects inside a warning type are instances of it rather than categories, and unrelated hues would suggest a taxonomy that does not exist.
Reading the grid
| Column | What it is |
|---|---|
| Severity | The band, in the chart’s colour. |
| Warning | Which of the seven. |
| Object and column | Resolved back to real schema, with the column’s actual type where it resolved. |
| Why it hurts | The sentence that makes the row a fix rather than a diagnosis. |
| CPU / day | Processor time per day inside the plans carrying this warning. |
| Plans | How many cached plans carry it. |
| Fix | What the right-click script will generate. |
A conversion row says the thing out loud:
CustomerNumber is varchar(20) and the predicate compares it to nvarchar(4000), so every row is converted and no index on CustomerNumber can seek.
A row that only said “plan affecting convert” would leave the reader to work out which side of the comparison is wrong, and that is the whole difficulty of the problem.
Two things about the CPU column
A plan carrying three warnings contributes its processor time to all three, so the column deliberately sums to more than the total. The alternative is dividing one query’s cost between its problems, which understates every one of them and would rank a plan with several warnings below a plan with one.
The plan cache is volatile. A plan evicted an hour ago is exactly the plan that caused this morning’s incident, and it is not on this page. Processor time per day is extrapolated from the age of the cached plan.
Right-click actions in the grid
| Action | What it does |
|---|---|
| Analyze execution plan | Opens the statement with its plan document attached, which is what makes the Plan Analysis button on that dialog work. |
| Copy fix script | Both fixes for a conversion; a CREATE INDEX for a missing index; a CREATE STATISTICS for a missing statistic. |
| Go to Memory Grants | On a spill row. |
| Go to Index Statistics | On a no-statistics row. |
| Go to Missing Indexes | On a missing index row. |
| Copy query text | The statement, cut out of its batch. |
The plan document is re-read from its handle when a row is opened rather than held from the sweep. Keeping five hundred plan documents alive for the one that gets clicked is tens of megabytes for nothing.
Both fixes, because only the application knows
For a conversion the script offers two, with the consequences above each rather than underneath:
- Change the parameter in the caller to match the column. Free on the database, and an application deployment. This is usually the right one: an ORM defaulting every string parameter to
nvarcharis the single most common cause of this warning. - Change the column to match the parameter. This rewrites the table, takes a schema modification lock for the duration, rebuilds every index the column is in, and doubles the storage if the move is to
nvarchar.
Which is right depends on the application rather than on the database, and a report that offered only one of them would be guessing.
Missing index requests are folded through the same consolidator the Missing Indexes reports use, so the two pages cannot disagree about what a duplicate is. The optimizer habitually asks for eight to thirty near identical indexes on one table – same keys, different include lists – and a page that listed each as its own row is how a server ends up with nine overlapping indexes on one table.
Where the data comes from
| Source | What it gives |
|---|---|
sys.dm_exec_query_stats |
The ranking: worker time, logical reads, execution count, plan age. |
sys.dm_exec_sql_text |
The statement, and the database filter. |
sys.dm_exec_text_query_plan |
The plan document for that statement. |
sys.columns, sys.types, sys.tables, sys.schemas |
Resolving a warning back to a real column type. |
dm_exec_text_query_plan rather than dm_exec_query_plan for two reasons: it takes the statement offsets, so what comes back is the plan for the statement rather than for the whole batch it is one statement of, and it returns nvarchar rather than xml, so a plan nested deeper than the 128 levels the xml type allows still comes back instead of erroring – which is exactly the sort of plan somebody opens this report to find.
The shredding is namespace agnostic, matching on local name rather than on a registered namespace. A plan fragment that arrives without its namespace declaration matches nothing at all under a namespace bound parser, and a report that finds no warnings looks exactly like a clean bill of health.
Messages you may see
N plans were chosen and could not be read back. A module created
WITH ENCRYPTION, which hides its plan as well as its text; a plan that left the cache between the ranking and the fetch; or a document too large to return. Whatever they carried is not on this page, which is a hole in the coverage the page is claiming.
No optimizer warning in the top 200 plans. Raising the scan budget looks further down the cache.
The plan cache holds nothing for this database. A recent restart, a recent
DBCC FREEPROCCACHE, or a database nothing has run against.
The list is cut at 300 findings by processor time. There are more below the cut.
Related reports
| Report | Why you would go there |
|---|---|
| Index Statistics | A bad estimate and a missing statistic are the shared cause behind half of this page. |
| Missing Indexes | The same requests from the DMV rather than from the plans. |
| Memory Grants and Spills | Where a spill row leads, and where a bad estimate ends up. |
| Plan Cache | What else is in the cache this sweep read. |
| CPU by Query | The queries whose processor time these warnings sit inside. |
Frequently asked questions
Why does the CPU column add up to more than the database’s CPU? Because a plan with three warnings contributes its time to all three rows. Dividing it between them would understate every one.
Why is a table I know has a conversion not listed? Its plan was outside the scan budget, or it has left the cache. Raise the budget to 500 and refresh.
Why does an object show without a resolved type? The plan named a column this report could not find in sys.columns – another database, a table valued function, or a temporary object. It is left unresolved rather than guessed at, because inventing a type produces a fix script that alters the wrong thing.
Is running this safe on a busy production instance? It reads sys.dm_exec_query_stats once and fetches at most 500 plan documents. There is no XQuery in the batch at all. The heaviest part is the CROSS APPLY of sys.dm_exec_sql_text needed to filter to one database, which is a memory read per cached plan.
Why are there fewer findings than raw warnings? Showplan writes the same predicate once per operator that carries it. The footer says how many raw warnings were read and how many distinct ones they folded into.
Why does a database of encrypted procedures find nothing? WITH ENCRYPTION hides the execution plan as well as the module text, so there is no document to shred. Those plans are counted in the banner rather than silently dropped.
Should I create every missing index it lists? No. Every index costs writes, and an estimate of impact is not a promise. The requests are folded so that what is listed is closer to what a person would actually create.