Today I found out that it is now possible to enable the setting optimize for ad-hoc workloads at the database level when using Azure SQL Database. Traditionally this was always set at the server level for locally based SQL Servers.

We would usually do something like:

 sp_configure 'optimize for ad hoc workloads',1
GO
reconfigure

Running the above within Azure you will get: Could not find stored procedure ‘sp_configure’.

Msg 40510, Level 16, State 1, Line 3 – Statement ‘CONFIG’ is not supported in this version of SQL Server.

So what do we do for Azure SQL Database? Within the context of your database you can now execute the following:

 ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON

Let’s see it in action. The idea behind this setting is to reduce the memory footprint for ad-hoc based queries. If most of your queries are ad-hoc and never re-used caching them can take up lots of memory especially if you have many queries. With optimizing for ad-hoc workloads setting on it will cache a stub of the plan which means less memory requirement.

So the setting is currently off and I run the following.

 /****** Script for Test sizes command from SSMS  ******/SELECT TOP (1000) [Id]
      ,[UserId]
      ,[Date]
      ,[TransactionCodeId]
      ,[DescriptionID]
      ,[Shard]
  FROM [dbo].[Audit]

I then want to check what is in cache.

SELECT usecounts,cacheobjtype,objtype,size_in_bytes,[text]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)

Note down the size in bytes. I will then enable the setting then run the same query and compare. (Cache gets cleared when running  ALTER DATABASE SCOPED CONFIGURATION command)

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON
  /****** Script for Test sizes command from SSMS  ******/SELECT TOP (1000) [Id]
      ,[UserId]
      ,[Date]
      ,[TransactionCodeId]
      ,[DescriptionID]
      ,[Shard]
  FROM [dbo].[Audit]
SELECT usecounts,cacheobjtype,objtype,size_in_bytes,[text]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)

As you can see it is now a Plan stub  with a lower memory requirement.