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…

You can follow any responses to this post through the RSS 2.0 feed. You can leave a response, or trackback from your own site.