Plan guides are a useful tool for changing an execution plan when despite your best efforts the optimizer just isn’t coming up with the optimal plan.  This becomes useful for situations where you can’t make code changes to a query or stored procedure.  People are sometimes hesitant to use them due to the complexity in getting the syntax and parameters correct.  In this post I will provide a stored procedure that will allow you to create a plan guide so long as you have the plan_handle and hints you want applied to your query.

The Procedure

The procedure is usp_create_plan_guide_with_hint.

This procedure first uses sp_create_plan_guide_from_handle to generate a plan guide that will be used as a starting point (the model).  This plan guide is basically forcing the plan that is currently in place (ie the plan associated with the input @plan_handle, @statement_start_offset).  Next the proc retrieves the parameters that were used to create the plan guide from sys.plan_guides.  Next the model plan guide is dropped. Last the plan guide is created with your hints by calling sp_create_plan_guide.  Important note: this must be executed in the database where the plan guide needs to be created.

CREATE PROCEDURE usp_create_plan_guide_with_hint
@plan_handle VARBINARY(64),
@statement_start_offset INT,
@name NVARCHAR(128),
@hints NVARCHAR(MAX)
AS
BEGIN
DECLARE @model_name NVARCHAR(128)
DECLARE @query_text NVARCHAR(MAX)
DECLARE @scope_type_desc NVARCHAR(60)
DECLARE @scope_object_id INT
DECLARE @scope_batch NVARCHAR(MAX)
DECLARE @parameters NVARCHAR(MAX)
DECLARE @procedure_name NVARCHAR(128)
SET @model_name = @name + '_model'
exec sp_create_plan_guide_from_handle @plan_handle = @plan_handle,
@statement_start_offset = @statement_start_offset,
@name = @model_name
SELECT @query_text = query_text, @scope_type_desc = scope_type_desc,
@scope_object_id = scope_object_id,
@scope_batch = scope_batch, @parameters = parameters
FROM sys.plan_guides WHERE name = @model_name
exec sp_control_plan_guide @name = @model_name, @operation = 'DROP'
IF @scope_object_id IS NOT NULL
BEGIN
SELECT @procedure_name = OBJECT_NAME(@scope_object_id)
exec sp_create_plan_guide @name = @name, @stmt = @query_text, @module_or_batch=@procedure_name,
@type=@scope_type_desc, @params = @parameters,
@hints = @hints
END
ELSE
BEGIN
exec sp_create_plan_guide @name = @name, @stmt = @query_text, @module_or_batch=@scope_batch,
@type=@scope_type_desc, @params = @parameters,
@hints = @hints
END
END


Some Examples

Here are a couple examples of calling this procedure.  This example shows how to force a particular index to be used:

Here is an example that forces an execution plan that you have the XML plan for:

Note, the execution plan XML was truncated above so this could fit in one screenshot.

If You Use SQLGrease

If you are a SQLGrease user, you can easily retrieve the @plan_handle and @statement_start_offset parameters by query sys.dm_exec_query_stats:
SELECT statement_start_offset, plan_handle FROM sys.dm_exec_query_stats WHERE query_plan_hash=0x.......

Edit 03/17/2024: If you are a SQLGrease user you will see a generate plan guide button next to execution plans in the UI.  This will generate the syntax for the plan guide.  You will only need to modify the @hints to reflect what hints you want the plan guide to apply.

Parting Notes

For this to work the plan_handle for the query you would like to change a plan for must exist in the plan cache.   If somebody used a RECOMPILE hint, it could get tricky to find this.  Also, you can use this to remove hints.  Set the @hints parameter to NULL to remove a hint that was added to a query.  Happy tuning!