SQLServer : Adhoc Queries and Query Cache

by bitznbitez

Todays “Database Weekly” from SqlServerCentral refers to an article a couple years ago by Brad McGhee on the question of adhoc queries that are only run once and the impact these have on your query plan cache.  In this article Brad talks about the depletion of the plan cache by queries run only once.   The premise being things that are only run once don’t need cache to speed up their second run and may force out other plans that may be run again later, but haven’t been run recently etc.  Brad suggests you may have a problem if the number of adhoc query plans run only once exceeds 10,000.  In this case he suggests setting “optimize for ad hoc workload” to 1 so you stop storing queries in the plan cache that are only run once.  ( A stub is stored and if its run a second time the full plan cache is stored ).

I am always intrigued by magic numbers like the 10,000 above.  People generally want a rule of thumb, a magic number, and as consultants or authors we tend to give them one.  In the article Brad naturally says its just an estimate and you must test to see if you see any benefit or not from setting “optimize for ad hoc workload”.

The query recommended was the following :

I wanted to get a feel for the size and hit rates in the query cache on one of the servers I administer.

First how many entries are in the cache plan and what are their types.

select cacheobjtype, count(*) as [Number of Cach Entries] 
from sys.dm_exec_cached_plans group by cacheobjtype;
cacheobjtype Number of Cach Entries
-------------------------------------------------- ----------------------
Parse Tree 912
Extended Proc 14
Compiled Plan 36438

(3 row(s) affected)

Second lets get a feel for the total executions just for the compiled plans

select count(*) as [Total Queries], sum(usecounts) as [Total Executions]
from sys.dm_exec_cached_plans
where cacheobjtype = 'Compiled Plan';

Total Queries Total Executions
------------- ----------------
36450 80040514

(1 row(s) affected)

Third lets look at the distribution of queries by use count

select usecounts, count(*) as [total queries], (
 select sum([total queries])
 from (
   select count(*) as [total queries] 
   from sys.dm_exec_cached_plans b
   where cacheobjtype = 'Compiled Plan' 
   and b.usecounts <= a.usecounts 
   group by usecounts
 ) x
) as [running total of queries]

from sys.dm_exec_cached_plans a
where cacheobjtype = 'Compiled Plan'
group by usecounts
order by usecounts ;

usecounts total queries running total of queries
----------- ------------- ------------------------
1 13940 13940
2 1485 15425
3 965 16390
4 859 17249
5 855 18104
6 1211 19315
7 621 19936
8 636 20572
9 556 21128
10 581 21709
11 748 22457
12 449 22906
13 294 23200
14 383 23583
15 405 23988
16 306 24294
17 385 24679
18 303 24982
19 239 25221
20 254 25475
21 267 25742
22 142 25884
23 119 26003
24 135 26138
25 78 26216
26 96 26312
27 96 26408
28 114 26522
29 84 26606
30 121 26727
31 53 26780
32 99 26879
33 67 26946
34 83 27029
35 60 27089
36 92 27181
37 48 27229
38 63 27292
39 80 27372
40 66 27438
41 51 27489
42 68 27557
43 26 27583
44 73 27656
45 55 27711
46 42 27753
47 45 27798
48 82 27880
...
217658 2 36380
218131 1 36381
218160 1 36382
218625 1 36383
278999 1 36384
279038 1 36385
279040 4 36389
279052 2 36391
279066 1 36392
347112 1 36392
348500 1 36393
348508 1 36393
358974 2 36395
358976 2 36395
462156 2 36401
681007 1 36402
5589897 1 36402
5755192 1 36403
5910634 1 36404
11786061 1 36405

(2863 row(s) affected)

The above results indicate that by removing the queries executed once we would move up in the distribution to the queries run 48 times.   That is to say, where the running total is 2x the total of the single run.

The conclusion is that 13940 queries run once represents 37% of the query plans in the cache.  In this case I would definitely say its worth turning on.   To turn it on ( all versions of sqlserver from 2008 onward as I understand it )

sp_configure 'show advanced options',1
reconfigure
GO

sp_configure ‘optimize for ad hoc workloads’,1
reconfigure
GO

As with anything you need to test prior to implementation in a production environment.

Advertisements