SQL Server Plan Guides: The Last Resort That Works When Nothing Else Can

---

The query optimizer picks an execution plan based on statistics, indexes, and cost estimates. Most of the time it makes good decisions. Occasionally it does not, and the bad decision is locked into a cached plan that runs thousands of times a day while DBAs chase symptoms without being able to touch the application code. The query comes from a third-party ERP system, a packaged application, or a vendor-controlled stored procedure. The source code is off limits. The query cannot be changed.

Plan Guides are the mechanism SQL Server provides for exactly this scenario. They intercept a specific query at compile time and attach optimizer hints or a fixed query plan to it before the optimizer ever makes its decision, without changing a single character of the original query text. According to Microsoft documentation, Plan Guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server.

This article covers Plan Guide internals, all three types, the character-for-character matching trap that breaks most first attempts, validation and monitoring, and a complete decision matrix for when to use Plan Guides versus the newer Query Store alternatives available in SQL Server 2022 and later.

1 What a Plan Guide Is Beginner

A Plan Guide is a database-scoped metadata object that tells SQL Server how to handle a specific query at compile time. It does not change the query text, the stored procedure, the application, or any database objects. It operates entirely within the optimizer pipeline, intercepting a query when it is being compiled and injecting optimizer hints or a fixed execution plan before the optimizer makes its decisions.

The practical effect is identical to adding an OPTION clause directly to the query. The difference is that the query text in the application is never modified. From the application’s perspective, the query is unchanged. From the optimizer’s perspective, the query arrives with additional directives already attached.

Plan Guides are supported on SQL Server Standard, Developer, Evaluation, and Enterprise editions. They persist when a database is restored or attached to an upgraded SQL Server version, meaning they survive upgrades and migrations without being recreated.

2 How Plan Guide Matching Works Internally Intermediate

Understanding the internal matching process is essential for using Plan Guides correctly. When SQL Server receives a query for compilation, before the optimizer begins its work it checks whether any Plan Guide in the current database matches the incoming statement. If a match is found, the OPTION clause from the Plan Guide is attached to the query, and the optimizer then compiles the query with those hints in place.

The matching process for SQL and TEMPLATE type Plan Guides is character-for-character string comparison. According to Microsoft documentation, the values for the @module_or_batch and @params arguments must match the query exactly as SQL Server receives it. No internal conversion is performed. A single extra space, a tab character instead of a space, or different case in a keyword is enough to prevent matching.

The sequence of events at compile time:

1. Query arrives at the SQL Server Database Engine for compilation
2. SQL Server checks sys.plan_guides for a matching Plan Guide in the current database
3. If a match is found, the Plan Guide’s OPTION clause is attached to the query
4. The optimizer compiles the modified query including the injected hints
5. The resulting plan is cached and executed
6. The application receives results without any knowledge that the Plan Guide intervened

3 The Three Plan Guide Types Beginner

SQL Server supports three Plan Guide types that cover different query execution contexts. The type determines what the @module_or_batch parameter means and how matching is performed.

Type	Matches	@module_or_batch Value	Best For
OBJECT	Queries inside stored procedures, user-defined scalar functions, multi-statement table-valued functions, and DML triggers	The schema-qualified name of the stored procedure or function	Third-party stored procedures that cannot be modified. Most reliable type because it is anchored to a specific object.
SQL	Standalone T-SQL statements and batches that are not part of any database object, including queries submitted via sp_executesql	The full batch text, or NULL to match any single-statement batch	Ad-hoc queries, ORM-generated queries, application queries not in stored procedures. Most brittle type due to character-for-character matching.
TEMPLATE	Standalone queries that parameterize to a specified form	NULL (always)	Controlling parameterization behavior for classes of queries. Used when PARAMETERIZATION database setting needs to be overridden for specific query patterns.

OBJECT type: the most reliable

