Fixed query plan for a stored procedure I am exploring the plan guides topic. Sounds easy, as far as we go through the examples in documentation.

The problem is that I cannot find any working example (nor did I manage to figure it out by myself) for setting a fixed plan for a stored procedure, or a statement inside the procedure.

I've read lots of blog posts, but all of them just copy/paste examples from microsoft docs.

Anyone more creative/expirienced?
Here is an example of forcing a plan for a statement inside a procedure using a plan guide:

USE tempdb;
GO

CREATE PROCEDURE dbo.test
AS
BEGIN

    SET NOCOUNT ON;

    SELECT svp.name AS login_name, dbp.name AS user_name
    FROM sys.database_principals AS dbp
    INNER JOIN sys.server_principals AS svp
        ON dbp.sid = svp.sid;

END
GO

-- Use Ctrl+M to capture the execution plan
-- You will see loop joins used
EXEC dbo.test;


Now generate the execution plan for this statement with a forced join strategy:

SELECT svp.name AS login_name, dbp.name AS user_name
FROM sys.database_principals AS dbp
INNER JOIN sys.server_principals AS svp
    ON dbp.sid = svp.sid
OPTION (HASH JOIN);


Copy the execution plan XML and paste it to the query window. Replace any occurence of ' (single quote) with '''' (four single quotes).

Your final parameter for the sp_create_plan_guide procedure will be in the form:

@xmlPlan = 'OPTION (USE PLAN N''your plan goes here'')'


Now you can see why four quotes: escape once to put the XML inside a string literal, escape twice to put the XML inside a string literal inside another string literal.

Make sure that the XML plan is pasted right after OPTION (USE PLAN N'' : any whitespace or newline will make it invalid.

DECLARE @planXML nvarchar(max) = 'OPTION (USE PLAN N''your modified XML plan here'')';


EXEC sp_create_plan_guide 
    @name = 'UseHash', 
    @stmt = 'SELECT svp.name AS login_name, dbp.name AS user_name
    FROM sys.database_principals AS dbp
    INNER JOIN sys.server_principals AS svp
        ON dbp.sid = svp.sid;',
    @type = 'OBJECT',
    @module_or_batch = 'dbo.test',
    @hints = @planXML
GO

-- Use Ctrl+M to capture the execution plan
-- You will see hash joins used
EXEC dbo.test;