When you run a query in SQL Server, the SQL Server Query Optimizer will draw the road map for that query, specifying the optimal way to execute it, which is called the query execution plan.
Generating the execution plan will take few milliseconds from the CPU cycles, which is negligible for one query or small load, but it will be considerable for a very heavy transactional workload. Because of this, SQL Server caches these generated plans in a special type of memory called the Plan Cache to eliminate the overhead generated by the query plan if the same query is executed again. When you submit your query to the SQL Server Engine, it will search in the plan cache if there is any existing execution plan that can be reused, if an available execution plan is found in the plan cache, the plan will be used to execute that query, otherwise, the SQL Server Query Optimizer will create a new plan and keep it in the plan cache for future use.
There will be some cases, where the query is called one time and never executed again. Like any other SQL query, the SQL Server Query Optimizer will create an execution plan for that query and save it in the plan cache for further reuse. If there are many query plans for ad hoc queries that run one time and never executed again, these plans will consume the plan cache memory although these plans will not be visited again. In this case, we are wasting an important SQL Server resource, which is memory, to save a few milliseconds of time and CPU cycles that are consumed while creating new plans.
Starting from SQL Server 2008, a new server level setting is introduced that can be used to improve the plan cache efficiency for such workload type that has many ad hoc queries that are not called frequently. When setting the Optimize for Ad hoc Workloads option to 1, the SQL Server Database Engine will store a small compiled plan stub value in the plan cache when the query is compiled for the first time, rather than storing the full execution plan for that query. If the query is executed again, the stub value will help the SQL Server Database Engine to determine that this ad hoc query has been complied one time before, so this stub value will be removed from the plan cache and replaced by the full query execution plan for further reuse after compiling the query again. In this way, the memory pressure that is caused by storing the execution plans for all running queries will be eliminated without performing any change in your applications, as the plan cache will not be filled with many execution plans for ad hoc queries that are executed once and will not be called again and keep that memory to store the plans that are really executed frequently.
Let us go through our demo to understand how the Optimize for Ad hoc Workloads option works practically. We will clear the plan cache first using the DBCC FREEPROCCACHE command and run a simple SELECT query from the Employees table in the SQLShackDemo test database:
|
1 2 3 4 5 6 7 8 |
USE SQLShackDemo GO DBCC FREEPROCCACHE GO SELECT * FROM [SQLShackDemo].[dbo].[Employees] WHERE [EmpName]='Emily' |
Then we will use the sys.dm_exec_cached_plans dynamic management object to return the cached execution plans in the plan cache memory storage and view the number of reuse of these execution plans, the amount of memory taken by each cached plan and the cached query text by CROSS APPLY that dynamic management object with the sys.dm_exec_sql_text dynamic management object passing the plan handle as parameter as follows:
|
1 2 3 4 5 |
SELECT usecounts AS NumOfExecutions,cacheobjtype,objtype AS ObjectType,size_in_bytes AS PlanSize,[text] AS QueryText FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) |
The result of the previous query will show us that, the execution plan for our simple ad hoc SELECT statement is stored in the plan cache memory as a full compiled plan, executed only once and consuming 16 KB from the plan cache total storage, which is not too much if we have large memory on our SQL Server or the load in our server is not heavy. However, you need to take that into consideration if your system is a heavy transactional system with thousands of ad hoc queries and that they will eventually consume the plan cache part of the memory affecting the overall performance of your SQL server. In this case, it is time to think about using the Optimize for Ad hoc Workloads option.
To have a good indication that our workload requires enabling the Optimize for Ad hoc Workloads option we will query sys.dm_exec_cached_plans dynamic management object again for the total number of execution plans stored in the plan cache for each plan cache type, and the total size of memory consumed by each plan cache type as below:
|
1 2 3 4 5 6 7 8 9 |
SELECT objtype AS [PlanCacheType], COUNT_BIG(1) AS [NumOfPlans], SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [PlanSizeInMB] FROM sys.dm_exec_cached_plans GROUP BY objtype ORDER BY NumOfPlans desc GO |
If the result of the previous query shows that, you have huge number of execution plans of Ad hoc plan cache type consuming huge amount of your plan cache memory, then this is a good indication that you should enable the Optimize for Ad hoc Workloads server option. The below result is from one of our production servers showing that more than 8 GB of memory are consumed to keep the execution plans of 154045 ad hoc plans:
The Optimize for Ad hoc Workloads option is an advanced server configuration that you will not be able to configure without enabling the show advanced options using sp_configure command. After being able to show the advanced server options, we can enable the Optimize for Ad hoc Workloads option using the sp_configure command setting its value to 1 and perform the RECONFIGURE command to take effect as below:
|
1 2 3 4 5 6 7 8 9 10 |
SP_CONFIGURE 'Show Advanced Options', 1 GO RECONFIGURE GO EXEC sys.sp_configure N'Optimize for Ad hoc Workloads ', N'1' GO RECONFIGURE WITH OVERRIDE GO |
The below message will be displayed after executing the previous command showing that the show advanced and Optimize for Ad hoc Workloads option options are enabled as their values are changed from 0 to 1:
Enabling the Optimize for Ad hoc Workloads option will affect only the new cached plans, without affecting the plans that are already stored in the plan cache storage. You can also enable the Optimize for Ad hoc Workloads option using the SQL Server Management Studio from the Advanced page of the Server Properties window by setting the value to True as in the below snapshot:
Now the Optimize for Ad hoc Workloads option is enabled. If we clear the plan cache and perform the previous SELECT statement again:
|
1 2 3 4 5 6 7 8 |
USE SQLShackDemo GO DBCC FREEPROCCACHE GO SELECT * FROM [SQLShackDemo].[dbo].[Employees] WHERE [EmpName]='Emily' |
Then check if that plan is stored in the plan cache by querying the sys.dm_exec_cached_plans dynamic management object:
|
1 2 3 4 5 |
SELECT usecounts AS NumOfExecutions,cacheobjtype,objtype AS ObjectType,size_in_bytes AS PlanSize,[text] AS QueryText FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) |
The result will show us that only a Compiled Plan Stub is stored for that query consuming only 384 bytes from the plan cache storage compared to the 16 KB size when storing it from the first execution as a full plan before enabling the Optimize for Ad hoc Workloads option:
Let us run the SELECT statement again:
|
1 2 3 4 5 6 |
USE SQLShackDemo GO SELECT * FROM [SQLShackDemo].[dbo].[Employees] WHERE [EmpName]='Emily' |
Then check what is stored now in the plan cache for that query by querying the sys.dm_exec_cached_plans dynamic management object:
|
1 2 3 4 5 |
SELECT usecounts AS NumOfExecutions,cacheobjtype,objtype AS ObjectType,size_in_bytes AS PlanSize,[text] AS QueryText FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) |
The result will show us that the Compiled Plan Stub for that query is replaced by full compiled plan with size 16 KB that is executed only once. Running the query again and again, the number of executions will be increased indicating that the query that is executed frequently will use the same query plan that is stored in the plan cache, so we are not wasting the plan cache storage for a query that is used once:
If the cached execution plans in your system still not fitting the plan cache storage although you enabled the Optimize for Ad hoc Workloads option and you are not able to extend your SQL Server memory or perform changes in your application, it is better to periodically clear the plan cache storage or minimum clear the ad hoc queries plans from the plan cache using the below DBCC command:
|
1 2 3 |
DBCC FREESYSTEMCACHE('SQL Plans') |
Conclusion
The SQL Server Database Engine caches the execution plans for the running queries in a special type of memory storage called the plan cache, including the queries that are running only once. Such plans for the queries that will not be executed again will waste plan cache storage resources, as these stored plans will not be visited again. To overcome such issue, SQL Server 2008 introduced the Optimize for Ad hoc Workloads option in which only a small compiled plan stub will be stored in the plan cache when the query is executed at the first time and will be replaced with the full execution plan if it is executed again. In this way, you will save the plan cache storage from being consumed by less frequently used plans and keep it for the queries that are running frequently.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021