Share via

Optimize for Ad hoc Workloads

Vishu 1,556 Reputation points
2021-06-14T03:51:45.593+00:00

Is setting Optimize for Ad hoc Workloads to true for sql 2017/2019 a best practise.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,670 questions
0 comments
Accepted answer
  1. CarrinWu-MSFT 6,871 Reputation points
    2021-06-14T09:16:36.713+00:00

    Hi @Vishu ,

    Welcome to Microsoft Q&A!

    Before you enable Optimize for Ad hoc Workloads, you should test it first. Please refer to this thread, see below:
    105361-2.png

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 44,296 Reputation points
    2021-06-14T06:27:12.347+00:00

    Is setting Optimize for Ad hoc Workloads to true for sql 2017/2019 a best practise.

    If it would, then the option would be set to "True" by default; which isn't.
    So it depends on the workload of your SQL Server if you should set it or not.
    See optimize for ad hoc workloads Server Configuration Option for more details

    0 comments
  2. Erland Sommarskog 110.3K Reputation points
    2021-06-14T22:06:20.843+00:00

    Yes, it is generally best practice to turn on this option. The option is kind of band-aid for poorly written applications, but your workload would have to be quite special to suffer from it.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.