Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

5 Answers 5

up vote 21 down vote accepted

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!

share|improve this answer
7  
+1 new features are very, very rarely turned on by default. I can't really think of any good reasons to not turn this specific feature on - at the worst case all of your queries are single-use and wouldn't benefit from caching anyway. –  Aaron Bertrand Feb 26 '13 at 19:48
    
This is a "safe" answer based on common sense and doesn't address the question. The asker wants to know specifically the use case for when NOT turning this feature on is better. –  MikeTeeVee Mar 26 '13 at 15:17
1  
MikeTeeVee - it might be a safe answer, but this is one of those features where I genuinely cannot think of a reason not to enable it. Since it is so awesome, I just wanted to explain why it was off by default! –  Peter Schofield Mar 26 '13 at 19:37
add comment

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?

share|improve this answer
3  
Ok so pretend the feature doesn't exist because it might cause a problem? This is why you test things. I'm not suggesting it should be on by default (Peter covered that quite well) - you just seem to be paranoid about the feature in general. If you're going to suggest that turning it on is a bad idea, you should at least have some concrete idea about why you feel that way, other than being Chicken Little about it. –  Aaron Bertrand Feb 26 '13 at 20:02
1  
Ok, so don't ever use any new feature, ever. <shrug> –  Aaron Bertrand Feb 26 '13 at 20:33
1  
@Stu in fairness, you also have a strawman with "always better." –  swasheck Feb 26 '13 at 20:45
2  
Fair enough. I should have phrased that better. Aaron, I apologize. In the end, I think we are agreeing in a very unusual way. –  Stu Feb 26 '13 at 20:47
2  
@Stu yes but remember the bulk of your audience here is not in your situation. Any fear-mongering should be based on real, widespread problems with the specific feature in question, not a general mistrust in change. –  Aaron Bertrand Feb 26 '13 at 21:04
show 11 more comments

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
share|improve this answer
    
Not only is the math a little off, but your assumptions for recommending turning on this feature are debatable. The Kimberly Tripp article linked to by @SomeGuy gives a much better and concise script that shows you the Plan Cache breakdown in good detail. sqlskills.com/blogs/kimberly/… –  MikeTeeVee Mar 26 '13 at 21:22
    
Can you let me know why the math is little off as well as the assumption is debatable ? I have been using this for almost more than a year and it does report correctly. here the assumtion is that if the % of total cache plan occupied by adhoc plans only used once is more than 25% then, enabling this feature will help. (for this query to give accurate results, the server must be running for atleast a day or two). –  Kin Mar 26 '13 at 21:38
    
I ran both Kimberly's and yours at the same time. It added Kimberly's query to the cache plan before running yours, which explains why it was a little off - sorry. As for the 200MB/25% rule, it's debatable because it should be based on other factors like environment, available memory (we have over 100GB) and the rate of 1-use plans created daily (500 a day is ~1 every 3mins). Our 3rd party database's scripts free the cache every night, which doesn't always give our 1-Use plans enough time to get reused - it depends on how the app is used that day. I will remove both of my comments tomorrow. –  MikeTeeVee Mar 27 '13 at 6:09
add comment

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.

share|improve this answer
add comment

"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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.