How to Improve Performance When Adhoc Queries are Executed Frequently

There are many instances when same query is executed number of times on our database server and every time optimizer has to compile the same and create query execution plan and same is stored in cache so that it would be used next time and so. When server is heavily used and cache keeps getting refreshed due to high volume of query coming from application server, we need to single-out those queries which are executed once and doesn’t get repeated. Once we single them out, query plan doesn’t get stored rather only stub of the query plan is stored, if it gets executed before cache gets cleared, stub plan will be cleared and same will be replaced by full plan.

Let’s see how to enable this configuration using command:

SP_CONFIGURE ‘Show Advanced Options’, 1
GO
— To make that online setting take effect
RECONFIGURE
GO
— Optimize for Ad hoc Workload Setting to 1 – or enabled
SP_CONFIGURE ‘optimize for ad hoc workloads’, 1
GO
RECONFIGURE
GO

Same can also be changed at UI(user Interface) level by getting into advance tab of properties section by right clicking at instance level as below:

Now, lets understand when should you enable this setting and would benefit and when should it not be. In some cases, it could degrade the performance.

First criteria would be to find out how many adhoc queries are present in the cache plan, please ensure that cache has enough plan to take decision based on that, if server’s up time is 1 or 2 day only, preferably we need to wait for couple of days so that cache has good volume of plans and it would help in taking right call.

We shall run the query to check plans which are executed only once:

select usecounts,cacheobjtype,objtype,size_in_bytes,[text]
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where usecounts =1
go

You may export it to excel and filter it for adhoc queries. Alternatively, you may use below query to find out the percentage of adhoc queries:

SELECT adhoc_Plan_MB, total_Cache_MB,
adhoc_Plan_MB*100.0 / total_cache_MB AS ‘AdHoc %’
FROM (
SELECT SUM(convert(bigint,(CASE
WHEN objtype = ‘adhoc’
THEN size_in_bytes
ELSE 0 END))) / 1048576.0 adhoc_Plan_MB,
SUM(convert(bigint, size_in_bytes)) / 1048576.0 Total_Cache_MB
FROM sys.dm_exec_cached_plans)Plan

We should be enabling ‘optimize for ad hoc workloads” only when adhoc queries are more than 40% on the server and leave it as it is if the percentage is less than 20. In case, the value is 10 or less and if this setting is enabled, it could degrade performance.

You may get full details about query plans and its usage by using below query:

SELECT Summation.CacheType, Summation.Avg_Use, Summation.Avg_Multi_Use,
Summation.Total_Plan_3orMore_Use, Summation.Total_Plan_2_Use, Summation.Total_Plan_1_Use, Summation.Total_Plan,
CAST( (Summation.Total_Plan_1_Use * 1.0 / Summation.Total_Plan) as Decimal(18,2) )[Percentage_Plan_1_Use],
Summation.Total_MB_1_Use, Summation.Total_MB,
CAST( (Summation.Total_MB_1_Use * 1.0 / Summation.Total_MB ) as Decimal(18,2) )[Percentage_MB_1_Use]
FROM
(
SELECT Cached_Plan.objtype[CacheType],
COUNT(*)[Total_Plan],
SUM(CASE WHEN Cached_Plan.usecounts > 2 THEN 1 ELSE 0 END)[Total_Plan_3orMore_Use],
SUM(CASE WHEN Cached_Plan.usecounts = 2 THEN 1 ELSE 0 END)[Total_Plan_2_Use],
SUM(CASE WHEN Cached_Plan.usecounts = 1 THEN 1 ELSE 0 END)[Total_Plan_1_Use],
CAST((SUM(Cached_Plan.size_in_bytes * 1.0) / 1024 / 1024) as Decimal(12,2) )[Total_MB],
CAST((SUM(CASE WHEN Cached_Plan.usecounts = 1 THEN (Cached_Plan.size_in_bytes * 1.0) ELSE 0 END)
/ 1024 / 1024) as Decimal(18,2) )[Total_MB_1_Use],
CAST(AVG(Cached_Plan.usecounts * 1.0) as Decimal(12,2))[Avg_Use],
CAST(AVG(CASE WHEN Cached_Plan.usecounts > 1 THEN (Cached_Plan.usecounts * 1.0)
ELSE NULL END) as Decimal(12,2))[Avg_Multi_Use]
FROM sys.dm_exec_cached_plans as Cached_Plan
GROUP BY Cached_Plan.objtype
) AS Summation
ORDER BY Summation.CacheType

The result will be displayed in below grid format:

CacheType	Avg_Use	Avg_Multi_Use	Total_Plan_3orMore_Use	Total_Plan_2_Use	Total_Plan_1_Use	Total_Plan	Percentage_Plan_1_Use	Total_MB_1_Use	Total_MB	Percentage_MB_1_Use
Adhoc	111.46	3551.22	170	16	5792	5978	0.97	1618.22	1635.88	0.99
Prepared	6293.08	6882.43	1609	238	173	2020	0.09	32.23	385.19	0.08
Proc	5420.65	5689.71	269	13	14	296	0.05	22.48	179.31	0.13
Rule	69.98	94.24	162	20	64	246	0.26	1.95	7.8	0.25
Trigger	3	3	1	1	0	2	0	0	2.88	0
UsrTab	163.85	165.53	88	9	1	98	0.01	0.02	2.38	0.01
View	1576.01	1576.01	463	68	0	531	0	0	65.44	0

Please leave your comment if you liked this post or have any feedback.