SQL Server Plan Guides: A Walkthrough Part 1

Plan guides are some strange things, and can be very high risk. This post does not have the intention to get all you fine people to implement plan guides everywhere, it should be one of the last resort options in your toolkit. You will not directly see the query’s being adapted by it, meaning that troubleshooting this will be way harder. The first part will create some data and some tests before going deeper into it.

Create some test data

CREATE TABLE DBO.TABLE1( ID INT IDENTITY(1,1), VALUE NVARCHAR(255));
CREATE TABLE DBO.TABLE2( ID INT IDENTITY(1,1), TABLE1ID INT, VALUE INT);

SET NOCOUNT ON;

DECLARE @I INT = 1
WHILE @I < 10000
BEGIN
INSERT INTO DBO.TABLE1(VALUE)
VALUES('PLAN GUIDE DEMO')

INSERT INTO DBO.TABLE2(TABLE1ID,VALUE)
VALUES(@I,@I)

INSERT INTO DBO.TABLE2(TABLE1ID,VALUE)
VALUES(5,5)

SET @I += 1
END

Create some indexes for the query’s

--CLUSTERED INDEXES
CREATE CLUSTERED INDEX CX_TABLE1_ID
on dbo.TABLE1(ID)
CREATE CLUSTERED INDEX CX_TABLE2_ID
on dbo.TABLE2(ID)
-- SEEK NC INDEX
CREATE INDEX IX_TABLE2_VALUE_TABLE1ID
on dbo.TABLE2(Value,TABLE1ID)
Query’s our application sends to the server:
EXEC SP_EXECUTESQL N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3)',N'@P1 int, @P2 int, @P3 int', @P1 = 4, @P2 = 5, @P3=6
	Returns 10002 ROWS

EXEC SP_EXECUTESQL N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3)',N'@P1 int, @P2 int, @P3 int', @P1 = 7, @P2 = 8, @P3=9

– Returns 3 ROWS

Oh-oh, we got parameter sniffing.

 OPTION(RECOMPILE) solves all your issues, and the pro’s of adding this outweigh the con’s.

EXEC SP_EXECUTESQL N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3) OPTION(RECOMPILE)',N'@P1 int, @P2 int, @P3 int', @P1 = 4, @P2 = 5, @P3=6
EXEC SP_EXECUTESQL N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3) OPTION(RECOMPILE)',N'@P1 int, @P2 int, @P3 int', @P1 = 7, @P2 = 8, @P3=9

That’s better

 But you can’t alter the application code no matter what, and all the other options where explored.

Introducing the plan guide

Remember that you need to create it In the database where the query is executed.

So for example, if a query like this is happening:

USE MASTER
GO
EXEC SP_EXECUTESQL N'SELECT * FROM [MY_TEST].[DBO].[TABLE1] T1 INNER JOIN [MY_TEST].[DBO].[TABLE2] T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3)',N'@P1 int, @P2 int, @P3 int', @P1 = 4, @P2 = 5, @P3=6
EXEC SP_EXECUTESQL N'SELECT * FROM [MY_TEST].[DBO].[TABLE1] T1 INNER JOIN [MY_TEST].[DBO].[TABLE2] T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3)',N'@P1 int, @P2 int, @P3 int', @P1 = 7, @P2 = 8, @P3=9

The plan guide will have to be created in the Master database, unfortunately.

Plan Guide parameters

@Name: the name you choose for the plan guide, can be found in sys.plan_guides

@stmt: the whole statement, without the Parameter definition or values. ANY SPACES OR OTHER CHARACTERS HERE WILL MAKE THE PLAN GUIDE INVALID

@Type:

• OBJECT (stored procedure, function, trigger, tvf, svf)
• SQL (Statement) PARAMETERIZATION { FORCED | SIMPLE ) can’t be specified for this type
• TEMPLATE à Only PARAMETERIZATION { FORCED | SIMPLE ) can be specified for this type

TEMPLATE exists if you for example don’t want to apply FORCED PARAMETERIZATION on all the queries when enabling this at the database level OR VICE VERSA.

You will have to use sp_get_query_template to find the parameterized forms of the query.

@module_or_batch: Can be NULL, or the Object name or the batch text. If it is NULL then and the Type = SQL, the value will be set to the same value in @stmt.

@params = exact parameters used in the query, only for type = SQL or TEMPLATE

@hints= the hints you want to add to the query.

@hints overrides the behavior of the existing options clause, more on that in example 3

