Optimize For Ad Hoc Workloads – Enable or not?

With today’s Straight Up SQL Server tip, I want to talk about a setting I have so far only ever recommended to enable: Optimize for Ad Hoc Workloads. Rather than just make a suggestion, I want to keep in the spirit of the tips for beginner approach to the series and describe a little of the “how and why” behind the setting. That’s hard because we could get even longer reducing every concept, I hope I reduced it enough to explain it and give you an idea of topics to research further, if interested.

Introducing Optimize for Ad Hoc Workloads. . .

But first – Query Plans and Optimization

Before I can talk about Optimize for Ad Hoc workloads, I have to talk about query plans a little. I’m not going to go overboard on this primer because I want to keep the surface area low for this series. Every time you write a query in SQL Server (well not quite every, time as there are some very simple queries like select 1, or queries that error before they even get to the optimization phase), the SQL Server Query Optimizer needs to formulate a “map” of how it is going to run that query. Which indexes will it seek? Which physical types of joins will SQL use behind the scenes to do the joins you wrote in SQL? Will it have to apply a filter after a seek or scan because an index can’t help it? Etc. For most queries there are a rather high number of permutations of how the executions could happen. Maybe the query optimizer could first read from the bigger table and join it to the smaller. It could do the opposite, and there are many other factors more complex. But just know that it generates a map, a route. Or a query plan. You can see the graphical representation of that plan when you look at the query plan – either the estimated based on the information the query optimizer has when you ask for it or the actual based on the route the optimizer actually took. So this happens. It happens in milliseconds, normally single or double digit ms.  SQL won’t look at every possibly approach, it looks for a “good enough” plan. There are a lot more variables here. But when you go deeper – and I hope you do – you’ll learn more about them. It’s actually really fun and exciting stuff!

These plans take milliseconds of time and CPU to create. That doesn’t sound like a lot. but if you think about a query executed hundreds of times per second? Let’s say a “getCustomer” stored procedure that runs every time someone logs onto a busy eCommerce site? That’s a lot of milliseconds all added up.  So SQL caches that plan. It stores it. In memory. So when SQL Server sees you run the same pattern as that query whose plan is in memory it says “wow. cool. I’ve seen this before. Don’t need to go to the chart room and plot out a plan. Just give that query this plan” the Query Optimizer can stay on the easy chair and know that you have a plan. There are SO.MANY.POSTS to write about this, though. This can be good and bad. There can be bad plans, etc. But I don’t want to digress. Just remember: 1.) SQL makes a plan. 2.) It tries to reuse that plan generally when it can.

Sometimes, though –  actually I would say often – with ORMs and ad-hoc queries and even stored procedures, there can be “single use plans” – these are plans that get used, well you can figure it out, once. For various reasons. Maybe that query pattern is never called again. Maybe a query is written with some “no-no’s” for query plan reuse (see I told you this topic can spiral, you’ll have to trust me here). For whatever reason – these queries are used only once.

But I told you that the plan is stored. In memory. A finite resource. A resource we want to optimize and use efficiently. So most SQL Servers out there have a lot of plans hanging around in memory that will never get used again. SQL’s optimizer is well meaning here. It’s hoping you’ll come and ask for a plan and save a few ms of CPU ticks and time, but they are just clogging memory if they aren’t used again.

Finally. We can talk about it. . .

Optimize For Ad Hoc Workloads

This is a setting in SQL Server. It is configured either through the GUI by going to properties of the instance in object explorer and then advanced options, or through the GUI as I’ll show below. This setting is basically an enabled or disabled flag that tells SQL Server you either want it to store the complete query plan every time it creates one (Optimize for Ad Hoc Workloads is disabled, or set to 0, false, the default setting). Or you tell SQL Server “No. Just store a small portion of that query plan – a unique signature or stub of the query plan – the first time you see it, and IF you see it a second time, then store the entire plan”. You’ve told SQL to take a fingerprint of that plan and if it sees that fingerprint again to go ahead and compile it that second time and then store the whole plan.

In other words – SQL will take less memory for the plans that are executed only once and save it’s memory for the plans that are really executed at least twice. You tell SQL server to operate in this mode by enabling Optimize for Ad Hoc workloads (setting to true in the GUI, or setting to 1 as I show below).

I can’t really give you a situation that I would expect to see where this shouldn’t be enabled honestly. There are theoretical examples out there – maybe a series of queries – lots of them – with reusable plans executed only twice. Or something like that. I answer a lot of questions on DBA.StackExchange – it’s a great site in a great network and the consensus there seems to generally be that it almost always makes sense in this answer. When I do a SQL Server Health assessment? I want to see that enabled.`

How to Enable Optimize For Ad Hoc Workloads.

As with most of these – you can do it through the GUI. I won’t screenshot it but maybe a video later. Go to SSMS, connect to your instance. Right click on your instance. Then go to properties and advanced settings. You’ll see it there. This is an online setting – you can change it to true or back to false with users connected.

To do it with SP_Configure:

-- if you haven't looked at advanced options before in SP_CONFIGURE
SP_CONFIGURE 'Show Advanced Options', 1
GO
-- To make that online setting take effect
RECONFIGURE
GO

-- Change Optimize for Ad Hoc Workload Setting to 1 - or enabled
SP_CONFIGURE 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO

Happy tuning! Let me know if you’ve found a case where this setting isn’t ideal for a workload. Let me know if you have other questions burning in your mind. I’d love to help.