Tips: Avoid single-use cached plan with ‘optimize for ad hoc workloads’ in the SQL Server 2008 or later

In SQL Server 2008, there is a new server configuration option introduced, called ‘optimize for ad hoc workloads’. This option can improve the efficiency of the plan cache for single use ad-hoc queries (or called dynamic SQL). When this option is on (set to 1, and 0 by default), SQL Server only stores a small compiled plan stub in the plan cache instead of the full compiled plan. When the same query executs next time, the database engine knows that this ad hoc query has compiled before based on the compiled plan stub in the plan cache, and then removes the compiled plan stub from the plan cache and stores the full compiled plan in the plan cache this time so that the plan can be reused in the following executions. For more informaiton, refer to BOL: http://msdn.microsoft.com/en-us/library/cc645587.aspx.

Here is a script to check the ‘optimize for ad hoc workloads’ option, and update it to 1 if required.

-- Check the total server memory currently used by buffer pool only
SELECT cntr_value / 1024 AS [Total Server Memory (MB)]
FROM	sys.dm_os_performance_counters 
WHERE object_name = 'SQLServer:Memory Manager' 
AND counter_name = 'Total Server Memory (KB)'

-- Cached plan usage statistics  (from kimberly)
SELECT objtype AS [CacheType]
        , COUNT_BIG(*)AS [Total Plans]
        , SUM(CAST(size_in_bytes AS DECIMAL(18,2)))/1024/1024 AS [Total MBs]
        , AVG(usecounts) AS [AVG USE COUNT]
        , SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE COUNT 1]
        , SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE COUNT 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE COUNT 1] DESC
GO 

-- Check the size of the total cached plans
SELECT SUM(CAST(size_in_bytes AS DECIMAL(18,2)))/1024/1024 AS [Total MBs]
FROM sys.dm_exec_cached_plans

-- Check the size of total cached plans that were used only once
SELECT SUM(CAST(size_in_bytes AS DECIMAL(18,2)))/1024/1024 AS [Total MBs of Single-user Plans]
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1

-- You can clean the whole plan cache since 'optimize for ad hoc workloads' 
-- only works for following queries
/*
DBCC FREEPROCCACHE;
GO
*/

-- Check the optimize for ad hoc workloads option, should be 1
SELECT	*
FROM sys.configurations
WHERE	name = 'optimize for ad hoc workloads';
GO

-- Update optimize for ad hoc workloads to 1 if it is 0
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE;
GO

/*
	References:
	http://msdn.microsoft.com/en-us/library/cc645587.aspx
	http://msdn.microsoft.com/en-us/library/ms187404.aspx
	http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx
	http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-from-cache.aspx
	http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/04/did-you-know-sp2-does-not-limit-the-amount-of-plan-cache-you-can-have.aspx
*/
Advertisements

About Alex Feng

SQL Server DBA in Alibaba Cloud Computing
This entry was posted in Database Engine, Script Library, Tips & Tricks and tagged , , . Bookmark the permalink.

One Response to Tips: Avoid single-use cached plan with ‘optimize for ad hoc workloads’ in the SQL Server 2008 or later

  1. I do accept as true with all the ideas you have presented for your post. They’re really convincing and will certainly work. Still, the posts are too quick for starters. May you please extend them a little from next time? Thank you for the post.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s