Wednesday, September 17, 2008

Forced Parameterization

SQL Server's query optimizer can make use of cached plans if a query is submitted that already has one.
The random nature of adhoc SQL against a database makes it difficult to reuse a plan however as similar adhoc tsql queries just dont look the same.
Turning Parameterization to 'forced' tries to improve this and alledgedly makes a difference for high volume systems.
Am currently trialing it out...

-- change parameterization to 'forced'
ALTER DATABASE databasename SET PARAMETERIZATION FORCED
GO

-- reverse the setting
ALTER DATABASE databasename SET PARAMETERIZATION Simple
GO

No comments: