PASS Summit 2011 - Zen and the Art of Workspace Memory - Demos

Adam Machanic — Mon, 17 Oct 2011 00:23:00 GMT

What a rush . Standing on the stage in an almost-full 1,000-person room, I (very) momentarily wondered what I'd been thinking when I submitted a 500-level talk for the biggest SQL Server conference in the world. But despite a rough start--my laptop crashed...(read more)

SQL Server Procedure Cache: More Relief on the Way

Adam Machanic — Wed, 16 Apr 2008 00:26:00 GMT

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

Adam Machanic : memory

PASS Summit 2011 - Zen and the Art of Workspace Memory - Demos

SQL Server Procedure Cache: More Relief on the Way