OBJECT type Plan Guides target a specific named database object. When the stored procedure or function executes, SQL Server checks for OBJECT Plan Guides that reference it and applies any matching guides to the specific statements within the object. Because matching is tied to the object name rather than raw query text, OBJECT guides are far more resilient to minor formatting variations.

SQL type: powerful but brittle

SQL type Plan Guides match standalone queries by comparing the exact batch text character by character. They are the right choice for application queries that are not in stored procedures, but they require capturing the query exactly as it arrives at SQL Server, which is a common source of failed matching.

TEMPLATE type: parameterization control

TEMPLATE Plan Guides match queries that share the same parameterized form. They are used in two specific situations: when the database-level PARAMETERIZATION is set to FORCED and specific queries should use SIMPLE parameterization, or when PARAMETERIZATION is SIMPLE and specific query patterns should use FORCED parameterization. Only one TEMPLATE plan guide can match a given statement.

4 Hints That Can Be Applied Beginner

Any valid OPTION clause that can be applied directly to a query can be specified in a Plan Guide’s @hints parameter. The most commonly used hints in Plan Guides are listed below. The full list of valid query hints is documented in Microsoft Learn under Query Hints (Transact-SQL).

Hint	Effect	Common Use Case
OPTION (RECOMPILE)	Forces the optimizer to generate a new plan on every execution. Discards the cached plan immediately after use.	Parameter sniffing where different parameter values need different plans
OPTION (OPTIMIZE FOR (@param = value))	Tells the optimizer to compile assuming a specific value for a parameter regardless of what value is actually passed	Locking in a good plan for the most common or most representative parameter value
OPTION (OPTIMIZE FOR UNKNOWN)	Forces the optimizer to use statistical averages rather than the sniffed parameter value when compiling	Reducing parameter sniffing sensitivity without forcing RECOMPILE
OPTION (MAXDOP N)	Limits the degree of parallelism for the query to N threads	Capping parallelism on specific expensive queries without changing instance-level MAXDOP
OPTION (INDEX (IndexName))	Forces the optimizer to use a specific index on the referenced table	Third-party queries choosing a poor index when a better one exists
OPTION (FORCESEEK)	Forces the optimizer to use index seeks rather than scans on referenced tables	Queries performing unnecessary full scans when selective seeks are available
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))	Compiles without using the sniffed parameter values, equivalent to OPTIMIZE FOR UNKNOWN	Parameter sniffing problems without revealing specific parameter values
OPTION (USE PLAN N'...')	Forces a specific XML query plan to be used. The plan is embedded in the hint.	Locking an exact plan that is known to perform well

5 Creating Plan Guides with sp_create_plan_guide Intermediate

Plan Guides are created using sys.sp_create_plan_guide. The parameters must be provided in order. The guide takes effect immediately after creation for queries that have not yet been compiled. Queries with existing cached plans must have those plans evicted before the Plan Guide takes effect on them.

Example 1: OBJECT type for a stored procedure

-- Apply OPTION(RECOMPILE) to a specific statement inside a stored procedure
-- Use when the stored procedure belongs to a vendor and cannot be modified
-- Requires ALTER permission on the referenced object

EXEC sys.sp_create_plan_guide
    @name           = N'PG_GetCustomerOrders_Recompile',
    @stmt           = N'SELECT o.OrderID, o.OrderDate, o.CustomerID
                        FROM dbo.Orders o
                        WHERE o.CustomerID = @CustID
                        ORDER BY o.OrderDate DESC',
    @type           = N'OBJECT',
    @module_or_batch = N'dbo.usp_GetCustomerOrders',  -- exact schema.procname
    @params         = NULL,
    @hints          = N'OPTION (RECOMPILE)';
GO

Example 2: OBJECT type to force a specific index

-- Force the optimizer to use a specific index on a table inside a stored procedure
-- Use when the optimizer is choosing a full table scan over an available index

