Azure SQL Database – Optimizing For Ad-hoc Workloads

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)

highsizebyte

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)

lowermem

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

 

One thought on “Azure SQL Database – Optimizing For Ad-hoc Workloads

  1. Pingback: Azure SQL Database – Optimizing For Ad-hoc Workloads - SSWUG.ORG

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s