Problem solve Get help with specific problems with your technologies, process and projects.

Forced parameterization database option

In this expert response, expert Adam Machanic discusses a new SQL Server 2005 setting called forced parameterization.

What does the forced parameterization database option do?
SQL Server's Query Optimizer has the ability to auto parameterize certain types of queries in order to prevent recompilation. If a predicate uses a literal instead of a parameter, SQL Server can internally optimize the query as if the literal were a parameter, thereby producing a reusable execution plan. However, SQL Server's default simple parameterization can only auto-parameterize certain types of queries. Complex queries may not be parameterized, and therefore the database may be doing a lot more work than necessary recompiling query plans. This is especially prevalent in systems that make much use of ad-hoc T-SQL.

In order to help with this problem, SQL Server 2005 offers a new database setting called forced parameterization. This setting enables a more robust form of auto parameterization that can parameterize a much larger variety of queries, thereby reducing the potential for recompilation if similar queries are repeatedly used. This feature should only be enabled if the number of recompilations in a database is especially high, even though many similar queries are being executed. Forced parameterization has a higher overhead than simple parameterization, and so may cause performance problems rather than fixing them if used in the wrong circumstances.

This feature can be enabled by using ALTER DATABASE and setting the PARAMETERIZATION option to FORCED. The option can be set back to SIMPLE in order to return to the default behavior.

Dig Deeper on Microsoft SQL Server 2005

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.