EXEC sys.sp_create_plan_guide
    @name           = N'PG_ProductSearch_ForceIdx',
    @stmt           = N'SELECT ProductID, ProductName, ListPrice
                        FROM dbo.Products
                        WHERE CategoryID = @CatID
                          AND ListPrice > @MinPrice',
    @type           = N'OBJECT',
    @module_or_batch = N'dbo.usp_SearchProducts',
    @params         = NULL,
    @hints          = N'OPTION (TABLE HINT (dbo.Products, INDEX (IX_Products_CategoryID_Price)))';
GO

Example 3: SQL type for a standalone ad-hoc query

-- Limit parallelism on a specific ad-hoc query from an application
-- The @stmt text must match exactly character for character
-- Capture the exact text using Extended Events (see Section 6)

EXEC sys.sp_create_plan_guide
    @name           = N'PG_SalesReport_MaxDOP2',
    @stmt           = N'SELECT SalesRepID, SUM(Amount) AS TotalSales
FROM dbo.SalesTransactions
WHERE TransactionDate >= @StartDate
  AND TransactionDate <  @EndDate
GROUP BY SalesRepID
ORDER BY TotalSales DESC',
    @type           = N'SQL',
    @module_or_batch = NULL,
    @params         = N'@StartDate datetime, @EndDate datetime',
    @hints          = N'OPTION (MAXDOP 2)';
GO

Example 4: SQL type to force OPTIMIZE FOR UNKNOWN

-- Force OPTIMIZE FOR UNKNOWN to avoid parameter sniffing
-- on a standalone query that is not in a stored procedure

EXEC sys.sp_create_plan_guide
    @name           = N'PG_CustomerLookup_OptimizeForUnknown',
    @stmt           = N'SELECT CustomerID, CompanyName, ContactName, Country
FROM dbo.Customers
WHERE Country = @Country',
    @type           = N'SQL',
    @module_or_batch = NULL,
    @params         = N'@Country nvarchar(15)',
    @hints          = N'OPTION (OPTIMIZE FOR UNKNOWN)';
GO

6 The Character-for-Character Trap Advanced

The single most common reason Plan Guides fail to match is incorrect query text. For SQL and TEMPLATE type guides, SQL Server compares the @stmt value character by character against the incoming query text. A single difference in whitespace, line endings, case, or parameter declaration format prevents the match. The guide is created successfully but never fires.

According to Microsoft documentation, the @stmt text must be provided in the identical format as it is submitted to SQL Server. No internal conversion is performed.

Capturing the exact query text with Extended Events

-- Create an Extended Events session to capture exact query text
-- as SQL Server receives it, before any normalization

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = N'CaptureExactQueryText')
    DROP EVENT SESSION [CaptureExactQueryText] ON SERVER;
GO

CREATE EVENT SESSION [CaptureExactQueryText] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.plan_handle,
        sqlserver.database_name
    )
    WHERE sqlserver.database_name = N'YourDatabase'
      AND sqlserver.sql_text LIKE N'%YourQueryKeyword%'
),
ADD EVENT sqlserver.rpc_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.database_name
    )
    WHERE sqlserver.database_name = N'YourDatabase'
)
ADD TARGET package0.ring_buffer (SET max_memory = 51200)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);
GO

ALTER EVENT SESSION [CaptureExactQueryText] ON SERVER STATE = START;
GO

-- After capturing, stop and clean up
-- ALTER EVENT SESSION [CaptureExactQueryText] ON SERVER STATE = STOP;
-- DROP EVENT SESSION [CaptureExactQueryText] ON SERVER;

Verifying text from the plan cache

-- Find the exact query text for a query currently in plan cache
-- Use the text from this output directly in @stmt to ensure character-for-character match

SELECT
    qs.plan_handle,
    qs.execution_count,
    qs.total_elapsed_time / NULLIF(qs.execution_count, 0)   AS AvgElapsedUs,
    qs.total_logical_reads / NULLIF(qs.execution_count, 0)  AS AvgLogicalReads,
    SUBSTRING(
        qt.text,
        (qs.statement_start_offset / 2) + 1,
        CASE WHEN qs.statement_end_offset = -1
             THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
             ELSE (qs.statement_end_offset - qs.statement_start_offset) / 2 + 1
        END
    )                                                       AS StatementText,
    qt.text                                                 AS FullBatchText
