Hi Experts,
What is 'optimize for ad hoc workloads' do mean in short? I hae read it as it minimizes plan cache bloating. Do we really need to set this settig?
What is the benefit we get if we set 'optimize for ad hoc workloads' = 1.
How to verify if we really have to set this up on sql instance or leave it to default 0.
Environment:
SQL Server 2017 EE
Cheers,
Bob
It will avoind storing every sqlplan in the plan cache.
Queries that run only once do not polute your plan cache when this setting is on.
The article below holds all information:
ref: optimize for ad hoc workloads server configuration option
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 20, 2023 at 2:45 pm
For the first execution of a query, you only store a plan stub (basically a hash for the query, plan & settings affecting the plan) instead of a plan. That's it. That's the magic. Then, on the second execution where the hash values all match, the plan is stored since it seems more likely that the plan will be reused after more than one execution.
It's not an automatic performance enhancer or a magic "run faster" switch. It's merely a way, when you have a higher percentage of ad hoc queries, of helping to deal with plan cache bloat (caused by the plan cache being filled by single use plans). You may not see much, if any help at all, depending on your system. However, I've also never seen it hurt performance in a meaningful way. I recommend enabling it on servers unless proven otherwise.
"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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply