How to Enable Optimize for Ad Hoc Workloads in SQL Server 2016

By | October 14, 2020

The option to enable Optimize for Ad Hoc Workloads in SQL Server has become one of our most recommended settings.  When enabled the Optimize for Ad Hoc Workloads, this setting will command the SQL Server to only store a snippet of the query plan each time a query is executed for the first time.

It is only when that snippet of a query plan is referenced a second time, the SQL Server will be able to store the entire query plan. Of course, this is very beneficial once you are considering the amount of space being consumed by single use query plans, particularly in large scale environments.

Based on the research, optimize for Ad Hoc Workloads was introduced in SQL Server 2008. So, if you are working in an older environment, you will not be able to use this feature. Similar to other things in the SQL Server, there are two ways you are able to enable the option to optimize for Ad Hoc Workloads in SQL Server. For note: This is an online server setting, so this is able to be enabled without disrupting services. Also, it is going to apply to all databases on the server.

ENABLING THE OPTIMIZE FOR AD HOC WORKLOADS IN SQL SERVER

As we said before, there are two different methods you are able to enable for Ad Hoc Workloads in SQL Server. Please look at the text below to see those ways!

METHOD 1: ENABLING THIS FEATURE THROUGH THE GUI IN SQL SERVER MANAGEMENT STUDIO.

  • If you want to enable optimize for Ad Hoc Workloads in SQL Server through the GUI in SQL Server Management Studio, when you are connected to the server, simply you are able to right-click the server and choose Properties.
connected to the server, simply you are able to right-click the server and choose Properties
  • On the Server Properties window, you have to choose the Advanced page.  Under the Miscellaneous grouping you are going to see the option for Optimize for Ad Hoc Workloads.
choose the Advanced page Under the Miscellaneous
  • Now, simply you are able to change this value from False to True and then click OK.
  • Finally, you have enabled the Optimize for Ad Hoc Workloads.

METHOD 2: ENABLING THIS FEATURE THROUGH SCRIPTING

Another option for enabling the optimize for Ad Hoc Workloads in SQL Server is through scripting. To perform this method, of course you will need to know the script. You do not worry about that as here we are going to help you.

In the text below, we share the script. For your information, the script below utilizes the SP_CONFIGURE command to perform the same task outlined above.

SP_CONFIGURE ‘Show Advanced Options’, 1

GO

RECONFIGURE

GO

SP_CONFIGURE ‘optimize for ad hoc workloads’, 1

GO

RECONFIGURE

GO

Honestly, we have yet to discover a situation where enabling Optimize for Ad Hoc Workloads is not helpful. We assume if you have a SQL Server which is dedicated to a  specific task where the queries are streamlined and do not alter frequently, so leaving this disabled may be suitable.

Actually, we are a little surprised that this setting is not enabled by default. However, it is such an easy setting to apply and will likely be something you will apply to your production SQL Server environments.

 OPTIMIZE FOR AD HOC WORKLOADS

Optimize for Ad Hoc Workloads is one of the server level settings which are not changed very frequently. However, it is still good to know about. Once using the SQL Server, it reserves a portion of memory for Plan Cache. The Optimize for Ad Hoc Workloads setting will control what SQL Server places into this plan cache for single use queries. When it is turned off, all single use queries are going to have the entire plan cached, so consuming more space.

Turning this on means that you are prompting the SQL Server to not store the entire plan once the query is executed for the first time. Right now the SQL Server will only store a plan stub instead and consumes less memory than the full plan would. Something that you have to note, the next time that the query is executed, it is going to flush the stub from the cache and replace it with the full plan.

According to the research, there are two methods you are able to use to determine if this setting is on or off. Actually, we have shared this in the previous page. To make you more understand, well we are going to explain it again. The first method is through the SQL Server Management Studio (SSMS).

In the Object Explorer, you are able to right click on the server and then click on the Properties. The default is False, meaning that the entire plan is going to be placed in cache once a query is compiled and executed. As with lots of things in SQL Server, you are also able to use SQL to get this information. You are going to need to query sys.Configurations for getting this setting via TSQL.

By the way, how to determine if there are many single use queries in cache? We think there are two measures we will need to understand when it comes to discovering what is consuming the cache. You need to know the amount of space consumed by Ad Hoc queries and the number of queries which are considered to be Ad Hoc queries. Let us see how lots of Ad Hoc query plans are in cache now.

Of course, there are the codes utilizing the dm_exec_cached_plans to find out how much space in cache is being consumed by Ad Hoc queries. For this case, you are able to search for the codes by yourself.

Well, this is an explanation regarding how to enable Optimize for Ad Hoc Workloads in SQL Server and some information related to the Optimize for Ad Hoc Workloads. If you want to enable Optimize for Ad Hoc Workloads in SQL Server, do not hesitate to follow those steps correctly.

Leave a Reply

Your email address will not be published. Required fields are marked *