The application owns the SQL text, but you still need to address its plan. Plan guides can attach an approved hint without editing that application statement. Their usefulness depends on matching the exact query context the application submits.
Capture the Submitted Statement First
A plan guide associates query text or a module with optimization instructions. SQL, OBJECT, and TEMPLATE guides serve different matching contexts. This example uses a SQL guide for a parameterized statement submitted through sp_executesql.
Capture the actual text and parameter declaration before creating the guide. Reconstructing a similar-looking statement from memory risks building a guide that never matches the request you meant to influence.
I inspect the application's parameter types and database context as carefully as its SELECT text. A parameter declared nvarchar isn't identical to a varchar parameter declaration. An object guide needs the correct module and statement.
A template guide addresses parameterization and follows another creation process. Tie the guide type to the real submission path. An unrelated example doesn't establish the correct matching context.
Establish an Isolated Baseline
Use a disposable database for the sample table. The two rows are invented input and don't establish a performance problem. Execute the parameterized statement with an actual plan enabled in SSMS.
Preserve that baseline and the parameter value. In a real tuning task, include representative input ranges. A guide that improves one request can create a different trade for another parameter value.
The statement and parameter declaration below are reused unchanged in the guide definition. That makes the matching contract easy to see. Don't add a comment or alter the submitted batch during verification and then blame the guide for a changed match.
Exact context matters. The fixture demonstrates the mechanism. It doesn't establish a CPU reduction for an unmeasured query.
CREATE TABLE dbo.PlanGuideDemo(ProductId int PRIMARY KEY,Price decimal(19,4) NOT NULL);
INSERT dbo.PlanGuideDemo VALUES (1,10),(2,20);
EXEC sys.sp_executesql
N'SELECT ProductId,Price FROM dbo.PlanGuideDemo WHERE ProductId = @ProductId;',
N'@ProductId int',@ProductId = 1;Create Plan Guides With a Narrow Hint
The sp_create_plan_guide procedure stores the association in the current database. The example attaches OPTION(RECOMPILE), which recompiles the statement for its execution. That can help a particular parameter-sensitive request and adds compilation cost.
Another reviewed guide can attach a MAXDOP hint when limiting parallelism addresses the measured problem. Choose the hint for its documented behavior, not merely because the command accepts it.
All arguments below use names and follow the documented order. A NULL module_or_batch for this SQL guide uses the statement as its batch context. The parameter definition matches the actual sp_executesql call.
Review permissions and edition support before deploying. A guide changes optimization for matching requests. Test it like another tuning change and keep a clear reversal plan.
EXEC sys.sp_create_plan_guide
@name = N'Guide_PlanGuideDemo',
@stmt = N'SELECT ProductId,Price FROM dbo.PlanGuideDemo WHERE ProductId = @ProductId;',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@ProductId int',
@hints = N'OPTION (RECOMPILE)';
Validate Plan Guides After Creating Them
The sys.fn_validate_plan_guide function checks the stored guide against the current environment and returns validation errors when found. Query the guide's metadata separately so a missing guide isn't mistaken for a valid one with no errors. Validating plan guides doesn't prove application matching. It establishes that the stored definition is valid under the database state available to the validation function at that time.
I rerun validation after relevant schema or index changes. Hints referring to changed structures deserve particular attention. Keep the guide's name, type, enabled state, and validation evidence in the maintenance record.
The function doesn't measure runtime benefits or certify every parameter case. Those remain workload checks. A clean definition is the start of the test, not its final conclusion.
SELECT name,is_disabled,scope_type_desc,query_text,parameters,hints
FROM sys.plan_guides WHERE name = N'Guide_PlanGuideDemo';
SELECT v.*
FROM sys.plan_guides AS g
CROSS APPLY sys.fn_validate_plan_guide(g.plan_guide_id) AS v
WHERE g.name = N'Guide_PlanGuideDemo';Here the metadata query showed the guide enabled with scope SQL, and the validation function returned no rows.
Confirm the Guide in the Actual Plan
Execute the same sp_executesql statement again with the actual plan enabled. Inspect the statement's plan properties or XML for PlanGuideName and related guide information. That evidence connects the guide to the compiled request.
The presence of a row in sys.plan_guides only confirms storage. It doesn't show that the application's current text still matches the intended guide.
Compare reads, CPU, compilation effects, and result correctness under representative inputs. Don't claim a reduction without measurements from your server. A RECOMPILE guide can remove reuse for the statement while improving its parameter-specific estimate.
Whether that trade fits depends on execution frequency and cost. Keep the original behavior and hinted behavior side by side, using the same data and request contract.
EXEC sys.sp_executesql
N'SELECT ProductId,Price FROM dbo.PlanGuideDemo WHERE ProductId = @ProductId;',
N'@ProductId int',@ProductId = 1;In my run, the actual plan XML for this execution carried PlanGuideName=”Guide_PlanGuideDemo”, so the guide matched the submitted statement.
Compare Plan Guides With Query Store Hints
Query Store hints in newer supported versions attach an approved hint to a captured query identity. That can avoid the text-matching management burden of a plan guide. It requires Query Store and the appropriate query record.
A query identity still needs validation against the intended request. Don't select a similar text fragment and assume it represents the same application statement or runtime context.
Which mechanism will the team be able to review and remove reliably? That operational answer matters alongside engine support. Keep one tuning authority for the request instead of stacking unexplained guide and Query Store choices.
Check their interaction in the documented behavior for your deployment. Keep the intervention narrow and understandable. Review it after application updates for lost matching or conflicting tuning decisions.
Keep a Disable Path and an Owner
The sp_control_plan_guide procedure can disable the specific guide when the comparison or later workload change no longer supports it. Record that action with the original definition. An application release can change statement text or parameters and stop matching the guide.
Review it at that boundary. A stored tuning object nobody remembers isn't a maintenance strategy, even when it has an impressively precise name.
Use plan guides when application SQL can't be edited and a measured hint has a clear purpose. Validate the definition, confirm matching in the plan, and review representative inputs. Prefer the mechanism your deployment and team can operate clearly.
The adjustment needs an owner and a reversal command. A hint attached outside the application still influences every matching request, so it deserves ordinary tuning accountability.
-- Reversal after the controlled comparison, when the guide is not being retained.
EXEC sys.sp_control_plan_guide N'DISABLE',N'Guide_PlanGuideDemo';After this call, sys.plan_guides showed is_disabled set to 1. The definition stays stored, so it can be enabled again or dropped later.
Related reading on this blog: Forcing a Plan in Query Store and Checking That It Held and Auditing Query Hints Left in Production Code.
A plan guide is not a rewritten application query, it is a matching rule with a tuning responsibility.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
