Optimizing a query with Forced Parameterization

Enabling Forced Parameterization

-- enable forced mode
ALTER DATABASE YOUR_DATABASE SET PARAMETERIZATION FORCED

-- enable simple mode
ALTER DATABASE YOUR_DATABASE SET PARAMETERIZATION SIMPLE

You can also use the Management Console and the properties option
under the relevant database to switch the setting over manually.
When switching from one mode to another I made sure to clear the
current cache by running the following:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE


To check the queries that were currently cached and the
type of method being engaged.
SELECT *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
INNER JOIN sys.dm_exec_cached_plans as cp 
on qs.plan_handle=cp.plan_handle
WHERE cp.plan_handle=qs.plan_handle
AND (ObjType = 'Adhoc' OR ObjType='Prepared')


Enabling Forced Parameterization for a single query
There maybe situations where you want to enable this
feature for one or more queries rather than set it
on for the whole database. You can do this by
setting up a plan guide for the query in question.
An easy way to do this is to run the following SQL 
making sure to replace the first parameter value with
the SQL you want to parameterize.


DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N'SELECT * FROM JOBS WHERE Row between 1 AND 20;',
@stmt OUTPUT,
@params OUTPUT;
EXEC sp_create_plan_guide
N'MyPlanGuide_JobResults',
@stmt,
N'TEMPLATE',
NULL,
@params,
N'OPTION(PARAMETERIZATION FORCED)';