Optimize for ad hoc workloads - at the database level

By: Aaron Bertrand | Updated: 2018-02-01 | Comments (3) | Related: > Performance Tuning

Problem

Plan cache bloat in SQL Server has been a problem since, well, SQL Server has had a plan cache. The engine tries to make intelligent decisions about which plans to keep in the cache based on usage and complexity, but it is limited by the amount of memory available, and sometimes we just throw too much at it.

One case is single-use plans. SQL Server goes to the trouble of compiling and caching an execution plan, and it ends up only ever being used once. This is a waste of memory, but is unavoidable in some workloads (especially some ORM patterns). Kimberly Tripp goes into great detail about this here, and many of us in the industry generally recommend the instance-level configuration option, “optimize for ad hoc workloads.” The high-level overview of how this option works is simply that a plan “stub” is cached the first time, and a full plan isn’t cached until it has been requested a second time. Effectively, we are trading an additional (hopefully small) compilation cost for a substantial memory saving.

While I have yet to experience a downside to enabling this instance-level option, I don’t doubt that some may exist, and there are certainly cases where customers are afraid to turn it on for their entire server. They have asked for the ability to only set it for specific databases but, currently, SQL Server does not offer this option.

Solution

Microsoft’s Joe Sack recently announced a new database-level option that will do exactly what customers have been asking for. It is first being introduced in Azure SQL Database where, of course, the server-level setting is simply not possible. (Well, setting this at the instance level would be possible, in theory, but it would be for all databases or no databases, and this violates the Azure SQL DB model.)

Since the new option is database-specific, it is being implemented as part of the ALTER DATABASE SCOPED CONFIGURATION settings. The new option is called OPTIMIZE_FOR_AD_HOC_WORKLOADS, and is off by default. To enable it, you would run the following command:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

Note that turning this option on or off will clear the plan cache for the database.

As a quick example of the benefit here, we can come up with a quick proof of concept to show the size of the plan cache with and without the setting enabled.

First, let’s create a table with 100 columns:

DECLARE @sql nvarchar(max) = N'CREATE TABLE dbo.EmptyTable
(';
SELECT @sql += N'
col' + CONVERT(varchar(11), rn) + N' int,'
FROM
(
SELECT TOP (100) rn = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_columns
) AS x
ORDER BY rn;
SET @sql += N'
);';
EXEC sys.sp_executesql @sql;

Then we can create a fake workload where we have 100 single-use queries – each one referencing exactly one of the columns:

DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'
EXEC sys.sp_executesql N''DECLARE @x int; SELECT @x = col'
+ CONVERT(varchar(11), rn) + N' FROM dbo.EmptyTable WHERE col'
+ CONVERT(varchar(11), rn) + N' > 0;'';'
FROM
(
SELECT TOP (100) rn = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_columns
) AS x
ORDER BY rn;
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
EXEC sys.sp_executesql @sql;

Now, we can check the size of the plan cache, and how much is attributed to single-use plans:

;WITH cp AS
(
SELECT [single?] = CASE usecounts
WHEN 1 THEN 'yes' ELSE 'no' END, size_in_bytes
FROM sys.dm_exec_cached_plans
WHERE cacheobjtype = N'Compiled Plan'
)
SELECT [single?],
number = COUNT(*),
size_MB = SUM(size_in_bytes)/1024.0/1024
FROM cp
GROUP BY GROUPING SETS (([single?]), ());

The results under the default settings:

You might expect 100 exactly, but there are other things including the DMV queries that can throw this off.

Now, if we start over after setting OPTIMIZE_FOR_AD_HOC_WORKLOADS to ON, the results are different:

For such a simple workload, the savings are not quite 50%, but still significant. On a much bigger workload, with much more complex (and therefore larger) query plans, the impact could be astounding. Like any change, you’ll want to test and make sure this impacts your workload in a positive way, and be sure to plan the configuration change during a maintenance window, scheduled updates, or a period of low activity.

Joe has confirmed that this option will also make its way into on-premises editions of SQL Server, but as of yet there are no firm announcements of timelines. I would expect this will be delivered to SQL Server 2017 in a forthcoming Cumulative Update, and SQL Server 2016 either in Service Pack 2 or a post-SP2 CU. Since DATABASE SCOPED CONFIGURATION was introduced in SQL Server 2016, I don’t expect it to ever be back-ported to earlier versions.

Personally, I am looking forward to being able to recommend this option to customers, and also offer them the safety of testing it in isolation or only ever implementing it for a single database (or a subset). I almost always recommend users enable this setting, because the worst that could possibly happen is one additional compile after a restart, failover, or a plan aging out of the cache. For all the potential upside, it is totally worth it to me.

Next Steps

If you have access to Azure SQL Database, you can start playing with this feature right now. If not, you’ll have to wait until this feature makes it into on-prem editions. In the meantime, check out these tips and other resources on the plan cache and the optimize for ad hoc workloads setting:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-02-01

Optimize for ad hoc workloads - at the database level - in SQL Server

Problem

Solution

Next Steps

About the author

Comments For This Article