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:
|
1 2 3 4 |
dbcc freeproccache go select FirstName,LastName from person.person Where LastName='Raheem' go |
We can check the result in the cache:
|
1 2 3 |
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.
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:
|
1 2 3 4 |
sp_configure 'show advanced options',1 GO reconfigure GO |
After executing the above code we can configure ‘Optimize for Ad hoc workloads’:
|
1 2 3 4 |
sp_configure 'optimize for ad hoc workloads',1 GO reconfigure go |
Now let’s run the queries again:
|
1 2 3 4 5 6 7 8 |
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.
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:
|
1 2 3 4 5 6 |
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 |
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.
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.
Load comments