Parameterization

 The “Parameterization” property controls the way SQL Server handles literals in query predicates. In some cases, SQL Server might decide to replace a literal value with a parameter during query optimization. If your application sends ad-hoc queries to SQL Server, where the same query is executed many times with different literal values, then replacing the literal value with a parameter can significantly reduce the number of compilations in the system and the overhead associated with them. 

Under the “Simple” mode, SQL Server will only parameterize queries when it is certain that there is only one plan for the query regardless of the value of the literal being parameterized. In other words, it will parameterize only queries, which have a trivial plan. If you don’t know what a trivial plan is, then let me explain this concept as well. The query optimizer recognizes a class of very simple queries, for which only a single plan exists, and for which there is no reason to start the full optimization process. An example is a query on a single table with a single predicate on the clustered primary key. 

So under simple parameterization mode, SQL Server will only parameterize queries with a trivial plan. This is good, because there is no risk of hurting performance as a result of reusing the wrong plan. If the same plan is good for all literal values, then it makes no sense to compile the query again and again for each value. This is what SQL Server does by default. 

 

Changing the parameterization property for a database removes all the current plans associated with the database from the plan cache. Under the forced parameterization mode, SQL Server will parameterize every query regardless of its plan being trivial or full. You should be very careful when changing this property and test it thoroughly before you do it in production. 

But in some cases, forced parameterization can significantly improve the overall performance of the database. If your application uses a lot of ad hoc queries, which are compiled many times, and in most cases the same plan is generated again and again, then changing to forced parameterization can substantially reduce the resource consumption associated with excessive compilations and improve performance. 

The problem with this database property is that it affects the whole database. Forced parameterization might be a great solution for some queries, but in most cases this is not the best solution for all queries in the database.