Welcome to our deep dive into SQL Server’s “Optimize for Ad Hoc Workloads” option, a feature that might seem a bit technical at first but is incredibly handy for improving your database’s performance, especially if your environment is rife with one-time queries. Let’s break it down into more digestible pieces and explore how you can use it effectively with practical T-SQL examples.

What is “Optimize for Ad Hoc Workloads”?

Imagine your SQL Server as a busy librarian. Every day, it gets numerous requests (queries) from people (applications). Some of these requests are repetitive, while others are unique, only asked once. By default, SQL Server tries to prepare for all requests equally, but this can lead to wasted effort and resources, especially for those one-off questions.

Enter “Optimize for Ad Hoc Workloads.” This feature tells SQL Server to be smarter about how it handles these unique, one-time queries. Instead of immediately storing detailed information about every query, (i.e. the compiled plan of a query in the plan cache) it first takes a small note (a “stub”) for one-off queries. If the query comes back, then SQL Server will remember it fully. This approach saves memory and makes the server more efficient, like a librarian who decides not to shelve a book unless it’s requested at least twice.

How to Enable “Optimize for Ad Hoc Workloads”

Before you start, it’s good practice to check whether this feature is already enabled on your server. Here’s a simple T-SQL script to do that:

SELECT value_in_use
FROM sys.configurations
WHERE name = 'optimize for ad hoc workloads';

If the result is 0, the setting is disabled. To enable it, you can run:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

This script first ensures that advanced options are visible, then enables the “Optimize for Ad Hoc Workloads” setting.

Using SQL Server Management Studio (SSMS) GUI:

1. Open SQL Server Management Studio and connect to your SQL Server instance.
2. In Object Explorer, locate the database for which you want to modify the setting.
3. Right-click on the database and select “Properties” from the context menu.
4. In the Database Properties window, select the “Options” page from the list on the left-hand side.
5. Scroll down to the “Miscellaneous” section, where you’ll find the “Optimize for Ad Hoc Workloads” option.
6. Toggle the setting to either “True” or “False” as desired.
7. Click the “OK” button to save the changes.

Seeing the Difference

Now that you’ve enabled the setting, how can you tell it’s working? You can monitor the “Plan Cache” to see the effect. The Plan Cache is where SQL Server keeps information about all the queries it has seen. With our setting enabled, you should see fewer one-off query plans taking up space.

Here’s a script to view the cache before and after enabling the setting:

SELECT objtype AS [CacheType],
    count_big(*) AS [TotalPlans],
    sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [TotalMBs]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [TotalMBs] DESC;

Run this before and after enabling the setting (and running some ad hoc queries) to see the difference.

Practical Example: Before and After

To illustrate, let’s simulate a workload with and without the setting.

1. Without “Optimize for Ad Hoc Workloads”: Run a batch of unique queries and observe the Plan Cache size.
2. Enable the setting using the script provided above.
3. Run the same batch of unique queries again and compare the Plan Cache size.

You should notice that after enabling the setting, the Plan Cache is leaner, as SQL Server is not storing detailed plans for every one-off query.

Conclusion

“Optimize for Ad Hoc Workloads” is like teaching SQL Server not to hoard information on every single query it encounters, but rather to wait and see which queries are truly important. This can lead to significant performance improvements, especially in environments with many unique queries. By understanding and applying this feature, you can make your SQL Server more efficient, like a well-organized librarian who knows exactly when to keep a book on the shelf.

Remember, every environment is different, so it’s always a good idea to test changes like these in a development environment before rolling them out to production. Happy optimizing!