Database scoped optimizing for ad hoc workloads

First published on MSDN on Jan 04, 2018
SQL Server provides the “ optimize for ad hoc workloads ” server-scoped option that is used to reduce the memory footprint of single use ad hoc batches and associated plans.  When enabled at the SQL Server instance scope, the “optimize for ad hoc workloads” option stores a reduced-memory compiled plan stub on the first execution of an ad hoc batch for any database on the instance. This server option has been available in SQL Server for several years now, but until recently there hasn’t been a way to enable this option in Azure SQL Database for individual databases.

We are now introducing a new database scoped configuration called OPTIMIZE_FOR_AD_HOC_WORKLOADS which enables this behavior at the database scope in Azure SQL Database.

You can check the current state of this new database scoped configuration using the following query in the context of your database:
SELECT configuration_id, name, value
FROM sys.database_scoped_configurations
WHERE name = N'OPTIMIZE_FOR_AD_HOC_WORKLOADS';
The default configuration value is “0” (disabled).  When enabled, you’ll see a value of “1”.

To enable “optimize for ad hoc workloads” for your database, execute the following command within the context of the database:
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;
Note: As with any call to ALTER DATABASE SCOPED CONFIGURATION, executing this command will clear the procedure cache for entries of the current database.

To disable “optimize for ad hoc workloads” for your database, execute the following command:
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = OFF;
Plan stubs have a smaller memory footprint compared to the size of the full compiled plan. Below is an example of plan size differences in bytes for a single use ad hoc query:

Query	OPTIMIZE_FOR_AD_HOC_WORKLOADS OFF	OPTIMIZE_FOR_AD_HOC_WORKLOADS ON
SELECT ProductModelID, Summary FROM SalesLT.vProductModelCatalogDescription;	425,984 bytes	432 bytes

Once the database scoped configuration OPTIMIZE_FOR_AD_HOC_WORKLOADS is enabled for a database, a compiled plan stub will be stored in cache when a batch is compiled for the first time.  If a batch is compiled or executed again, the compiled plan stub will be removed and replaced with a full compiled plan.