Benefits of Optimize for Ad hoc Workloads

Anytime you run a query, SQL Server needs to build an execution plan to use as directions for best executing that query. These execution plans can be stored in your plan cache to be reused in the future if that same query is ran. Instead of resources going into rebuilding the plan each time, SQL Server can use the same plan as the previous execution. This is great for queries that run over and over. On the other hand, what if you have a large number of queries that will run once but never again? Plans never to be reused are taking up valuable space in plan cache. If this looks like your workload, consider enabling the Optimize for Ad hoc Workloads feature.

Enabling Optimize for Ad Hoc Workloads

Let’s look at an example. First, we’ll turn on Optimize for Ad Hoc Workloads.

sp_configure 'show advanced options', 1
GO
reconfigure
GO
sp_configure 'optimize for ad hoc workloads', 1
GO
reconfigure
GO

If you’d rather enable this option in the SSMS GUI, you can right click on your server in Object Explorer and find Optimize for Ad hoc Workloads on the Advanced Page:

Here’s the Plan

We’ll be using this test data again from a previous blog:

CREATE TABLE BrownsContracts
(
    Id INT IDENTITY(1,1) NOT NULL,
    Player VARCHAR(100) NOT NULL,
    ContractYear SMALLINT NOT NULL,
    YearlyCash INT NOT NULL
);
GO

INSERT INTO BrownsContracts
VALUES ('Baker Mayfield',2018,22329440),
    ('Myles Garrett',2018,1847375),
    ('Baker Mayfield',2019,24326411),
    ('Myles Garrett',2019,2105605),
    ('Baker Mayfield',2020,27941905),
    ('Myles Garrett',2020,21914442),
    ('Baker Mayfield',2021,33048675),
    ('Myles Garrett',2021,21684000),
    ('Baker Mayfield',2022,51906675),
    ('Myles Garrett',2022,19000000);

We’ll run the following query to show Baker Mayfield’s contract information:

DECLARE @Player varchar(100) = 'Baker Mayfield';

SELECT ContractYear, YearlyCash
FROM BrownsContracts
WHERE Player = @Player;
GO

Next, we’ll check our plan cache with the following query and see what we find:

SELECT usecounts,cacheobjtype,objtype,size_in_bytes,text,query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text like 'DECLARE @Player%';
GO

For cacheobjtype, we see what we have in the plan cache is the Compiled Plan Stub. The size_in_bytes value is only 456. Keep that number in mind. We also do not have a query plan yet. That value is NULL. What if we run our first SELECT query against the BrownsContracts table again? If we run that query and check our plan cache, we see some changes:

Our cacheobjtype is now Compiled Plan because the plan stub was recognized on the second execution. That leads us to having the query_plan stored and this entry in the plan cache having a size_in_bytes value of 49152 compared to 456.

Running the query a third time we can see the compiled plan get reused based on the usecounts of 2:

Remember that the query text has to be identical for the plan to be recognized and reused. For instance, running the same SELECT query except for the GO will restart the whole plan stub to compiled plan process for that specific query:

Is This Right For You?

Optimize for Ad Hoc is one option to consider when managing plan cache. It can help memory utilization by allowing more room for plans that get reused. If you don’t have many ad hoc queries on your server then you may be better off not using this option because the compiled plan would be stored after the first run and could be used on the second run. One easy way to check the amount of ad hoc plans in your plan cache is to run this query from Pinal Dave:

SELECT AdHoc_Plan_MB, Total_Cache_MB,
        AdHoc_Plan_MB*100.0 / Total_Cache_MB AS 'AdHoc %'
FROM (
SELECT SUM(CASE
            WHEN objtype = 'adhoc'
            THEN size_in_bytes
            ELSE 0 END) / 1048576.0 AdHoc_Plan_MB,
        SUM(size_in_bytes) / 1048576.0 Total_Cache_MB
FROM sys.dm_exec_cached_plans) T

I’ve seen more cases where Optimize for Ad Hoc been beneficial than not but it’s always better to test before enabling it in production.

Thanks for reading!