FROM sys.dm_exec_query_stats          qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE N'%YourKeywordHere%'
ORDER BY qs.total_elapsed_time DESC;

7 TEMPLATE Plan Guides for Parameterization Control Intermediate

TEMPLATE Plan Guides control how SQL Server parameterizes a class of queries. They are the only way to override the database-level PARAMETERIZATION setting for specific query patterns without changing the setting for the entire database.

-- Scenario: database PARAMETERIZATION is set to SIMPLE (default)
-- but a specific query pattern should use FORCED parameterization
-- to reduce plan cache bloat from repeated single-use plans

-- First, use sp_get_query_template to get the parameterized form
-- of the query that will be used in the TEMPLATE plan guide
DECLARE @stmt       NVARCHAR(MAX);
DECLARE @params     NVARCHAR(MAX);

EXEC sys.sp_get_query_template
    N'SELECT * FROM dbo.Orders WHERE CustomerID = 12345',
    @stmt   OUTPUT,
    @params OUTPUT;

-- Review the parameterized form returned
SELECT @stmt AS ParameterizedStatement, @params AS ParameterDeclaration;

-- Then create the TEMPLATE plan guide using the exact parameterized text
EXEC sys.sp_create_plan_guide
    @name           = N'PG_OrderLookup_ForcedParam',
    @stmt           = @stmt,
    @type           = N'TEMPLATE',
    @module_or_batch = NULL,
    @params         = @params,
    @hints          = N'OPTION (PARAMETERIZATION FORCED)';
GO

8 Validating Plan Guides Intermediate

Creating a Plan Guide does not guarantee it will match the target query. Validation must be performed after creation. SQL Server provides two tools: sp_validate_plan_guide for checking whether an OBJECT type guide is still valid, and the Extended Events approach for confirming a SQL type guide is actually matching at runtime.

-- View all Plan Guides in the current database
SELECT
    name,
    scope_type_desc,
    is_disabled,
    query_text,
    hints,
    scope_object_name
FROM sys.plan_guides
ORDER BY name;

-- Validate a specific Plan Guide
-- Returns a result set indicating whether the guide is valid
EXEC sys.sp_validate_plan_guide @name = N'PG_GetCustomerOrders_Recompile';

-- Validate ALL Plan Guides in the current database
EXEC sys.sp_validate_plan_guide;

-- A Plan Guide becomes invalid if the referenced object is modified
-- (e.g., stored procedure is altered, index is dropped)
-- Invalid Plan Guides are silently ignored at runtime, not errored

The most reliable way to confirm a SQL type Plan Guide is matching is through Extended Events. SQL Server fires specific events when a Plan Guide successfully matches or fails to match a query.

-- Extended Events session to confirm Plan Guide matching
-- Captures both successful matches and failed matches

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = N'PlanGuideTracking')
    DROP EVENT SESSION [PlanGuideTracking] ON SERVER;
GO

CREATE EVENT SESSION [PlanGuideTracking] ON SERVER
ADD EVENT sqlserver.plan_guide_successful (
    ACTION (
        sqlserver.database_name,
        sqlserver.sql_text,
        sqlserver.plan_handle
    )
),
ADD EVENT sqlserver.plan_guide_unsuccessful (
    ACTION (
        sqlserver.database_name,
        sqlserver.sql_text
    )
)
ADD TARGET package0.ring_buffer (SET max_memory = 51200)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);
GO

ALTER EVENT SESSION [PlanGuideTracking] ON SERVER STATE = START;
GO

-- Run the target query, then check the ring buffer for events
-- plan_guide_successful = guide fired correctly
-- plan_guide_unsuccessful = guide was found but could not be applied
--   (different from not matching -- if the guide is wrong it fires but fails)

