Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173

Today I am going to share another real-world scenario that I often encounter while working on   Comprehensive Database Performance Health Check at my client’s place. Today we are going to discuss if we should keep Optimize for Ad Hoc Workloads true or false. Let us see a detailed video about it.

Here is the blog post which discusses Optimize for Ad Hoc Workload setting in detail. SQL SERVER – AdHoc Queries and Optimize for Adhoc Workloads. Here is the video about Optimize for Ad Hoc Workloads.

Please make sure that you do not change any SQL Server configuration without doing proper testing. There are so many different settings and one needs to make sure that changing any one settings does not reduce the performance of your server overall. If you are confused with any settings, you can always reach out to me and I Will be happy to help.

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Ad Hoc Query, Execution Plan, SQL in Sixty Seconds, SQL Scripts, SQL Server, SQL Server Configuration
Previous Post
Avoid Join Hints – SQL in Sixty Seconds #172
Next Post
Index Scans – Good or Bad? – SQL in Sixty Seconds #174

Related Posts

5 Comments. Leave new

  • Pinal, thanks for your efforts to help inform people about this often-confusing feature. That said, can you please revisit your first post on it, https://blog.sqlauthority.com/2017/10/20/sql-server-turn-optimize-ad-hoc-workloads/ listed in the related posts here?

    You’d made a point there about enabling this when the adhoc cache was “20-30%” of the total. About half the comments there are people asking if higher numbers are better or worse, which was indeed unclear from the wording alone. The other half of the comments were about errors and suggested improvements to the calculation you’d offered.

    Sadly, there’s no response to any of them. Even just a few words could help there, and that would also help for whenever you highlight this topic like today where people may then find it that original post. Thanks.

    • Hi Charlie,

      Thanks for your comment. Performance tuning is a complex subject and I have shared one perspective. The error solution is proposed in the first comment.

      Additionally, my view is already listed in the blog post – If your adhoc plan cache is 20-30% of total Plan Cache, you should turn on the Optimize for Ad Hoc Workloads.

  • Pinal, again, the question (not clarified in that post or your comment here) is “what if the value is GREATER than 20-30%”? Since you’re repeated this, are you really meaning to leave it that you’re not comfortable proposing what people should do if their numbers or either higher or lower than that range? that you only want to make a recommendation for that SPECIFIC range? It seems odd, and I really feel like there’s a misunderstanding, but if you’ll confirm this is what you mean, it would at least help those left wondering.

    BTW, about the correction offered in the comments, I appreciate that readers were helping each other there. My point in asking about it here was under the hope that you may want to incorporate that change into the code you had offered (with a comment that you were updating it), so that people need not have to read the comments to avoid the error.

    As always, just trying to help.

    • Hi Charlie,

      The recommendation is pretty simple – If your adhoc plan cache is 20-30% of total Plan Cache, you should turn on the Optimize for Ad Hoc Workloads.

      If it is not in that range, leave it as it is.

    • Thanks Charlie, I appreciate your thoughts.


Leave a ReplyCancel reply

Exit mobile version