Plan Guides and Hints
Overview
A hint is a decision the optimizer can no longer revisit as the data changes. Hints come from three places, and each one hides in its own way:
- A plan guide that stops validating, usually after an index or column it names was dropped or renamed, is ignored without an error.
- A Query Store forced plan or Query Store hint can fail every time it is applied, and the only sign is a failure count in the Query Store catalog views.
- A hint written into a stored procedure, function, view or trigger (NOLOCK, an index hint, FORCESEEK, MAXDOP, RECOMPILE and the rest) is invisible until someone reads the code.
The Plan Guides and Hints report reads all three for one database and shows:
- a row of cards with the counts: plan guides, invalid plan guides, forced plans, Query Store hints, hints in code, and the guided and misguided plan executions on the instance,
- the findings for the database as a whole, in the warning color when they need a look,
- a bar chart, Hints by type across the database, counting each hint type found in module code, plan guides and Query Store hints,
- one grid that switches between Plan Guides, Query Store and Hints in Code.
Where to find it
| Route | How |
|---|---|
| Database tree | Expand a database → Real Time → Plan Guides and Hints |
The page title reads Plan Guides and Hints for <database name>. The item is shown on SQL Server 2008 and later.
The toolbar
| Control | What it does |
|---|---|
| Refresh | Reads the database again |
| Plan Guides / Query Store / Hints in Code | Which grid to show. Query Store is left out on versions without Query Store (before SQL Server 2016). Switching does not read the database again |
| Show Script | Shows the script for the selected row (enabled when a row is selected). Double-clicking a row does the same |
| Cancel | Stops a read in progress (shown only while reading) |
On the first read the grid opens on the first view that has rows: Plan Guides, then Query Store, then Hints in Code, and Plan Guides when none have. After you pick a view, a Refresh keeps it.
The read (the module scan and one sys.fn_validate_plan_guide call per plan guide) runs in the background on its own connection with the progress bar, and can take a while on a large database. It times out after 300 seconds.
Reading the cards
| Card | What it shows |
|---|---|
| Plan guides | The number of plan guides, and how many are disabled. “Needs VIEW DEFINITION to see all” when the login lacks VIEW DEFINITION on the database; “Not visible” when sys.plan_guides could not be read |
| Invalid plan guides | Plan guides that sys.fn_validate_plan_guide reports an error for. “Some could not be checked” when validation failed for a guide |
| Forced plans | Query Store forced plans, with the Query Store state. Warning color when a forced plan has failed. “Needs VIEW DATABASE STATE” when Query Store could not be read. SQL Server 2016 and later |
| Query Store hints | Hints set with Query Store hints. Warning color when one has failed. “N/A, Needs SQL Server 2022” before SQL Server 2022 |
| Hints in code | Hints found in module code, and how many of the database’s modules hold at least one. Warning color when any of them is a hint type that deserves a look (see below) |
| Guided / misguided runs | The Guided plan executions/sec and Misguided plan executions/sec counters, instance wide since the instance started. Warning color when misguided is above zero. “Needs VIEW SERVER STATE” when the counters cannot be read |
The heading sums it up, for example “Plan guides and hints in Sales: 1 invalid plan guide, 12 hints in code”, or “nothing overrides the optimizer” when nothing was found. The note under it gives the time of the read and the SQL Server version, and says when there is no Query Store before SQL Server 2016, when Query Store is OFF, or when the guided plan counters need VIEW SERVER STATE.
Database findings
| Finding | When |
|---|---|
| N plan guides do not validate (warning) | The optimizer ignores them without an error, usually after an index or column they name was dropped or renamed |
| Misguided plan executions: N (warning) | Plan guides matched a query but their hints could not be applied, instance wide since the instance started |
| N forced plans or Query Store hints have failed (warning) | See the Failures and Last Failure columns on the Query Store view |
| Plan guides could not be read | sys.plan_guides was refused; the error is shown |
| Plan guides and module code may be hidden | The login lacks VIEW DEFINITION on the database, so only what it owns or was granted is listed |
| Query Store could not be read / Query Store hints could not be read | The error is shown |
| Module code could not be read | The error is shown |
| N modules were not scanned | Their definition is not readable: encrypted, or the login lacks VIEW DEFINITION |
The hints by type chart
Each bar is one hint type, most used first, with the total count. When a type was found somewhere other than module code, or in more than one place, the count says where, for example “3 (2 in code, 1 in plan guides)”. Hover a bar for where it was found and what the hint does. Bars for the hint types that deserve a look are drawn in the warning color.
The types the report recognizes:
| Hint type | Deserves a look | What it means |
|---|---|---|
| NOLOCK, READUNCOMMITTED, READ UNCOMMITTED isolation | Yes | Dirty reads: rows can be missed, read twice or read before they are rolled back. Consider READ COMMITTED SNAPSHOT instead |
| Index hint | Yes | The query fails with an error if the index is renamed or dropped, and the optimizer cannot pick a better index as the data changes |
| FORCESEEK, FORCESCAN | Yes | Takes the access method away from the optimizer; the query fails if no plan with that access method can be built |
| USE PLAN | Yes | Pins a whole plan as XML; it stops working after a schema change the plan depends on |
| Join hint | No | Fixes the join algorithm (LOOP, HASH, MERGE or REMOTE), and a join hint in the FROM clause also forces the join order |
| FORCE ORDER | No | Fixes the join order as written, whatever the data distribution becomes |
| MAXDOP | No | Overrides the database and instance MAXDOP for this statement |
| RECOMPILE | No | Compiles on every execution: good plans for skewed parameters at a CPU cost on busy code |
| OPTIMIZE FOR | No | Builds the plan for a fixed or average value; review when the data distribution changes |
| USE HINT | No | Turns on an optimizer behavior for this statement; check it is still needed after an upgrade or compatibility level change |
| QUERYTRACEON | No | Sets a trace flag for this statement; it needs sysadmin unless it runs inside a module or plan guide |
Comments, string literals and quoted identifiers are left out of the code scan, so NOLOCK in a comment is not counted. MAXDOP, OPTIMIZE FOR, USE HINT, QUERYTRACEON, FORCE ORDER and USE PLAN are only counted inside an OPTION clause. A hint type is counted once per line of code.
The Plan Guides view
| Column | What it is |
|---|---|
| Plan Guide | The plan guide name |
| Scope | OBJECT, SQL or TEMPLATE |
| Object | The module an OBJECT plan guide is on |
| Status | Invalid, Disabled, Valid or Not checked |
| Hints | The hints the guide applies |
| Statement | The statement it matches |
| Validation | For an invalid guide, the error from sys.fn_validate_plan_guide (for example “Msg 8712, severity 16: …”); otherwise why it could not be validated, or “Validates, but disabled, so it is not used.” |
Status and Validation are dark orange for an invalid guide and gray for a disabled or unchecked one. Hover a row for the full details and the date it was last modified.
Show Script shows sys.sp_control_plan_guide for the guide, with its statement, parameters and hints as comments so it can be re-created by hand. For an enabled guide the DISABLE statement is live and DROP is commented out; for a disabled guide ENABLE is commented out and DROP is live. Nothing is run by the report.
The Query Store view
| Column | What it is |
|---|---|
| Kind | Forced plan or Query hint |
| Query ID | The Query Store query id |
| Object | The module the query belongs to, if any |
| Hint | “Forced plan” and the plan id, or the Query Store hint text |
| Source | MANUAL or AUTO for a forced plan (automatic plan correction), the source of a Query Store hint |
| Failures | How many times forcing or the hint failed |
| Last Failure | The last failure reason |
| Last Execution | When the query last ran |
| Query Text | The query |
Failures and Last Failure are dark orange for a row that has failed.
Right-click a row for:
- Open the Forced Plan in Plan Viewer (or Open the Latest Plan in Plan Viewer for a hint), which reads the plan from Query Store,
- Script sp_query_store_unforce_plan or Script sp_query_store_clear_hints, which shows the statement to undo it,
- Go to Automatic Tuning on a forced plan, on SQL Server 2017 and later.
The Hints in Code view
| Column | What it is |
|---|---|
| Module | The procedure, function, view or trigger |
| Type | Stored procedure, Scalar function, Table-valued function, Inline function, Trigger, Database trigger or View |
| Hint | The hint type, dark orange for a type that deserves a look |
| Line | The line in the module definition |
| Code | The line itself (up to 200 characters) |
Show Script, a double-click, or right-click Open <module> at Line N shows the module definition with the hint line selected and the advice for that hint type at the top.
Every grid has Copy the findings as text, which copies the heading, the findings and every row, and exports to CSV and Excel like every other grid.
Permissions and versions
- The plan guides and module code need VIEW DEFINITION on the database to see everything. Without it, SQL Server lists only what the login owns or was granted, with no error; the page says so.
- Validation uses
sys.fn_validate_plan_guide, SQL Server 2008 and later. - The Query Store view needs SQL Server 2016 or later, and VIEW DATABASE STATE to read it. Query Store hints need SQL Server 2022 or later.
- The guided and misguided plan counters come from
sys.dm_os_performance_countersand need VIEW SERVER STATE. They cover the whole instance, not only this database. - Every read that is refused is named on the page; the rest still shows.