Optimize for Ad Hoc Workload in SQL Server

Adam Fulton
data codes through eyeglasses

When a query is executed, it compiles and generates an execution plan and stores it in the plan cache. If this is a one time query that will never be run again, then the newly generated plan and space in plan cache are wasted. This means the buffer pool now contains a compiled plan of queries that will never be executed.

Imagine if you have hundreds of such ad-hoc queries, you are occupying the buffer pool with plans that will never be used. To prevent this, SQL Server allows you to configure a server level setting called Optimize for Ad Hoc Workload which helps address this issue. This setting improves the efficiency of plan cache for one-off or ad hoc queries or batches. Once you enable this option, it changes the behavior of storing the compiled plan in the plan cache. Enabling Optimize for Ad-Hoc Workload setting prevents the buffer pool from occupying space for unwanted plans that will never be used.

SQLOPS script to help identify if you can benefit from “Optimize for Ad Hoc Workload”

Note: Our Risk and Health Audit covers this in great detail.
--This SQLOPS script allows you to check if your server workload
--can benefit from "Optimize for Ad Hoc Workload" Server Setting.
--Script provided as sample by https://sqlops.com
SET NOCOUNT ON
DECLARE
@AdHocWorkloadSize decimal (14,2),
@TotalSizeInMB decimal (14,2),
@AdHocSetting Varchar(20)
SELECT @AdHocWorkloadSize =
SUM(CAST((CASE
WHEN usecounts = 1 AND LOWER(objtype) = 'adhoc'
THEN size_in_bytes
ELSE 0 END) as decimal(14,2))) / 1048576,
@TotalSizeInMB = SUM (CAST (size_in_bytes as decimal (14,2))) / 1048576
FROM sys.dm_exec_cached_plans
IF (@AdHocWorkloadSize > 200) or
((@AdHocWorkloadSize / @TotalSizeInMB) * 100) > 25
Select @AdHocSetting='ENABLE'
Else
Select @AdHocSetting='DO NOT ENABLE'
Select @AdHocSetting as Recommendation,
@AdHocWorkloadSize as [Single_Plan_Memory_Usage],
@TotalSizeInMB as [Cache Plan Size_MB],
CAST((@AdHocWorkloadSize / @TotalSizeInMB) * 100 as decimal(14,2))
as [%_of_Single_Plan_Cache_Used]
GO

Internally with Optimize for Ad Hoc Workload setting, when the query is executed the first time, the database engine stores query hash in the plan cache which is very small in size compared to the compiled plan. However, the second time the same query is executed, the database engine checks and recognizes that the query hash exists for the ad-hoc workload and it goes ahead with creating a full compiled plan for that query and stores it in the plan cache by removing the query hash. All subsequent execution of this query will use the plan from the plan cache.

Configure Optimize for Ad Hoc Workload setting using SSMS

You can also configure Optimize for Ad Hoc Workload settings using SQL Server Management Studio by right-clicking on the server and selecting Server Properties and then selecting Advanced page:

Optimize for Ad Hoc Workload

Keep the following points in mind about Optimize for Ad Hoc Workload setting:

1. You should enable this option when your server has more one-off or Ad-Hoc query requests.
2. This prevents plan cache pollution by ad-hoc workloads whose plans will never be used again.
3. Since it stores the complete plan during the second run, expect a small delay during the second execution of the query/batch as the engine will need to generate a full plan, remove query hash value from the planned cache and store the full new plan in the plan cache.
4. Enabling this option helps save plan cache memory in the buffer pool.

Credits: https://sqlops.com/sql-server-best-practices/

Explore our range of trailblazer services

Risk and Health Audit

Get 360 degree view in to the health of your production Databases with actionable intelligence and readiness for government compliance including HIPAA, SOX, GDPR, PCI, ETC. with 100% money-back guarantee.

DBA Services

The MOST ADVANCED database management service that help manage, maintain & support your production database 24×7 with highest ROI so you can focus on more important things for your business

Cloud Migration

With more than 20 Petabytes of data migration experience to both AWS and Azure cloud, we help migrate your databases to various databases in the cloud including RDS, Aurora, Snowflake, Azure SQL, Etc.

Data Integration

Whether you have unstructured, semi-structured or structured data, we help build pipelines that extract, transform, clean, validate and load it into data warehouse or data lakes or in any databases.

Data Analytics

We help transform your organizations data into powerful,  stunning, light-weight  and meaningful reports using PowerBI or Tableau to help you with making fast and accurate business decisions.

Govt Compliance

Does your business use PII information? We provide detailed and the most advanced risk assessment for your business data related to HIPAA, SOX, PCI, GDPR and several other Govt. compliance regulations.

You May Also Like…