ALTER EVENT SESSION [PlanGuideTracking] ON SERVER STATE = STOP;
DROP EVENT SESSION [PlanGuideTracking] ON SERVER;
GO

9 Monitoring Plan Guide Execution Advanced

After confirming a Plan Guide is matching and applying correctly, ongoing monitoring confirms it continues to produce the intended effect as the workload evolves. The plan cache and Query Store both surface plan guide activity.

-- Find queries in the plan cache where a Plan Guide was applied
-- The UsesPlanGuide attribute appears in the XML of plans that were guided
SELECT
    qs.plan_handle,
    qs.execution_count,
    qs.total_elapsed_time / NULLIF(qs.execution_count, 0) AS AvgElapsedUs,
    qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS AvgLogicalReads,
    qt.text                                                 AS QueryText,
    qp.query_plan
FROM sys.dm_exec_query_stats                                qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)             qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)          qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE N'%PlanGuideName="%'
ORDER BY qs.total_elapsed_time DESC;

-- The plan XML for a guided query contains an attribute like:
-- PlanGuideName="PG_GetCustomerOrders_Recompile"
-- Confirming which guide is active for a given plan

-- Check whether Query Store has plans where a Plan Guide was used
-- (available from SQL Server 2016 onward with Query Store enabled)
SELECT
    qsq.query_id,
    qsp.plan_id,
    qsrs.avg_duration / 1000.0          AS AvgDurationMs,
    qsrs.count_executions,
    LEFT(qsqt.query_sql_text, 200)       AS QueryText
FROM sys.query_store_plan                qsp
JOIN sys.query_store_query               qsq  ON qsp.query_id  = qsq.query_id
JOIN sys.query_store_query_text          qsqt ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_runtime_stats       qsrs ON qsp.plan_id   = qsrs.plan_id
WHERE CAST(qsp.query_plan AS NVARCHAR(MAX)) LIKE N'%PlanGuideName="%'
ORDER BY qsrs.avg_duration DESC;

10 Managing Plan Guides Beginner

-- Disable a Plan Guide (preserves it without deleting)
-- Useful for testing: disable and confirm performance changes
EXEC sys.sp_control_plan_guide
    @operation = N'DISABLE',
    @name      = N'PG_GetCustomerOrders_Recompile';

-- Re-enable a disabled Plan Guide
EXEC sys.sp_control_plan_guide
    @operation = N'ENABLE',
    @name      = N'PG_GetCustomerOrders_Recompile';

-- Drop a Plan Guide permanently
EXEC sys.sp_control_plan_guide
    @operation = N'DROP',
    @name      = N'PG_GetCustomerOrders_Recompile';

-- Disable ALL Plan Guides in the current database
EXEC sys.sp_control_plan_guide
    @operation = N'DISABLE ALL';

-- Drop ALL Plan Guides in the current database
EXEC sys.sp_control_plan_guide
    @operation = N'DROP ALL';

-- After dropping or disabling a Plan Guide, flush the plan cache
-- so previously guided plans are recompiled without the guide
-- Use DBCC FREEPROCCACHE only in a controlled window
DBCC FREEPROCCACHE;

11 Plan Guides vs Query Store Hints vs Plan Forcing: Decision Matrix Intermediate

SQL Server now has three mechanisms for influencing query plan behavior without changing query text. The right choice depends on the SQL Server version, whether Query Store is enabled, and what the specific problem is. According to Microsoft documentation, Query Store hints override hard-coded statement-level hints and existing Plan Guides, making them the highest-priority intervention on SQL Server 2022 and later.

Feature	Available From	Matching Method	Ease of Use	Best For
Plan Guide (OBJECT)	SQL Server 2005+	Object name match	Reliable	Third-party stored procedures on any SQL Server version
Plan Guide (SQL)	SQL Server 2005+	Character-for-character text	Brittle, requires exact text	Standalone queries on SQL Server 2016-2019 where Query Store hints unavailable
Plan Guide (TEMPLATE)	SQL Server 2005+	Parameterized form match	Moderate	Parameterization control for query classes on any SQL Server version
Query Store Hints	SQL Server 2022+	Query ID from Query Store	Simple, no text matching required	Any query already captured in Query Store. Preferred over Plan Guides on SQL 2022+.
Query Store Plan Forcing	SQL Server 2016+	Plan ID from Query Store	Simple via SSMS UI or DMV	When a specific previously-good plan needs to be locked in after regression

