Note The decrease in performance could be large. For example, a query can finish in a few seconds in SQL Server 2012 SP1. However, the same query can take several hours to run in SQL Server 2012 SP2. Additionally, statistics update does not help with the problem.
Exec sp_create_plan_guideNote You have to replace the example query with the query that you are facing issue with. It can be grabbed from the Profiler trace, so no extra character is accidentally added. Additionally, make sure that the SELECT query is put in a single line (for example, it should not have any carriage return or line feed) so that the plan guide can work successfully. You may also have to use DBCC FREEPROCCACHE to knock off the old plan from cache.
@name = N'CDC_Query1_PlanGuide',
@stmt = N'select [__$start_lsn],[__$operation],[__$update_mask],[Column1],[Column2],[Column3]
from [cdc].[fn_cdc_get_net_changes_dbo_Table1](CONVERT(binary(10), @cs, 1), CONVERT(binary(10), @ce, 1), @mode)',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@ce nvarchar(22),@mode nvarchar(14),@cs nvarchar(22)',
@hints = N'OPTION (RECOMPILE)';
Article ID: 2989478 - Last Review: 17 Nov 2014 - Revision: 1