Optimize for ad-hoc workloads

SQL Server 2008 introduced a new advanced option called “Optimize for ad-hoc workloads”.
According to Microsoft:

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

This is interesting for those of us who have software querying the database in on-the-fly queries instead of, for instance, stored procedures. Without optimize for ad hoc workloads turned on every query will be cached in the procedure cache, including those queries that generate an execution plan and only use it once. As you can imagine this can take a big bite out of your memory, memory you can better use for your buffer cache!

So just how big can that bite get? Well according to Greg Low’s recently published “Plan Caching and Recompilation in SQL Server 2012” it can get pretty big:

SQL Server Version Cache Pressure Limit
SQL Server 2012, SQL Server 2008 and SQL Server 2005 SP2 75% of visible target memory from 0-4GB + 10% of visible target memory from 4Gb-64GB + 5% of visible target memory > 64GB
SQL Server 2005 RTM and SQL Server 2005 SP1 75% of visible target memory from 0-8GB + 50% of visible target memory from 8Gb-64GB + 25% of visible target memory > 64GB
SQL Server 2000 SQL Server 2000 4GB upper cap on the plan cache

Source: Plan Caching and Recompilation in SQL Server 2012, G. Low

This means that if you have a SQL Server 2012 server with 100GB of memory you can use

(0.75 x 4) + (0.10 x 60) + (0.05 x 36) = 3 + 6 + 1.8 = 10.8GB on your procedure cache!

With the optimize for ad-hoc workload option enabled, whenever a execution plan is generated for the first time it will write a stub (which is much smaller in size) in the procedure cache instead of the full execution plan. When the same query is run a second time, and the execution plan can be reused, then the full execution plan will be written to the procedure cache.

So let’s find out how it works and how it can help you to get some space back from the procedure cache!

For this demo we will make use of the AdventureWorks database.

First we want to make sure optimize for ad-hoc workloads is turned off:

-- If you don’t have advanced options enabled yet
EXECUTE sp_configure 'show advanced option', 1
RECONFIGURE

-- Turn optimize for ad hoc workloads off
EXECUTE sp_configure 'optimize for ad hoc workloads', 0

RECONFIGURE

Next step is to clear the procedure cache, make sure you do not use this on production!

DBCC FREEPROCCACHE

Let’s find out if there is anything in the procedure cache:

SELECT usecounts, size_in_bytes, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%HumanResources%' AND
text NOT LIKE '%dm_exec_cached_plans%'

It should return nothing since we cleared the cache.

So let’s select something to generate a execution plan:

SELECT * FROM HumanResources.Employee

Now let’s run our procedure cache query again:

SELECT usecounts, size_in_bytes, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%HumanResources%' AND
text NOT LIKE '%dm_exec_cached_plans%'

The result should look something like this:

adhoc_01

As you can see the query we just ran is cached on first usage.

Now, let’s enable the optimize for ad-hoc workload option:

EXECUTE sp_configure 'optimize for ad hoc workloads', 1

RECONFIGURE

And let’s clear the procedure cache again:

DBCC FREEPROCCACHE

If we run our SELECT query again and look into the procedure cache you should see something like this:

adhoc_02

As you can see our execution plan has a different cacheobjtype showing us our plan is a stub. You can also see the stub is a lot smaller then the full plan!

If we now run our SELECT query again you will notice the cacheobjtype change from Compiled Plan Stub to Compiled Plan and it grows in size:

adhoc_03

This is normal behavior, the execution plan will only be saved as the full plan when it is ran a second time (or more).

So if you have a lot of queries that generate a one-time execution plan you will save a lot of memory space (that can now be used for the buffer cache) by turning the optimize for ad-hoc workload option on!

20 comments to “Optimize for ad-hoc workloads”

