August 11, 2010 at 5:13 am
Hi,
Is there any way we can limit the total amount of plan cache used by ad_hoc and prepared query plans?
If there are too many ad_hoc, prepared queries eating up plan cache, is there any kind of advanced memory option that we can use to restrict the amount of memory used by just the ad_hoc, prepared query plans.
Thanks.
August 11, 2010 at 5:39 am
Nope. There is no way to control the cache in that manner. The one thing you can do, and I think this is available in 2005, is set the system option "optimize for ad hoc workloads" to true. SQL Server will then store plan stubs instead of entire plans. It helps.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 11, 2010 at 5:58 am
Grant Fritchey (8/11/2010)
I think this is available in 2005, is set the system option "optimize for ad hoc workloads" to true.
2008 feature, along with the two options (access check cache bucket count, access check cache quota) that control the token store.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2010 at 6:08 am
GilaMonster (8/11/2010)
2008 feature, along with the two options (access check cache bucket count, access check cache quota) that control the token store.
Nuts. Thanks. I wasn't sure when it was introduced.
It does help with ad hoc cache management. It's the only thing I've found that does.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply