Quick Scan Report – Optimize for Ad Hoc Workloads

Optimize for Ad Hoc Workloads in SQL Server is an important setting that controls how one time (ad-hoc) queries are used.

SQL Server includes numerous features to help manage workloads effectively, one of which is the Optimize for Ad Hoc Workloads option. This setting is particularly useful when your SQL Server environment frequently encounters single-use queries that lead to inefficiencies in the plan cache. Let’s explore what this feature is, when to use it, and how to enable it.

What is the “Optimize for Ad Hoc Workloads” Option?

In SQL Server, the plan cache stores execution plans for queries to avoid recompiling them every time they run. This improves performance for queries executed multiple times. However, when dealing with ad hoc workloads (queries executed only once), the plan cache can quickly fill up with single-use plans that consume memory unnecessarily.

The Optimize for Ad Hoc Workloads option changes how execution plans are stored. Instead of saving the full execution plan for the first execution, SQL Server saves only a small “stub” of the plan. If the query runs again, the full plan is compiled and cached. This reduces memory usage for workloads with many unique, one-time-use queries.

How Does It Help?

  1. Reduced Plan Cache Bloat: Without this setting, SQL Server may store thousands of single-use plans, consuming valuable memory that could be used for more frequently executed queries.
  2. Improved Memory Utilization: By storing only a stub for first-time ad hoc queries, the server uses less memory for plan caching.
  3. Better Overall Performance: Freed-up memory can be allocated to critical operations such as buffer cache or frequently used query plans.

When to Use It

This option is most beneficial if your workload has the following characteristics:

  • High Number of Single-Use Queries: Queries that are executed only once and never reused.
  • Dynamic Query Generation: Applications that generate a wide variety of ad hoc queries, such as ORM (Object-Relational Mapping) frameworks like Entity Framework or NHibernate.
  • Frequent Plan Cache Evictions: If you notice excessive churn in the plan cache, it could indicate ad hoc workload inefficiencies.

How to Enable Optimize for Ad Hoc Workloads

You can enable this feature at the instance level. Here’s how:

Using SQL Server Management Studio (SSMS)

  1. Open SSMS and connect to your SQL Server instance.
  2. Right-click the instance name and select Properties.
  3. Navigate to the Advanced tab.
  4. Under the Miscellaneous section, locate Optimize for Ad Hoc Workloads.
  5. Set the value to True.
  6. Click OK to save the changes.

Using T-SQL

You can also enable this option via a T-SQL script:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

This script enables the feature without needing to restart the SQL Server instance.

Monitoring the Impact

After enabling the setting, monitor your SQL Server instance to ensure it’s having the desired effect. Key metrics to track include:

  1. Plan Cache Hit Ratio: Monitor whether the overall performance of cached plans improves.
  2. Memory Usage: Observe reductions in memory consumption by the plan cache.
  3. Query Execution Times: Ensure there’s no noticeable performance degradation for frequently executed queries.

You can use tools like Database Health Monitor to analyze plan cache usage and performance metrics effectively.

Real-World Example

At Stedman Solutions, we encountered a client whose ORM-generated queries caused excessive plan cache usage. They were storing over 80% of their plan cache as single-use plans, leading to frequent evictions and poor memory utilization. By enabling Optimize for Ad Hoc Workloads, we reduced their plan cache size significantly and improved memory allocation for other tasks, boosting overall performance.

When Not to Use It

This setting may not be suitable if:

  • Queries Are Frequently Reused: For environments with repetitive query patterns, the overhead of compiling plans multiple times can outweigh the benefits.
  • Performance Drops Post-Change: Monitor closely; if your workload doesn’t match the intended use case, you might observe a performance decline.

Conclusion

The Optimize for Ad Hoc Workloads setting is a simple yet powerful way to enhance performance for workloads dominated by one-time-use queries. By reducing memory consumption in the plan cache, this feature allows SQL Server to allocate resources more efficiently.

If you’re unsure whether this setting is right for your SQL Server environment, Stedman Solutions can help. Our SQL Server Managed Services include performance tuning, monitoring, and expert recommendations tailored to your specific workload. Contact us at Stedman Solutions to optimize your SQL Server environment today.

TSQL

To view the current setting.

sp_configure 'show advanced options';
GO
sp_configure 'optimize for ad hoc workloads';
Optimize for Ad Hoc Workloads

To turn on optimize for ad hoc workloads.

sp_configure 'optimize for ad hoc workloads',1;
GO
reconfigure;
GO

Or you can just right click on the warning in the Database Health Monitor Quick Scan Report and turn it on.

Turning it off.

sp_configure 'optimize for ad hoc workloads',0;
GO
reconfigure;
GO


To examine the plan cache details.

--To see all object in the plan cache cache
SELECT [objtype],
	   [usecounts],
       [cacheobjtype],
	   [size_in_bytes],
	   [text]
  FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE [usecounts] = 1
  and LOWER(objtype) = 'adhoc';



To compare the total space used between single use and multi-use adhoc.

SELECT SUM(CASE WHEN [usecounts] = 1 THEN [size_in_bytes]
      ELSE 0 END) / 1024.0 / 1024.0 as singleUseAdHocMB,
       SUM(CASE WHEN [usecounts] = 1 THEN 0
      ELSE [size_in_bytes] END) / 1024.0 / 1024.0 as multiUseAdHocMB
  FROM sys.dm_exec_cached_plans
 CROSS APPLY sys.dm_exec_sql_text(plan_handle)
 WHERE LOWER(objtype) = 'adhoc';

Should I turn this on?
Run the following query and look at the results.

WITH adHocCTE AS
(
 SELECT SUM(CASE WHEN [usecounts] = 1 THEN [size_in_bytes]
 ELSE 0
 END) / 1024.0 / 1024.0 as singleUseAdHocMB,
 SUM(CASE WHEN [usecounts] = 1 THEN 0
 ELSE [size_in_bytes]
 END) / 1024.0 / 1024.0 as multiUseAdHocMB
 FROM sys.dm_exec_cached_plans
 CROSS APPLY sys.dm_exec_sql_text(plan_handle)
 WHERE LOWER(objtype) = 'adhoc'
)
SELECT singleUseAdHocMB, multiUseAdHocMB,
 singleUseAdHocMB * 100.00 /
 (singleUseAdHocMB + multiUseAdHocMB) AS adHocPercent
FROM adHocCTE;

If the adHocPercentage is greater than 20% or the singleUseAdHocMB is > 100 then it is probably worth turning this option on, however it really depends on your specific environment.

Reasons to not use Optimize for Ad Hoc Workloads.

Tools such as Database Health, or others that query the plan cache to see what queries have been run.
If a majority of your ad hoc queries are alway run exactly twice, those queries may be slightly slower on their second run due to the recompile of the plan.