Here I am going to talk about Plan Guides and how to use them. In the example below, I will create a ‘plan guide’ to force query parameterization. It can be useful when it is impossible to change the query itself. For example, you are working with the database, and one of the old applications doesn’t use parameters, and you want to reduce the number of compilations.
I am going to use the StackOverflow database, I downloaded it from here, but the original copy is here.
Clearing the procedure cache.

Use
USE StackOverflow2013
GO
DBCC FREEPROCCACHE
GO

Creating an index to make a query run faster.

DROP INDEX IF EXISTS ix_Posts_OwnerUserId ON dbo.Posts;
CREATE NONCLUSTERED INDEX ix_Posts_OwnerUserId ON dbo.Posts (OwnerUserId);

Then executing a query without a parameter:

/*
Execute non-parameterized query
*/
SELECT TOP 10 u.Id, u.DisplayName,p.Title FROM users AS u
INNER JOIN posts AS p ON u.Id=p.OwnerUserId
WHERE u.Id = 1
GO

When it completes, let’s check the query plan.

/*check query plan*/
SELECT s1.execution_count, s2.text AS query_text, CAST(s3.query_plan AS XML) query_plan 
FROM sys.dm_exec_query_stats AS s1  
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS s2   
CROSS APPLY sys.dm_exec_text_query_plan (plan_handle, statement_start_offset, statement_end_offset) AS s3

Here is the result.

We can see that it doesn’t use a parameter. Let’s fix it! The first store procedure just prepares the parameters for the second one, which is creating a ‘plan guide.’

/*
Creating plan quide
*/
DECLARE @stmt NVARCHAR(MAX);  
DECLARE @params NVARCHAR(MAX);  
EXEC sp_get_query_template   
    N'SELECT TOP 10 u.Id, u.DisplayName,p.Title FROM users AS u
	INNER JOIN posts AS p ON u.Id = p.OwnerUserId
	WHERE u.Id = 1',  
    @stmt OUTPUT,   
    @params OUTPUT; 
EXEC sp_create_plan_guide   
    N'user_posts_query',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)';
GO

If we select parameters, we’ll just see two strings.

After executing the above query, let’s execute the initial query and check what we’ll see in the query plan cache:

/*check the query again*/
DBCC FREEPROCCACHE
GO
/*execute the same query*/
SELECT TOP 10 u.Id, u.DisplayName,p.Title FROM users AS u
INNER JOIN posts AS p ON u.Id=p.OwnerUserId
WHERE u.Id = 1
GO
/*check the query*/
SELECT s1.execution_count, s2.text AS query_text, CAST(s3.query_plan AS XML) query_plan 
FROM sys.dm_exec_query_stats AS s1  
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS s2   
CROSS APPLY sys.dm_exec_text_query_plan (plan_handle, statement_start_offset, statement_end_offset) AS s3
GO

Now it is using a parameter. “(@0 int) … where u . Id = @0”. Plan guide works!
All plan guides can be found querying the system view.

/* Check if plan guide was created */
select * from sys.plan_guides

Plan guide can be used to set MAXDOP or other options like RECOMPILE; please check the link below.Also, plan guide can be validated using system function ‘select * from sys.fn_validate_plan_guide(65537)’ or dropped by executing stored procedure ‘sp_control_plan_guide’.
I found a ‘plan guide’ to be a quite useful feature in SQL Server. I hope my post encouraged you to try it by yourself.
For those of you who like to read documentation: https://docs.microsoft.com/en-us/sql/relational-databases/performance/plan-guides?view=sql-server-ver15

See you in the next post!