Plan Guide Example 1: an Oopsie

sp_create_plan_guide   
@name = N'PLAN_GUIDE_DEMO',   
@stmt = N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3)',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = '@p1 int,@p2 int,@p3 int',
@hints = N'OPTION (RECOMPILE)';

-- IT IS NOT WORKING???

EXEC SP_EXECUTESQL N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3)',N'@P1 int, @P2 int, @P3 int', @P1 = 4, @P2 = 5, @P3=6
EXEC SP_EXECUTESQL N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3)',N'@P1 int, @P2 int, @P3 int', @P1 = 7, @P2 = 8, @P3=9

“For plan guides that specify @type = ‘SQL’ or @type = ‘TEMPLATE’ to successfully match a query, the values for batch_text and @parameter_name data_type [,…n ] must be provided in exactly the same format as their counterparts submitted by the application. “

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-create-plan-guide-transact-sql?view=sql-server-2017#plan-guide-matching-requirements

Plan Guide Example 2: Great Success

First , drop the existing false plan guide:

-- Drop the plan guide 
EXEC sp_control_plan_guide N'DROP', N'PLAN_GUIDE_DEMO2';  
GO  

-- THIS WORKS:
sp_create_plan_guide   
@name = N'PLAN_GUIDE_DEMO',   
@stmt = N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3)',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = '@P1 int, @P2 int, @P3 int', -- THE PARAMETER LIST ALSO NEEDS TO MATCH
@hints = N'OPTION (RECOMPILE)';
EXEC SP_EXECUTESQL N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3)',N'@P1 int, @P2 int, @P3 int', @P1 = 4, @P2 = 5, @P3=6
EXEC SP_EXECUTESQL N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3)',N'@P1 int, @P2 int, @P3 int', @P1 = 7, @P2 = 8, @P3=9

Plan 1:

Plan 2:

Plan Guide Example 3: Overriding Behaviour

@hints overrides the behavior of the existing options clause, more on that in example 3

EXEC SP_EXECUTESQL N'SELECT * FROM [MY_TEST].[DBO].[TABLE1] T1 INNER JOIN [MY_TEST].[DBO].[TABLE2] T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3) OPTION(LOOP JOIN)',N'@P1 int, @P2 int, @P3 int', @P1 = 4, @P2 = 5, @P3=6
EXEC SP_EXECUTESQL N'SELECT * FROM [MY_TEST].[DBO].[TABLE1] T1 INNER JOIN [MY_TEST].[DBO].[TABLE2] T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3) OPTION(LOOP JOIN)',N'@P1 int, @P2 int, @P3 int', @P1 = 7, @P2 = 8, @P3=9

– Nested loop join on the entire plan.

Create the plan guide with option(recompile)

sp_create_plan_guide   
@name = N'PLAN_GUIDE_DEMO',   
@stmt = N'SELECT * FROM [MY_TEST].[DBO].[TABLE1] T1 INNER JOIN [MY_TEST].[DBO].[TABLE2] T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3) OPTION(LOOP JOIN)', -- Hele bovenstaande query.
@type = N'SQL',  
@module_or_batch = NULL,   
@params = '@P1 int, @P2 int, @P3 int', -- THE PARAMETER LIST ALSO NEEDS TO MATCH
@hints = N'OPTION (RECOMPILE)';

Try again:

EXEC SP_EXECUTESQL N'SELECT * FROM [MY_TEST].[DBO].[TABLE1] T1 INNER JOIN [MY_TEST].[DBO].[TABLE2] T2 ON T1.ID = T2.TABLE1ID WHERE T2.VALUE IN (@P1,@P2,@P3) OPTION(LOOP JOIN)',N'@P1 int, @P2 int, @P3 int', @P1 = 4, @P2 = 5, @P3=6

Bye bye nested loop forcing.

Extra details to keep in mind

• Trying to drop or modify a function, stored procedure, or DML trigger that is referenced by a plan guide, either enabled or disabled, causes an error.
• OBJECTS  WITH ENCRYPTION can’t have plan guides added .

(https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-create-plan-guide-transact-sql?view=sql-server-2017#remarks)

• When you create the plan guide, the plan for the matching query or procedure will be flushed from cache.
• Drop all the plans in the database with this query:

EXEC sp_control_plan_guide N'DROP ALL';

GO

Thanks for reading, and stay tuned for part 2, where I will talk about OBJECT and TEMPLATE TYPES.