SQL Server Settings: Optimize For Ad-hoc Workloads
I’m my last post on SQL Server Setting, I discussed MAXDOP and how it can affect SQL Server performance. In this post, I’ll review another SQL Server configuration setting that can impact performance – Optimize for Ad-hoc Workloads.
What is the plan cache?
Every time you run a query in SQL Server, an execution plan for that query is stored in memory on the server. This is referred to as the plan cache. SQL Server does this for every query. It does not matter if it is a SELECT, INSERT, UPDATE, or DELETE statement. SQL Server still generates an execution plan for it and saves it in the plan cache.
The reason for this is efficiency. The next time that query is executed, SQL Server already has an execution plan generated for it. SQL Server will just use the cached plan instead of having to regenerate a new one. This allows for better performance.
But what about all of those one-off queries you run in Management Studio? You know, the ones you run when designing reports or troubleshooting data consistency issues? Those are sitting there, unused, in the plan cache. Dynamic queries generated from ORM utilities like Entity Framework and LINQ can also fall into this category if not parameterized correctly.
Storing execution plans for these queries can consume unnecessary memory. It can also degrade overall performance, especially in environments where ad-hoc queries are common. Thankfully, SQL Server has a setting called “Optimize for Ad-hoc Workloads” to help with that.
What is the Optimize for Ad-hoc setting in SQL Server?
The “Optimize For Ad-hoc Workloads” setting in SQL Server is a configuration option that helps improve performance when dealing with queries that are used infrequently or only once (ad-hoc queries).
How does Optimize for Ad-hoc work?
When Optimize For Ad-hoc Workloads is enabled, the database engine stores a small query hash (instead of the full execution plan) in the plan cache when a query is executed the first time. This query hash, or “stub”, is much smaller than a full execution plan and takes up less memory in the plan cache.
If the query is executed a second time SQL Server checks if the query hash exists. If it does, SQL Server creates a fully compiled plan for that query and stores it in the plan cache, removing the query stub.
Essentially, in order for a query to be saved in the plan cache, it has to be run at least twice.
Benefits of enabling Optimize for Ad-hoc Workloads Setting
By default, SQL Server does not enable the “Optimize for Ad-hoc Workloads” setting. This means that SQL Server stores execution plans for all queries, including those ad-hoc queries that might be used only once. By enabling this setting, the memory management in the plan cache will become more efficient, which can lead to better overall performance, especially in systems with a high volume of ad-hoc queries.
How do I enable Optimize for Ad-hoc Workloads?
You can configure the Optimize for Ad Hoc Workload setting in Management Studio. Right-click on the SQL Server instance, select Server Properties, and then selecting Advanced.
You can also enable this by using the following SP_CONFIGURE command:
SP_CONFIGURE 'optimize for ad hoc workloads', 1
GO
RECONFIGUREFor more information
Do you have questions about your SQL Server’s configuration settings? Or are you having some performance concerns? Here are some other posts and links that may be helpful.
- optimize for ad hoc workloads (server configuration option) – SQL Server | Microsoft Learn
- dbatools docs | Test-DbaOptimizeForAdHoc
- Infographic: 5 Commonly Overlooked SQL Server Settings
- Why is My SQL Server Slow? 14 Common Reasons
- Do I Still Need a SQL Server Health Check?
- Anatomy of a SQL Server Transaction Log
If you’d like some assistance assessing your SQL Server workload, contact us. We’re happy to help.