Very rarely there is a need to force an execution plan and even more rarely we should be doing it. But once you decide there is no other way or you need a quick temporary workaround to get production going and gain some buffer time for yourself to do proper analysis, you can do as below.

I have done this after migrating from sql server 2005 to sql server 2016. After migration there are some queries that perform slow. And these queries did not work even after forcing legacy cardinality estimate query hint. Not to mention, these queries were working well in 2016 TEST environment …. go figure …

I am assuming you have worked with Plan Guides earlier. I am just showing how to force an execution plan using Plan Guides.

Step 1

Get the OLD execution plan from old server. You can get this from SSMS or DMV’s or Profiler. Once you have this , right-click on graphical execution plan and get its XML

Step 2

At this point you have 1.) Query text that needs to be tuned 2.) Its execution plan XML

DECLARE @xml_showplan nvarchar(max);

DECLARE @sql nvarchar(max);

SET @xml_showplan = ‘<ShowPlanXML>………….</ShowPlanXML>’

SET @sql = ‘select * from dba..sqlserverInfo where Application like ”%cognos%” order by Application’

EXEC sp_create_plan_guide

@name = N’GuidePlanName’,

@stmt = @sql,

@type = N’SQL’,

@module_or_batch = NULL,

@params = NULL,

@hints = @xml_showplan;

GO

Step 3

To verify weather the plan guide is getting picked up, you can 1.) either run profiler with “Plan guide successful” event or 2.) get graphical exec plan and open its XML and search for keyword “Guide”

We might think that the new Query Store feature for forcing plan could be use here but since the query never ran fast on the new server I was unable to use it. However, if the query had regressed on the same environment we could have done that.