Empire Data Systems

Using Optimize for Ad hoc Workloads

Daniel AG by Daniel A G

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:

DBCC freeproccache
go 
SELECT firstname, 
       lastname 
FROM   person.person 
WHERE  lastname = 'Raheem' 
go 

We can check the result in the cache:

SELECT usecounts, 
       cacheobjtype, 
       objtype, 
       size_in_bytes, 
       [text] 
FROM   sys.dm_exec_cached_plans 
       CROSS apply sys.Dm_exec_sql_text(plan_handle) 

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:

Sp_configure 'show advanced options', 1 
go 
RECONFIGURE 
go 

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

Sp_configure 'optimize for ad hoc workloads', 1 
go 
RECONFIGURE 
go 

Now let’s run the queries again:

DBCC freeproccache 
go 
SELECT firstname, 
       lastname 
FROM   person.person 
WHERE  lastname = 'Raheem' 
go 
SELECT usecounts, 
       cacheobjtype, 
       objtype, 
       size_in_bytes, 
       [text] 
FROM   sys.dm_exec_cached_plans 
       CROSS apply sys.Dm_exec_sql_text(plan_handle) 
go 

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:

SELECT * 
FROM   person.person 
WHERE  lastname = 'Raheem' 
go 
SELECT usecounts, 
       cacheobjtype, 
       objtype, 
       size_in_bytes, 
       [text] 
FROM   sys.dm_exec_cached_plans 
       CROSS apply sys.Dm_exec_sql_text(plan_handle) 
go 

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.

Statistics Says It All

16

Years of Experience

3000

Gratified Students

100

Training Batches

9600

Training Hours

Subscribe

Please subscribe our technical blog to get recent updates.

Empire Data Systems

Social Links