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
Related Q&A from Adam Machanic
Migrating to SQL Server 2005 from SQL Server 2000 is a hefty feat when compared to upgrading from 7.0 to SQL Server 2000. Site expert Adam Machanic ... Continue Reading
Multiple readers can sometimes read the same row simultaneously causing a false result. SQL Server 2005 expert Adam Machanic suggests modifying the ... Continue Reading
SQL Server 2005 and T-SQL do have differences. The advantages and disadvantages would depend user environment as described by SQL Server 2005 expert... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.