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


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

Itís the Database. Or Is It? Put an End to Finger Pointing!

Free MSSQLTips Webinar: Itís the Database. Or Is It? Put an End to Finger Pointing!

During this webinar, we'll show how SolarWinds Server & Application Monitor (SAM) can enable your IT team to monitor the health and availability of your Microsoft SQL Servers. SAM in the hands of your IT teams provides broad visibility across the stack to easily identify the underlying cause for database availability and performance problems.


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:

single

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:

number

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:



Last Updated: 2018-02-01


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand 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.

View all my tips
Related Resources





Comments For This Article




Tuesday, February 20, 2018 - 6:01:34 AM - Nisarg Shah Back To Top

Hi Aaron, I guess both are correct. Looks like 'ALTER DATEBASE SCOPED CONFIGURATION' is only available from SQL Server 2016 onward (Ref: https://docs.microsoft.com/en-us/sql/database-engine/whats-new-in-sql-server-2016#scopedconfiguration). The alternative used to be "SP_CONFIGURE" (https://blogs.technet.microsoft.com/josebda/2009/03/19/sql-server-2008-optimize-for-ad-hoc-workloads/) - which is the one I had used earlier.

So, it might be that using "SP_CONFIGURE" won't clear the plan cache, and the newer option will. Makes sense?


Sunday, February 18, 2018 - 2:09:12 PM - Aaron Bertrand Back To Top

Nisarg, now I'm not sure who to believe. Joe Sack's introductory blog post, https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/01/04/database-scoped-optimizing-for-ad-hoc-workloads/, clearly states:

Note: As with any call to ALTER DATABASE SCOPED CONFIGURATION, executing this command will clear the procedure cache for entries of the current database.   


Sunday, February 18, 2018 - 9:24:19 AM - Nisarg Shah Back To Top

Thanks for the post!

Are you sure about this sentence?

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

Because on Microsoft's documentation notes:

Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected. To affect already cached query plans immediately, the plan cache needs to be cleared using ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, or SQL Server has to restart.

Ref: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option 



download


Recommended Reading

Different Ways to Flush or Clear SQL Server Cache

Fastest way to Delete Large Number of Records in SQL Server

UPDATE Statement Performance in SQL Server

How to find out how much CPU a SQL Server process is really using

SQL Server stored procedure runs fast in SSMS and slow in application





get free sql tips
agree to terms


Learn more about SQL Server tools