I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one: http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
Why is there even an option to "optimize for ad hoc workloads"? Shouldn't this always be on? Whether the developers are using ad-hoc SQL or not, why would you not have this option enabled on every instance that supports it (SQL 2008+), thereby reducing cache bloat?
The SQL Server development team work on the principle of least surprise - so SQL Server generally has new features disabled in the interests of maintaining behaviour as previous versions.
Yes, optimize for adhoc workloads is great at reducing plan cache bloat - but always test it first!
Kalen Delaney tells an interesting anecdote that she asked one of her Microsoft engineer friends whether there would be circumstances where it would not be appropriate to enable this. He comes back several days later to say - imagine an application that has a LOT of different queries, and each query runs exactly twice in total. Then it might be inappropriate. Suffice to say there's not many apps like that!
If the majority of your queries are executed more than once (not exactly twice); it would likely be inappropriate. The general rule would be to turn it if there are many one-time-use adhoc queries on the database; however, there are still not many apps like that.
Below is a little code that will help you decide if "switching optimize for ad hoc workloads ON/OFF" will be beneficial or not. We normally check this as a part of our health check for in-house and client servers.
It is the safest option to enable and is described well by Brad here and by Glenn Berry here.
--- for 2008 and up .. Optimize ad-hoc for workload
IF EXISTS (
-- this is for 2008 and up
SELECT 1
FROM sys.configurations
WHERE NAME = 'optimize for ad hoc workloads'
)
BEGIN
DECLARE @AdHocSizeInMB DECIMAL(14, 2)
,@TotalSizeInMB DECIMAL(14, 2)
,@ObjType NVARCHAR(34)
SELECT @AdHocSizeInMB = SUM(CAST((
CASE
WHEN usecounts = 1
AND LOWER(objtype) = 'adhoc'
THEN size_in_bytes
ELSE 0
END
) AS DECIMAL(14, 2))) / 1048576
,@TotalSizeInMB = SUM(CAST(size_in_bytes AS DECIMAL(14, 2))) / 1048576
FROM sys.dm_exec_cached_plans
SELECT 'SQL Server Configuration' AS GROUP_TYPE
,' Total cache plan size (MB): ' + cast(@TotalSizeInMB AS VARCHAR(max)) + '. Current memory occupied by adhoc plans only used once (MB):' + cast(@AdHocSizeInMB AS VARCHAR(max)) + '. Percentage of total cache plan occupied by adhoc plans only used once :' + cast(CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 AS DECIMAL(14, 2)) AS VARCHAR(max)) + '%' + ' ' AS COMMENTS
,' ' + CASE
WHEN @AdHocSizeInMB > 200
OR ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25 -- 200MB or > 25%
THEN 'Switch on Optimize for ad hoc workloads as it will make a significant difference. Ref: http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/. http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx'
ELSE 'Setting Optimize for ad hoc workloads will make little difference !!'
END + ' ' AS RECOMMENDATIONS
END
When you turn the "Optimize for Ad Hoc Workloads" option on, you will cause ad-hoc queries that are run the 2nd time to be just as slow as the 1st, because you will be Compiling an Execution Plan and pulling the same Data (without it cached) those first 2 times.
This may not be a big deal, but you'll notice it when testing queries.
So what happens now, without this option turned on and a cache full of 1-Off Ad-Hoc queries?
As this Optimization feature was introduced, the caching management algorithm was also updated.
Kimberly Tripp's article also references Kalen Delaney's post about this algorithm change.
She explains it best:
The change actually computes a plan cache size at which SQL Server recognizes that there is memory pressure, and it will start removing plans from cache. The plans to be removed are the cheap plans that have not been reused, and this is a GOOD THING.
This means those pesky one-timer plans will be the first to go when you need to free up resources.
"Why do we NEED 'Optimize for Ad Hoc Workloads' when SQL Server takes care of removing unused plans when necessary?"
My answer to that is, if you regularly have an s-ton of dynamic-sql generating oodles of non-parameterized ad-hoc queries, then it makes perfect sense to turn this feature on.
You want to avoid placing a strain on system resources, such that it forces cached-plan/data removal after you've used up the max cache memory space.
Here's a query I wrote to show you how many Ad-Hoc Plans you currently have cached and how much disk-space they're eating up (results will change throughout the day - so test it during a time of heavy load):
--Great query for making the argument to use "Optimize for Ad Hoc Workloads":
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) )[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) )[Pct_MB_1_Use]
FROM
(
SELECT CP.objtype[CacheType],
COUNT(*)[Total_Plan],
SUM(CASE WHEN CP.usecounts > 2 THEN 1 ELSE 0 END)[Total_Plan_3orMore_Use],
SUM(CASE WHEN CP.usecounts = 2 THEN 1 ELSE 0 END)[Total_Plan_2_Use],
SUM(CASE WHEN CP.usecounts = 1 THEN 1 ELSE 0 END)[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) )[Total_MB_1_Use],
CAST(AVG(CP.usecounts * 1.0) as Decimal(12,2))[Avg_Use],
CAST(AVG(CASE WHEN CP.usecounts > 1 THEN (CP.usecounts * 1.0)
ELSE NULL END) as Decimal(12,2))[Avg_Multi_Use]
FROM sys.dm_exec_cached_plans as CP
GROUP BY CP.objtype
) AS S
ORDER BY S.CacheType
Results:
I'm not gonna say, "When you have X MB's" or "If X% of your Ad Hoc are Single-Use" to turn this on.
It doesn't affect Sprocs, Triggers, Views or Parameterized/Prepared SQL - just the Ad-Hoc queries.
My personal recommendation is to just turn in on in your Prod Environment, but consider leaving it off in your Dev Environment.
I say this only for Dev, because if you're optimizing a query that takes a minute or more to run, then you don't want to run it 3 times before you may see how fast it will go with with it cached - every single time you edit it to find the best optimization design.
If your job doesn't involve doing this all day, then go nuts and ask your DBA to turn it on everywhere.
Think of a production server that serves only 5 different queries, but several thousand of those per second. You are the Microsoft SQL Server development team. You are going to fiddle with plan caching. Do you turn this behavior on by default, when you know that some of your largest and most critical clients (e.g., Microsoft's internal SAP implementation) work on the same campus and use the same cafeteria you do?
"Why should I NOT use...." In the course of some performance investigations, pulling plans out of the plan cache near real time, while observing resource utilization can be very helpful. "Optimize for adhoc workloads" may disrupt that, since the adhoc stub plans won't return a plan when querying the cache. In a case like that, if query and plan can't otherwise be identified, the setting could be toggled off and on again for the sake of investigation. Note that a change in setting effects queries compiled from that point forward. Also, whenever changing a 'server' property, check on a nonprod instance at the same version to verify whether or not the change will flush plan cache. I personally hate being surprised by that. (For example, changing maxdop at server level typically flushes plan cache, while changing dop in Resource Governor does not.)
"The compiled plan stub does not have an execution plan associated with it and querying for the plan handle will not return an XML Showplan." http://technet.microsoft.com/en-us/library/cc645587.aspx