Decision flow

1. On SQL Server 2022 or Azure SQL with Query Store available: use Query Store Hints for standalone queries. Query Store Hints require no character matching and are easier to manage and remove. They override Plan Guides when both exist.
2. On SQL Server 2016, 2017, or 2019: use Plan Guide (OBJECT) for queries inside stored procedures. Use Plan Guide (SQL) for standalone queries but capture the exact text first.
3. For plan regression where a known-good plan exists in Query Store: use Query Store Plan Forcing. It is the simplest intervention and can be done through the SSMS Query Store UI without writing any T-SQL.
4. For parameterization control across a class of queries: use TEMPLATE Plan Guides on any SQL Server version, or the PARAMETERIZATION database setting if the behavior is needed globally.

12 Workshop: Plan Guide End-to-End Advanced

This workshop walks through a complete Plan Guide scenario from problem identification to verification. Run in a non-production environment.

Setup: create a performance problem

-- Create a test table with skewed data
-- 95% of rows for one customer, 5% for others
-- This creates a parameter sniffing scenario

IF OBJECT_ID('dbo.PlanGuideTest', 'U') IS NOT NULL DROP TABLE dbo.PlanGuideTest;

CREATE TABLE dbo.PlanGuideTest
(
    OrderID     INT IDENTITY PRIMARY KEY,
    CustomerID  INT NOT NULL,
    OrderDate   DATETIME NOT NULL DEFAULT GETDATE(),
    Amount      DECIMAL(10,2)
);

-- Insert 100,000 rows: CustomerID 1 has 95,000; others share 5,000
INSERT INTO dbo.PlanGuideTest (CustomerID, Amount)
SELECT
    CASE WHEN ABS(CHECKSUM(NEWID())) % 100 < 95 THEN 1 ELSE ABS(CHECKSUM(NEWID())) % 100 + 2 END,
    ABS(CHECKSUM(NEWID())) % 10000 / 100.0
FROM sys.all_objects s1
CROSS JOIN (SELECT TOP 200 1 AS n FROM sys.all_objects) s2;

CREATE INDEX IX_PlanGuideTest_CustomerID ON dbo.PlanGuideTest (CustomerID)
    INCLUDE (OrderDate, Amount);

CREATE STATISTICS ST_PlanGuideTest_CustomerID ON dbo.PlanGuideTest (CustomerID)
WITH FULLSCAN;

-- Create a stored procedure simulating a vendor proc that cannot be modified
CREATE OR ALTER PROCEDURE dbo.usp_GetCustomerOrders
    @CustID INT
AS
    SELECT CustomerID, OrderDate, Amount
    FROM dbo.PlanGuideTest
    WHERE CustomerID = @CustID
    ORDER BY OrderDate DESC;
GO

Step 1: Observe the parameter sniffing problem

-- Clear plan cache to start fresh
DBCC FREEPROCCACHE;

-- First execution: sniff CustomerID 1 (95,000 rows) - optimizer chooses scan
EXEC dbo.usp_GetCustomerOrders @CustID = 1;

-- Now execute with a rare customer (optimizer reuses the scan plan)
-- Even though CustomerID 50 has ~50 rows, the plan is wrong for it
EXEC dbo.usp_GetCustomerOrders @CustID = 50;

-- View the current cached plan
SELECT
    qs.execution_count,
    qs.plan_handle,
    qt.text
FROM sys.dm_exec_query_stats          qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE N'%PlanGuideTest%'
  AND qt.text NOT LIKE N'%sys.%';

Step 2: Apply the Plan Guide

