Here is an example of setting an option inforcing 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;