Optimize for ad hoc workloads - at the database level - in SQL Server
By: Aaron Bertrand | Updated: 2018-02-01 | Comments (3) | Related: More > Performance Tuning
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.
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.
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:
- Minimize SQL Server plan cache bloat
- Troubleshooting Excess Compilations in SQL Server Using the Plan Cache and PowerShell
- The SQL Server Performance Tuning Tips Category
- Plan cache and optimizing for adhoc workloads
- Plan cache, adhoc workloads and clearing the single-use plan cache bloat
- Database scoped optimizing for ad hoc workloads
Last Updated: 2018-02-01
About the author
View all my tips