PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

Using Optimize for ad-hoc workloads

SQL Server caches the executio plan of our queries, not only stored procedures, but also ad hoc queries. However, when our server activity is mostly ad hoc queries, we have the risk to be wasting memory with queries that will never be executed again.

To solve this problem, this memory wasting with queries that will never be executed again, we can use the configuration called ‘Optimize for Ad hoc queries’. When this configuration is active, the server only stores the execution plan in the cache on the second execution of the query, not on the first execution. On the first execution of the query SQL Server creates a stub in the cache. The stub contains only a execution count of the query. On the second execution the stub is replaced by the actual execution plan.

Let’s use AdventureWorks database to do a few tests with this configuration. In your test environment (you shouldn’t execute this in production), execute the following code:

We can check the result in the cache:

The image bellow shows the execution plan in the cache. Notice the size, 16kb.

Screenshot-168.png

Now let’s activate ‘Optimize for ad hoc workloads’ and run the above queries again. To configure ‘Optimize for Ad Hoc queries’ we need first to configure ‘Show Advanced Options’ using ‘sp_configure’ stored procedure.

We use the following code for this:

After executing the above code we can configure ‘Optimize for Ad hoc workloads’:

Now let’s run the queries again:

The image bellow shows the result in the cache, only 352 bytes.

Screenshot-169.png

This time only the stub is stored in the cache, not the entire plan, so we don’t waste memory with a query that can never be executed again.

Execute the query again, without cleaning the cache, and check the result:

Screenshot-170.png

The above image shows the query plan in the cache, now the stub was replaced by the compiled query plan with 16kb. Run the query again and the usecounts start to grow, like the image bellow.

Screenshot-171.png

Using ‘optimize for ad hoc workloads’ we can reduce the memory wasted with query plans never reused. Most times it’s a good practice to keep this option active.