You can leave a reply or Trackback this post.
  1. After I originally commented I seem to have clicked on the -Notify me when new comments are added- checkbox
    and from now on whenever a comment is added I get 4
    emails with the exact same comment. Perhaps there
    is a means you are able to remove me from that service? Cheers!

  2. Undeniably consider that which you said. Your favourite reason appeared to be at the internet the easiest thing to be mindful of.
    I say to you, I definitely get annoyed whilst folks think about concerns that they plainly do not
    realize about. You controlled to hit the nail upon the highest as smartly as
    defined out the whole thing without having side-effects , people could take a signal.

    Will probably be back to get more. Thank you

  3. If some one wants expert view on the topic of blogging and site-building after that
    i advise him/her to pay a visit this weblog, Keep up the pleasant work.

  4. I was pretty pleased to uncover this site. I wanted to thank you for your time for this particularly fantastic read!!

    I definitely liked every little bit of it and i also have you saved as a favorite to
    see new information in your web site.

  5. I don’t even know how I stopped up here, but I assumed this submit was great.
    I don’t realize who you’re however definitely you are going to a well-known blogger if you are not already.
    Cheers!

  6. I must thank you for the efforts you have put in writing
    this site. I really hope to check out the same high-grade content by
    you later on as well. In truth, your creative writing abilities
    has encouraged me to get my own blog now 😉

  7. I think that is among the so much vital information for me.
    And i’m happy studying your article. However wanna statement on some general things, The site taste is wonderful,
    the articles is really nice : D. Good activity, cheers

  8. However the headhunter title caught on due largely to the efforts
    of the people engaged in headhunting. However, the development
    course of exposed web sites tto safety dangers because of the glaring gaps attributable to
    external performance. For, SharePoint developer,
    ensure you could carry out the process utilizing the newest version of the
    SharePoint platform. Be easy, trustworthy and keep the pinnacle-hunter updated on any issues
    which might crop out alongside the method. 15. You’ll be able
    tto check in/ out a visitor utilizing thhe entrance workplace system.
    Using the SharePoint platform, you can set up your personal workflow in a simple, clear and concise way to streamline
    your small business operations along with your
    overall mission plan. The networking aspect of your search shall be extra produyctive
    when you could have a clear concentrate on business, industry segment, type of firm throughout the section,
    and then a clearly definable goal job that you could land and in which you’ll
    be successful.

    While there is no such thing as a conclusive proof linking this particular person with Charietto the headhunter, some scholars have assumed a connection. But whereas their final prpose is just not that totally
    different, the way that they work to get there is.

    Charietto wwas inspired by Julian to attack the barbarian raiders at night, whereas
    Roman regular forces would confront them by day. In accordance with
    Zosimus, Charietto saw barbarian raiders crossing the Rhine and decided to take motion. Charietto wwas an Ancient German headhunter annd bounty hunter who labored for the Romans.
    Charietto was joined by other men, and finally their success earned him the admiration oof Julian, who
    was commander within the area and later turned the Emperor generally
    known as Julian the Apostate. After the success of their debut album,
    The Kentucky Headhunters began touring with Hank Williams, Jr.

    and Delbert McClinton. Headhuhnters normally work as representatives of sure companies so you must discover an expert that makes a speciality of
    puttijg folks inside your subject or industry.

    Developing a quiver of headhunter relationships serves one’s profession, positive, but studying for
    the CFA this isn’t. Ultimately, no one’s obligated to reply to a cold name.
    Others are wanting to vary career tracks or places, and so wouldn’t be on the call lists for
    roles they want. People who find themselves utilizing are in search of people whoo find themselves glad to try truly and have to
    study. By looking at a small variety of essential ratios you
    may gget an idea in regards to the competitive strength and profit energy in a enterprise.
    Define it. “If you are out of labor and actively seeking to get on the headhunter radar, think very clearly about the messaging and what you wish to do in the next function,” in line with DBP’s Huggett,
    who’s based mostly in London and runs searches as far
    afield as Australia. Despite the fact thazt I don’t love recruiters who work with latest college graduates, this offers you
    no proper to go on to the company. Executive recruiters work exhausting to put certified
    candidates in positions that are properly-suited to them.

    Further, ouur headhunters find and place the candidates that can permit your organization to carry out to its most potential.

    The headhunter which was asked to determine feminine
    candidates to change into the following Bank of England governor
    is to find a brand new chief for town’smost influential lovbying group amid a sexism row.
    When informed of this, Freshfields allegedly expressed great interest in recruiting Klingsberg,
    however quickly after requested the agency to hold off its recruiting efforts.

    With Deltona set to get two new commissioners in November, the present City Commissioon agreed to wait till
    after the election to carry a workshop on its metropolis supervisor search.
    Mayokr Heidi Herzberg said the fee may additfionally want to consider planning a separate workshop to
    receive public enter and discuss what the comunity
    desires in a metropolis manager. Marc-Antonie Cooper
    has served as Deltona’s interim manager since Jane Shang resigned at the tip of January.
    Herzberg stated at thhe end of Tuesday’s workshop. The cover letter is an introduction to your resume and can peak interest for what is to
    come back. Freshfields reportedly instructed
    Boston Executive the agency had performed no role in the introduction or placement of Klingsberg and wasn’t
    owed any compensation.

    “If a candidate can come to us and say, ‘This is what I’m great at; that is what I’ve accomplished; these are the two things I’m really enthusiastic about doing next’ – it’s incredibly useful,” Huggett said.
    Of course, recruiters would say, “Be nice and useful to recruiters.” But first impressions at all times
    matter, and blowing someone ooff unti yyou want one thing
    from them is a bad look. Recruiters sometimes reach out to go
    looking targets a couple of times and ways before backing off.
    “They will reach out to establish a relationship; we have a superb dialog; and i encourage them to keep in touch. “That doesn’t mean you want to provide them
    the keys to the castle and 10 good names. But they don’t.
    Andd then thrree years down the highway, theyy need something and then name me up.

    “But if I don’t know the person referenced, it’s not very useful. This particular person is a recruiter whose specialty is matching company purchasers with professionals who are highly skilled. The union was unable to challenge the choice because the person had been there less than two years, Unite’s national officer Dominic Hook said.

  9. Excellent weblog here! Additionally your site rather a lot up fast!
    What web host are you using? Can I get your associate hyperlink to your host?
    I want my site loaded up as quickly as yours lol

  10. I like the helpful information you supply on your articles.
    I’ll bookmark your blog and test once more right here regularly.
    I’m reasonably sure I will learn a lot of new stuff proper here!
    Good luck for the following!

  11. I was very pleased to discover this web site.
    I wanted to thank you for ones time for this particularly wonderful read!!

    I definitely savored every part of it and i also
    have you saved as a favorite to see new information in your website.

  12. Right here is the right webpage for anyone who would like to understand this topic.
    You know a whole lot its almost hard to argue with you (not that
    I personally will need to…HaHa). You certainly put a
    brand new spin on a subject that’s been discussed for years.

    Great stuff, just great!

  13. Nice post. I was checking continuously this weblog and I am inspired!
    Extremely useful information specially the remaining
    phase :) I handle such information a lot. I was
    looking ffor thi certain information for a lolng time.
    Thank you and best of luck.

  14. Having read this I thought it was extremely enlightening.
    I appreciate you spending some time and energy to put this article together.
    I once again find myself spending a significant amount of time
    both reading and commenting. But so what, it was still worthwhile!

  15. Thank you for some other magnificent post.
    Where else could anyone get that type of info in such an ideal manner of writing?
    I have a presentation subsequent week, and I am at the search for such information.

Write a Reply or Comment

Your email address will not be published.