Why would I NOT use the SQL Server option “optimize for ad hoc workloads”?

Question

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?

Answer 1

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!

Answer 2

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?

Answer 3

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

Answer 4

The Caching Management Algorithm:

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.
Which means that by turning this option on, you will cause ad-hoc queries that are run the 2nd time to be just as slow as the 1st.

This may not be a big deal at first, but still more noticeable.
Especially when you are developing and you run a query for 1min, then you run again to see how fast it will run with a query plan, only to have to wait another 1min, and then have to run it a 3rd time.
Imagine repeating that ad-nauseum.

So now the question becomes:

    "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 have an s-ton of dynamic sql generating oodles of non-parameterized ad-hoc queries running in a loop, then it makes perfect sense to turn this feature on - but only temporarily.

For Example:

I have a crazy stored procedure that searches the entire database for a GUID (and another sproc that searches char/text fields).
I use it once every blue moon.
The 3rd party database we use has about 8,000 tables (no joke) and you never know where the 3rd party app that uses this database will store it's data (sql-trace is not always an option).
So as I dynamically loop through 8,000 tables (times the number of Guid fields found in each table) I am hammering the Cache plan and squeezing out all those single-use (and probably multi-use) plans from memory.

This is the only time when it makes sense to turn on - again, only temporarily till my sproc completes.