THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

SQL Server Procedure Cache: More Relief on the Way

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...

Published Tuesday, April 15, 2008 9:26 PM by Adam Machanic

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Andrew Kelly said:

Yup, they finally listened to us after all these years of "discussing" the topic:).

April 16, 2008 11:42 AM

Gail Shaw said:

That's good to hear. Do you know if there's any similar fix for the token store?

May 2, 2008 3:23 PM

Ranga Narasimhan said:

We had this issue and almost brought down the server to its knees. Using sp_executesql with parameterization is good, but in our case, the sql statement itself was built based on the stored proc paremeters. Based on the sort condition, the order by clause differs. In this case even parameterization will not work I think. Is this considered adhoc query or not, since it comes from a stored procedure. As usual developers still think that dynamic sql is the only way to go in this part of the application where user options changes the stored proc query.

November 10, 2010 2:37 PM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement