Tuesday, April 12, 2011

Forced Parameterization

Forced Parameterization is a fabulous tool for those DBAs fighting all of the ad-hoc coming from the middle tier.  Those developers continually insisting that it wasn’t them…  ;-).

Forced parameterization is one of the best on the list of SQL Server improvements, in my opinion.  It became available in v2005, and it allows SQL to take advantage of query plan reuse, and reduce the overhead required by the compilation of repeat query executions.  For cases with high query compilations, this option can help response time and performance within your applications.  It is very useful in systems with a lot of ad-hoc (non-procedural executions), and it can reduce the memory required by the procedure cache.

I, personally, have seen improvements greater than 28%, simply due to this option.  It allows queries to be parameterized, and cached for reuse.  It practically proceduralizes them!  This reduces the overhead typically used by these queries, by replacing the literal values with parameters. 

ALTER DATABASE databasename SET PARAMETERIZATION FORCED;

Forced Parameterization can be enabled with the above statement, and also manually, within SSMS\Database Properties\Miscellaneous – Parameterization.  It is typically enabled at the database level, but it can also be enabled it within individual queries, rather than the entire database.  You would do this by setting a plan guide for the query you are targeting. 

I won’t do it justice, so please take a look at Forced Parameterization in Books Online, and let me know what you think:      

      http://technet.microsoft.com/en-us/library/ms175037.aspx

No comments:

Post a Comment