The incident started at 06:40 on a Tuesday, forty minutes after the nightly statistics update job finished. The vendor application that ran our warehouse's picking logic went from its usual 300-millisecond response to forty-five seconds per call, and the morning shift's pick queue backed up to eleven hundred orders before anyone paged me. The execution plan told the story in one glance: the stored procedure had flipped from an index seek with a key lookup to a full scan with a hash join, on a parameter value the new statistics made look far more selective than it was. Classic parameter-sensitive plan flip — except the procedure lived inside a compiled vendor module, the support contract explicitly forbade modifying it, and the vendor's fix cycle was measured in quarters. The fix that morning was not an index and not a code change: it was a plan guide, created from the cached copy of the good plan, pinning the behavior in about twenty minutes.
Plan guides are the least-loved plan stability feature in SQL Server, mostly because Query Store gets all the attention and partly because they have sharp edges nobody warns you about. This piece is when I still reach for them, how to create one from a known-good plan, the silent-failure behavior that makes them dangerous, and the honest comparison with Query Store forcing. Applies to SQL Server 2016 through 2022.
When do I reach for a plan guide instead of everything else?
I reach for a plan guide when I need a specific plan shape and I cannot modify the query text — vendor procedures, compiled application SQL I do not control, or an ORM that regenerates its statements from code I cannot deploy on my own schedule. The decision tree is short. If the query text is mine, I fix the query or the index, because a plan guide is a patch over a problem that will keep growing underneath. If the bad plan is caused by parameter sniffing and the text is mine, the patterns from my parameter sniffing toolkit notes — OPTIMIZE FOR, local variables, splitting the procedure — are cleaner fixes. If Query Store is enabled and capturing the workload, plan forcing from the GUI is faster and far better instrumented. The plan guide is what remains: the tool for SQL you do not own. It also still has one capability Query Store forcing does not replicate cleanly — forcing a plan onto a query whose current plan has never been good, using a template or a hint, without waiting for a good plan to appear in the cache.
How do I create a plan guide from a known-good plan?
The reliable path is to capture the good plan from the plan cache while it still exists and create the guide from its handle, so you are pinning a plan that actually ran rather than writing XML by hand. During the Tuesday incident the good plan was still in cache on a replica that had not yet recompiled, so I pulled its handle and pinned it:
-- find the good plan's handle (run where the good plan still lives)
SELECT qs.plan_handle, qs.execution_count, t.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE t.text LIKE N'%usp_GetPickList%';
-- pin that exact plan onto the statement
EXEC sp_create_plan_guide_from_handle
@name = N'PG_PickList_SeekPlan',
@plan_handle = 0x06000600...; -- the handle from the query above
-- verify what exists
SELECT name, scope_type_desc, is_disabled, query_text
FROM sys.plan_guides;When no good plan is in cache anywhere — the usual case by the time you are called — the fallback is sp_create_plan_guide with a hints argument: OPTION (OPTIMIZE FOR (@Region = N'EU')) or OPTION (RECOMPILE) attached to the statement text exactly as the engine receives it. "Exactly" is doing heavy lifting in that sentence: for an OBJECT or SQL guide the statement text must match the batch character for character, including whitespace, because the match is literal. A guide whose text is off by one space is a guide that matches nothing, and nothing tells you. That is why the from_handle route, where the engine records the text itself, is the one I trust in an incident.
Why do plan guides fail silently?
Because SQL Server treats an invalid plan guide as a hint it cannot honor, not as an error: if the guide references an object that was renamed, an index that was dropped, or a query text that no longer matches, the optimizer simply compiles without the guide and the query runs free — no error, no warning in the error log, no entry anywhere you would casually look. This is the behavior that turns plan guides from a fix into a time bomb. The vendor eventually shipped a new build of the picking module that renamed an internal table, and for six weeks the guide sat in sys.plan_guides looking perfectly healthy while the old scan plan crept back and the morning queue degraded by minutes a week. Nothing paged; the guide was just decoration. The one diagnostic the engine offers is the sys.fn_validate_plan_guide function, which reports whether a guide would apply if invoked right now:
SELECT pg.name, v.msgnum, v.severity, v.message
FROM sys.plan_guides AS pg
CROSS APPLY sys.fn_validate_plan_guide(pg.plan_guide_id) AS v;A row back means the guide is broken. No rows means it would fire today. I run that query in every estate audit and after every vendor upgrade, every schema deployment, and every index change, because those are the three events that orphan guides. The extended events session that catches Plan_guide_unsuccessful exists too, but validating on a schedule is simpler and catches the same class of rot.
How do plan guides compare to Query Store plan forcing?
Query Store forcing is the better instrument in every dimension that matters operationally — visibility, failure signaling, and maintenance — and plan guides win only where Query Store cannot run or the plan you need never existed in cache. The full mechanics of forcing are in my Query Store plan forcing notes; the comparison that matters here is the failure mode. A forced plan that becomes invalid — its index dropped, its objects renamed — shows up in Query Store's force failure reasons and the engine can even fall back to compiling a fresh plan while reporting why forcing failed. A plan guide in the same situation just stops matching, in silence, as covered above. Query Store also gives you the regression story around the pin: what the query did before, during, and after, in query_store_runtime_stats, which is how you justify keeping or removing the pin. On SQL Server 2022 I default to Query Store on every production database, and plan guides have become the exception: legacy instances stuck on older builds, databases where the vendor forbids Query Store overhead, and the never-had-a-good-plan hint cases.
When should a plan guide come back out?
As soon as the underlying problem is fixed — and the guide should carry a retirement date from the day it is created, because an undocumented plan guide outlives the person who created it and confuses everyone who follows. My convention is that every guide gets a name encoding the date and the incident ticket, a row in whatever runbook the team keeps, and a review entry for the next vendor release. When the vendor shipped their corrected procedure, dropping the guide was one line — sp_control_plan_guide with @name and 'DROP' — and the new code planned fine on its own. The estates that scare me are the ones with forty-odd guides in sys.plan_guides, half named things like fix_query_2, none validated in years, some pinned to plans that were only good on a schema that no longer exists. A plan guide is a cast on a broken bone: exactly right for the healing period, actively harmful if you never take it off.
Watching pinned plans with MonPG when SQL Server support lands
The signals worth tracking here are the count of plan guides per database as an inventory fact, the fn_validate_plan_guide output as a scheduled check so a silently-dead guide pages instead of rotting, and the runtime stats of the pinned statement so you can see the day the pinned plan stops being the good one. A guide count that only ever grows is its own smell. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status. Until it ships, schedule the validation query above, name your guides like they will be read by a stranger at 2 A.M., and put the removal date in the ticket that created them.