-- Apply OPTIMIZE FOR UNKNOWN to prevent sniffing
-- Using OBJECT type since this is a stored procedure
EXEC sys.sp_create_plan_guide
    @name           = N'PG_GetCustomerOrders_NoSniff',
    @stmt           = N'SELECT CustomerID, OrderDate, Amount
    FROM dbo.PlanGuideTest
    WHERE CustomerID = @CustID
    ORDER BY OrderDate DESC',
    @type           = N'OBJECT',
    @module_or_batch = N'dbo.usp_GetCustomerOrders',
    @params         = NULL,
    @hints          = N'OPTION (OPTIMIZE FOR UNKNOWN)';
GO

-- Confirm it was created
SELECT name, scope_type_desc, is_disabled, hints
FROM sys.plan_guides
WHERE name = N'PG_GetCustomerOrders_NoSniff';

Step 3: Verify the Plan Guide fires

-- Create XEvent session to confirm the guide fires
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = N'PlanGuideVerify')
    DROP EVENT SESSION [PlanGuideVerify] ON SERVER;

CREATE EVENT SESSION [PlanGuideVerify] ON SERVER
ADD EVENT sqlserver.plan_guide_successful,
ADD EVENT sqlserver.plan_guide_unsuccessful
ADD TARGET package0.ring_buffer (SET max_memory = 10240);

ALTER EVENT SESSION [PlanGuideVerify] ON SERVER STATE = START;

-- Clear cache and execute
DBCC FREEPROCCACHE;
EXEC dbo.usp_GetCustomerOrders @CustID = 1;
EXEC dbo.usp_GetCustomerOrders @CustID = 50;

-- Check ring buffer for plan_guide_successful events
SELECT
    CAST(xdr AS XML).value('(/event/@name)[1]', 'varchar(100)')    AS EventName,
    CAST(xdr AS XML).value('(/event/data[@name="database_name"]/value)[1]', 'varchar(128)') AS Database
FROM (
    SELECT CAST(target_data AS XML).query('//RingBufferTarget/event') AS xml_data
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address
    WHERE s.name = N'PlanGuideVerify'
) AS data
CROSS APPLY xml_data.nodes('//event') AS XEventData(xdr);

ALTER EVENT SESSION [PlanGuideVerify] ON SERVER STATE = STOP;
DROP EVENT SESSION [PlanGuideVerify] ON SERVER;

Step 4: Confirm the plan shows the guide name

-- After the guide fires, the plan XML contains the guide name
SELECT
    qs.execution_count,
    CAST(qp.query_plan AS NVARCHAR(MAX))   AS PlanXML
FROM sys.dm_exec_query_stats               qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.text LIKE N'%PlanGuideTest%'
  AND qt.text NOT LIKE N'%sys.%';
-- Search the PlanXML column for PlanGuideName="PG_GetCustomerOrders_NoSniff"

Step 5: Clean up

-- Drop the Plan Guide
EXEC sys.sp_control_plan_guide @operation = N'DROP', @name = N'PG_GetCustomerOrders_NoSniff';

-- Drop the test objects
DROP PROCEDURE IF EXISTS dbo.usp_GetCustomerOrders;
DROP TABLE IF EXISTS dbo.PlanGuideTest;

References

• Microsoft Docs: Plan Guides
• Microsoft Docs: sys.sp_create_plan_guide (Transact-SQL)
• Microsoft Docs: sys.plan_guides
• Microsoft Docs: sys.sp_control_plan_guide
• Microsoft Docs: sys.sp_validate_plan_guide
• Microsoft Docs: Query Store Hints (SQL Server 2022+)
• Microsoft Docs: Query Store Hints Best Practices
• Microsoft Docs: Query Hints (Transact-SQL)
• Microsoft Docs: Design and Implement Plan Guides
• Microsoft Docs: Create a New Plan Guide
• Microsoft Docs: sp_get_query_template (for TEMPLATE plan guides)
• SQLYARD: A Beginner to Advanced Guide to Query Store