David.Poole (1/31/2012)
Christoph Muthmann (1/31/2012)
Nice article, but I'm missing a hint to adhoc queries and their influence on the proc cache. You can save a lot of memory by using the optimize for ad hoc workloads Option.Good catch, wish I'd known this 2 years ago! I've passed this one on to my colleagues.
The worry now is that I'm going to hear "we don't have to fix this because there is a DB option".:crazy:
The only thing with that is you're still paying the cost to the query optimizer for each new compile. It's just taking less space in memory unless it's called again.
It's worth mentioning that you see the same capitalization behavior if you start using plan guides. It has to do with the fact that a hash of the query is created first and since there's potential for SQL to be capitalization sensitive it doesn't muck with capitalization before doing the hash.