Browse: Wetory / Ideas / SQL Server / Configuration / Optimize for Ad-Hoc Workloads

Optimize for Ad-Hoc Workloads

by Tomáš RybnickýNo Comments Reading Time: 3 minutes

SQL Server is using part of its memory for storing execution plans of the queries that can be used repeatedly without recompilation. Every compilation of execution plan has its cost including system resources mostly CPU. So is it saving time by caching execution plans for their reuse, this part of memory is called procedure cache. More info about this mechanism can be found in technet article Execution Plan Caching and Reuse.

Now lets consider situation when there are many queries running over SQL Server database. Most of these queries are totally different each other and running not too often. Anyway SQL Server need to store its compiled execution plan in cache having its running costs and use some piece of memory.

Running costs are not something that SQL Server can’t handle easily, but lets imagine that we have tons of queries, concurrency etc. Then we have a lot of running costs for storing execution plans that won’t be used again and using bytes in memory that could be used for something beneficial.

 

Check what plans we have in cache #

Run below query to check what type of queries are using our query execution plan cache. Query will gather aggregated data from dynamic management view sys.dm_exec_cached_plans and grouped by object type. Object types described in list below:

  • Proc: Stored procedure
  • Prepared: Prepared statement
  • Adhoc: Ad hoc query. Refers to Transact-SQL submitted as language events by using osql or sqlcmd instead of as remote procedure calls.
  • ReplProc: Replication-filter-procedure
  • Trigger: Trigger
  • View: View
  • Default: Default
  • UsrTab: User table
  • SysTab: System table
  • Check: CHECK constraint
  • Rule: Rule

 

SELECT 
    S.CacheType, S.Avg_Use, S.Avg_Multi_Use,
    S.Total_Plan_3orMore_Use, S.Total_Plan_2_Use, S.Total_Plan_1_Use, S.Total_Plan,
    CAST( (S.Total_Plan_1_Use * 1.0 / S.Total_Plan) as Decimal(18,2) ) AS [Pct_Plan_1_Use],
    S.Total_MB_1_Use, S.Total_MB,
    CAST( (S.Total_MB_1_Use   * 1.0 / S.Total_MB  ) as Decimal(18,2) ) AS [Pct_MB_1_Use]
FROM (
    SELECT 
        CP.objtype AS [CacheType],
        COUNT(*) AS [Total_Plan],
        SUM(CASE WHEN CP.usecounts > 2 THEN 1 ELSE 0 END) AS [Total_Plan_3orMore_Use],
        SUM(CASE WHEN CP.usecounts = 2 THEN 1 ELSE 0 END) AS [Total_Plan_2_Use],
        SUM(CASE WHEN CP.usecounts = 1 THEN 1 ELSE 0 END) AS [Total_Plan_1_Use], 
        CAST((SUM(CP.size_in_bytes * 1.0) / 1024 / 1024) AS Decimal(12,2) )[Total_MB],
        CAST((SUM(CASE WHEN CP.usecounts = 1 THEN (CP.size_in_bytes * 1.0) ELSE 0 END) / 1024 / 1024) AS Decimal(18,2) ) AS [Total_MB_1_Use],
        CAST(AVG(CP.usecounts * 1.0) as Decimal(12,2)) AS [Avg_Use],
        CAST(AVG(CASE WHEN CP.usecounts > 1 THEN (CP.usecounts * 1.0) ELSE NULL END) as Decimal(12,2)) AS [Avg_Multi_Use]
    FROM sys.dm_exec_cached_plans AS CP
    GROUP BY CP.objtype
) AS S
ORDER BY S.CacheType

Above query returns on my test instance running locally on my laptop following results. We can see what object types are cached and their usage. This is starting point of making decision if we can earn some plus points by using server setting Optimize for Ad-Hoc workloads set to true.

 

To see exact compiled execution plans for queries and its usage statistics can be checked using following query

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

Results containing count of uses and size in bytes used by this plan, and query text itself

 

Change server configuration #

Using SQL Server Management Studio:

You can check this setting in server properties using SQL Server Management Studio in tab Advanced in Miscellaneous section.

Using T-SQL:

SP_CONFIGURE 'Show Advanced Options', 1
GO
RECONFIGURE
GO
SP_CONFIGURE 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO

 

Database scoped configuration #

Microsoft introduced Database Scoped Configuration since SQL Server 2016. This brings many advantages of setting configuration options for individual databases rather than affecting whole instance. One of them are MAXDOP, Cardinality Estimation, clean plan cache and more (maybe in separate post).

One interesting option unfortunately now available only for Azure SQL Database is OPTIMIZE_FOR_AD_HOC_WORKLOADS. As you guess you can enable this just for one database without affecting whole instance behavior.  Note that turning this option on or off will clear the plan cache for the database.

You can enable it by running following query against the database you want to alter.

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

 

Hopefully we can get this option available also for on-premise SQL Server databases as well as others that are now available on for Azure SQL DB. You can check Database Scoped Configurations by querying sys.database_scoped_configurations system view.

SELECT * FROM sys.database_scoped_configurations

 

You can also find some available configurations in database properties using Management Studio under Options tab

 

Summary #

Keep in mind that this configuration will definitely not solve all your performance issues. Modifying it require complex view of your workload and data. And of course test on non-production environment first. I wish you good luck on the way to better SQL Server performance.

 

 

 

Leave a Reply