If you've read many of my blog posts, you know
that I consider lack of procedure cache control to be a major SQL Server pain point. Badly written apps
that use non-parameterized ad hoc queries can quickly flood SQL Server's memory
pools and bring the server to its knees.
SQL Server 2005 brought some relief in the form of
the Forced Parameterization database option, and SP2 took things one step
further with better throttling of the cache... but it's still not enough. We
want a knob!
The bad news: We're not getting quite the knob I
was hoping for.
The good news: SQL Server 2008 will include an
sp_configure option called "optimize for ad hoc workloads". This option
will cause the procedure cache to only cache the parameterized stubs for ad hoc
queries, rather than the full query with parameters. This means that
applications passing a large number of non-parameterized batches should see much
lower procedure cache memory utilization and, therefore, better overall
throughput. I'm really looking forward to seeing this in action; this feature
should be added with the next pre-release drop.
Remember, there is simply no substitute for
properly designing your application's data access layer, but hopefully this will
help for those applications that simply can't be changed...