SQL 2005's new "Forced Parameterization" feature
I was discussing the problems associated with managing the performance of SQL Server based vendor software systems with Kalen Delaney whilst she was teaching in Melbourne a couple of weeks ago & she pointed out a new SQL Server 2005 feature to me which I wasn't previously aware of - "Forced Parameterization".
This feature allows you to gain better procedure cache re-use by forcing parameterization of queries, even when they're submitted by applications as SQL statements without any declarative form of parameterization (ie, stored procedure or sp_executesql).
In case you're not aware already, applications that submit SQL statements on an "ad-hoc" basis (not via stored procs or sp_executesql) can bloat a SQL Server's procedure cache because every individual statement needs to be compiled & CACHED when submitted by the application, even if the only difference between the statements being submitted are the constants in the where clause. The further the proc cache bloats, the less memory is left for data caching (the primary purpose for memory in a database server), which in turn leads to IOs that otherwise might have been serviced by the data cache being pushed back to disk, slowing things down considerably.
If you configure the database Parameterization property to FORCED (using ALTER DATABASE...), SQL Server forces parameterization for EVERY SQL command submitted, leading to far greater procedure cache efficiency (re-use & overall reduced size). There are a few rules & exceptions, but generally speaking, most real-world statements should end up parameterized.
This should usually yield a positive overall server performance improvement but, for completeness, it should also be pointed out that in some scenarios, re-compiling every statement based on its embedded parameters could actually be better than forcing parameterization on every statement. Eg, a specific query might qualify significantly more or less rows, based on the parameter provided. In this case, it might be better to re-compile the statement for every individual parameter provided (the default behaviour).
The reason why I say that this feature is so useful when managing vendor apps in particular is that you usually can't change the way that vendor apps submit SQL the way you can with an internal app. For example, if you find that an internal app is bloating procedure cache, you can usually tap the developers on the shoulder & request that they change to using stored procs. Dealing with vendors is obviously a lot harder because you have less influence over their willingness to change an app. They (perhaps rightly) usually look at such requests in terms of how much testing will be involved in making such changes, even though it will mean their app will perform better. This new feature allows you to force the parameterization of queries, regardless of how the vendor has implemented their data access layer.
Forced Parameterization is set at the database level (again, via the ALTER DATABSE command), which allows you to configure different databases on a SQL Server instances seperately (nice).
We have a few customers at work who use vendor apps, a couple of which really suffer from bloated procedure caches, so hopefully I'll have the opportunity to trial this out in the real world & post back some measured experiences soon..