Plan cache and optimizing for adhoc workloads

I mentioned that servers receiving a lot of dynamic constructs (typically those generated by client SQL apps/ORMs/LINQ, etc.) can start to consume too much plan cache and have problems with “single-use plans” in my last post titled: Statement execution and why you should use stored procedures. I also mentioned that SQL Server 2008 has an option/feature specifically to help reduce the bloat on the plan cache by only storing a query plan hash the first time a plan is created.

First – let’s check to see how your plan cache is currently allocated:

(note: updated to decimal(18,2) as a few of you had overflow errors due to high use counts!)

SELECT objtype AS [CacheType],
COUNT_BIG(*) AS [Total Plans],
SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
AVG(usecounts) AS [Avg Use Count],
SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes
ELSE 0
END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
SUM(CASE WHEN usecounts = 1 THEN 1
ELSE 0
END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC
GO

This statement will show you how much of your cache is allocated to single use plans… this query works in 2005 and 2008; however, the primary solution I’m describing here (optimize for adhoc workloads) will only work in SQL Server 2008 and higher.

IMPORTANT NOTE / UPDATE: The most important point about this post is that you should be using resources more efficiently by regularly (and programmatically) checking / clearing the ad hoc / prepared plan cache using DBCC FREESYSTEMCACHE (‘SQL Plans’)… So, this post is really more about understanding the problem. Check out this post for more information on these numbers as well as cleaning the plan cache: Plan cache, adhoc workloads and clearing the single-use plan cache bloat.

If you have a lot of your cache going to plans that are only executed once, then it’s time to clean up the cache and take better advantage of it with plans that are more consistent and more stable. Ideally, this means using more stored procedures and writing these stored procedures effectively for better performance (I’m currently in the midst of doing this in my Optimizing Procedural Code category). However, if you absolutely must use a lot of adhoc SQL, then you should consider upgrading and turning on this new option. It’s an advanced configuration option so you won’t see it until you “show advanced options” and it’s set using sp_configure. There have been some other really good posts out there on how to use this and what this is so I’m just going to bring together some great resources for you to read. The most important post to read (and it’s especially important for those on you on versions of SQL Server 2000 or SQL Server 2005 RTM/SP1 [er… why aren’t you on SP2?]) is that plan cache can get out of control. 2005 SP2 and 2008 reduce the total size but there no upper limit (which is again – a GREAT reason for the addition of “optimize for adhoc workloads”). Kalen Delaney talks about how things really work in her SQL Server 2005 SP2 post titled: Did You Know? SP2 does NOT limit the amount of plan cache you can have (key word there is LIMIT). Again, SQL Server doesn’t set an upper limit but it does [drastically] reduce the total size that’s possible (as of SP2). As for even more details on plan caching, recompilation and SQL Server 2008’s better cache control – check out Greg Low’s (blog|twitter) whitepaper titled: Plan Caching in SQL Server 2008. Taken STRAIGHT from the first section of the whitepaper:

SQL Server 2008 and SQL Server 2005 SP2
* 75% of visible target memory from 0-4GB + 10% of visible target memory from 4Gb-64GB + 5% of visible target memory > 64GB

SQL Server 2005 RTM and SQL Server 2005 SP1
* 75% of visible target memory from 0-8GB + 50% of visible target memory from 8Gb-64GB + 25% of visible target memory > 64GB

SQL Server 2000
* SQL Server 2000 4GB upper cap on the plan cache

Finally, lots of additional posts on this topic will give you even more details:

NOTE: Be sure to read the subsequent post on these numbers and how to deal with it when your cache is bloated!

Next couple of posts: (read this first) Plan cache, adhoc workloads and clearing the single-use plan cache bloat and then read this: Clearing the cache – are there other options?

Thanks for reading!
kt

87 thoughts on “Plan cache and optimizing for adhoc workloads”

  1. Hello Kimberly, here are my numbers !

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Prepared 2021 620.531250 195 265.601562 895
    Adhoc 1557 57.023437 678 28.718750 811
    Proc 210 35.992187 7000 4.523437 31
    UsrTab 8 0.710937 1002 0.421875 3
    Check 15 0.492187 2 0.257812 8
    Trigger 7 1.351562 6 0.000000 0
    View 113 14.742187 5 0.000000 0

    I was unaware of this option and found your latest posts on the subject very, very valuable.
    Thanks for that.
    Kind Regards,
    Olivier.

  2. Very relevant post Kimberly. We should be encouraging people to be on SQL Server 2005 SP3 (since SP2 is an "unsupported service pack"), and the same situation with moving from SQL Server 2008 RTM to SQL Server 2008 SP1. I have been using "optimize for ad-hoc workloads" for over a year with very good results. I will send you my numbers.

  3. Cool. Thanks Glenn. I suspect that a lot of folks have moved to this BUT I also suspect even more folks to be using things like LINQ and be completely unaware of this very necessary option. So… while there have been a lot of great posts about using this (and how to use it), I still wonder how bad some servers really are?? Hence the request! Your servers are probably going to be in a pretty good state but I wonder how many 2 or 3s there are? I’ve always wondered if this shouldn’t have been configurable? Where you should set it at 5 – or 10 before SQL Server would store the plan.

    In fact, I wonder if I shouldn’t create some other columns (plan cache dedicated to the "under 10 executions") for example? Thoughts?

    Regardless, we’ll get some good insight from averages. Really looking forward to the numbers! And, I’ll compile a bit of details from the numbers I receive as well.

    Cheers Glenn!!
    kt

  4. My numbers:
    Prepared 236 77.718750 261 68.578125 150
    Adhoc 1415 68.640625 389 42.093750 491
    Proc 289 47.109375 139355 7.335937 32
    UsrTab 1 0.070312 1 0.070312 1
    Trigger 3 0.281250 35 0.000000 0
    View 362 31.859375 18 0.000000 0
    Check 5 0.101562 15 0.000000 0

  5. Here is our PROD DWH totals. it is an SQL Server 2005 SP3 Standard Edition. Loading(ELT) is done using a lot of SP’s but querying is adhoc using SAP business objects.

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 2906 122.812500 24 77.078125 1911
    Prepared609 98.062500 106 75.593750 360
    Proc 53 23.734375 4115 15.390625 25
    View 52 3.593750 93 0.000000 0
    Check 2 0.031250 5 0.000000 0

  6. CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 10107 240.273437 76 157.507812 6073
    PrepareD 1442 48.984375 3826 4.750000 106
    Proc 44 13.367187 442322 0.742187 2
    Trigger 2 0.718750 38 0.000000 0
    View 20 1.867187 5 0.000000 0

  7. Here is the query off of our Production Sql Server 2005 SP3 Enterprise (Clustered) server.

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 17028 1255.601562 185 1160.851562 15845
    Prepared 22566 838.789062 357 99.867187 505
    Proc 561 186.039062 9677 20.414062 51
    Check 392 13.296875 10 5.468750 174
    UsrTab 3 0.281250 7 0.117187 1
    Trigger 7 2.820312 130391 0.000000 0
    View 313 32.031250 17 0.000000 0

  8. Here’s one that will skew your results. This is a SQL 2008 server with Optimize for adhoc workloads enabled.

    Adhoc 32085 896.078544 77 111.710937 14123
    Prepared 2771 178.304687 1682 80.890625 867
    Proc 231 54.812500 8145 1.382812 7
    UsrTab 1 0.218750 1 0.218750 1
    View 802 91.257812 338 0.070312 1
    Trigger 25 4.093750 972 0.046875 1

  9. Production server #s:
    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 8865 507.632812 6 293.976562 4738
    Trigger 330 286.507812 439 40.890625 46
    Prepared 1623 109.015625 99 23.687500 393
    Proc 922 186.781250 356 22.242187 114
    UsrTab 129 2.070312 2 1.070312 65
    Check 20 0.312500 5 0.078125 5
    Rule 41 0.320312 6 0.046875 6
    View 492 18.625000 41 0.000000 0

  10. Well… it’s not surprising that so many of you who read my blog have servers that aren’t in too bad of shape. There are still MBs of useless cache and you’ll want to clean that up for you but nothing’s quite as bad (so far) as one system I’m seeing right now that has over 7GB of single plan cache sitting wasted.

    Even in email the worst I’m seeing is a couple hundred MB. It’s the people that aren’t reading this blog for whom I’m worried! (tell your friends to check their servers – friends don’t let friends waste GBs of cache!)

    Keep em coming!

    Cheers,
    kt

  11. Hey there Darrel – Not too bad… and yes, a few folks sent me email with their numbers (from 2008 – with Optimize for adhoc workloads set) and all of these are better. But, stll seems like a lot of wasted cache!

    And, @SQLSVRMan – You’ve got over a GB. But, you’re running 2005 SP3 so it’s probably better than it may have been in earlier releases/SPs. You still might want to throw out cache periodically.

    Thanks for the numbers!! (and keep ’em coming – I want to see a clear winner (for worst cache) here :))

    Cheers,
    kt

  12. Actually, another good point (thanks stalker woman – you know who you are :) :) :))… thanks for telling me the system memory.

    That’s another thing to add to your numbers – let me know how much memory the box has on it. I just received an email where a server has over 1.5GB of wasted cache on a system with only 12GB. Those are not good percentages.

    Thanks!!
    kt

  13. Ha!! Lightweights!! :)

    I posted my results in a blog post of my own (which points back to here). I also made a slight modification of the query so that it outputs a Totals line at the bottom. My results are in a easy to read table there, but just to give you an idea, I have 50,745 total single-use plans in cache.

    My post: http://www.sqlservercentral.com/blogs/robert_davis/archive/2010/04/23/Looking-forward-to-Optimize-for-Ad-hoc-Workloads-in-Sql-Server-2008.aspx

  14. @Robert-I responded to your post. Excellent numbers! But, not yet the winner (sadly)… I have one customer at over 7GB of "single plan cache" and over 77K of single-use plans in cache. This is on a 2005 SP3 server with 128GB of memory.

    THANKS!!!
    kt

  15. 2008 sp1 DW server 104Gb DB memory

    Prepared 5580 3544.414062 190561 874.382812 1968
    Adhoc 9871 935.804687 116 681.539062 7711
    Proc 438 286.289062 283497 10.203125 30
    UsrTab 29 2.140625 3545 0.437500 2
    Trigger 4 0.195312 3 0.070312 1
    View 1513 171.937500 272 0.054687 1
    Check 15 0.359375 15 0.000000 0

  18. Hello,

    Here my results :
    CacheType Total Plans Total MBs Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 126954 3769.070312 1751.523437 61037
    Prepared 32423 964.687500 376.570312 9775
    Proc 2784 1235.664062 67.195312 186
    Check 1114 26.281250 0.187500 8
    Trigger 27 5.523437 0.156250 2
    View 4265 490.335937 0.000000 0
    UsrTab 6 0.539062 0.000000 0

    Sorry no AVG => Msg 8115, Level 16, State 2, Line 2
    Arithmetic overflow error converting expression to data type int.
    I’ve no enough time to check why.

    Win2K8 SQL2K8 2node failover cluster
    2 instances (result just for one of them)
    160 Gb RAM
    32 cores

    Chris

  19. UsrTab,2,0.437500,0.000000,0
    Prepared,11711,1247.304687,834.093750,2179
    View,3884,405.367187,0.000000,0
    Adhoc,148326,1043.029365,251.200576,128662
    Check,24,0.679687,0.312500,10
    Trigger,136,22.390625,0.453125,5
    Proc,567,256.390625,55.125000,

    sql server 2008 – 256GB. adhoc and forced parametrization enabled

  20. CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 6210 264.039062 1278 130.742187 4584
    Prepared 1488 152.843750 1391 103.382812 985
    Proc 137 121.187500 96624 4.093750 10
    UsrTab 1 0.117187 38 0.000000 0
    View 663 61.929687 172 0.000000 0
    Check 4 0.109375 8 0.000000 0
    Trigger 2 0.171875 108 0.000000 0

    SQL Server 2005 Enterprise Total memory 24 GB, Max set to default

  21. Here are my numbers on a SQL Server 2005 SP3 box with 7.5GB of total system memory all of which is available to SQL Server. This is an OLTP system that is almost exclusively queried in an ad-hoc manner.

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 4904 322.437500 67 150.906250 1887
    Prepared 4493 348.250000 196 40.937500 423
    Proc 440 103.179687 1964 8.523437 33
    UsrTab 15 0.343750 123 0.140625 2
    View 311 15.648437 62 0.031250 1

  22. We have a SQL Server 2005. I’m not sure of the other details. Our data is:
    Prepared3332 277.445312 73 150.976562 1594
    Adhoc 6566 295.554687 22 122.406250 2866
    View 808 66.421875 69 28.250000 301
    Proc 347 92.992187 13288 16.562500 35
    Trigger 74 41.492187 96 3.078125 10
    Check 32 0.679687 11 0.031250 2
    UsrTab 4 0.117187 37 0.000000 0

    I don’t know if this is really good or bad. One thing to note about our system is that we regularly reboot every weekend. I’m running this on a Wednesday. I wonder how bad it would get if we did not reboot.

  23. Hi Here are my numbers

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 12033 672.148437 34 605.484375 8925
    Proc 82 51.273437 18310 2.445312 8
    Prepared 23 0.867187 4068 0.085937 3
    Trigger 2 0.187500 177 0.000000 0
    View 10 1.031250 16 0.000000 0
    UsrTab 1 0.015625 20257 0.000000 0

    I just want to know the meaning of each columns also tell me these no.s are good or bad?
    How to cleanup cache?

  24. Running an active/active 2008R2 cluster, optimize for adhoc workloads not set…

    Instance 1

    Prepared 20210 3080.640625 12070 967.109375 6339
    Adhoc 12174 427.820312 13038 228.796875 5114
    Proc 1692 533.078125 1221659 46.812500 177
    Trigger 100 18.453125 568372 0.679687 8
    UsrTab 23 2.289062 2936 0.406250 3
    View 863 102.671875 132 0.101562 2
    Check 35 2.109375 24 0.093750 3

    SQL Min Mem 12288 SQL Max Mem 24576 Server Total Mem 32767 Server Available Mem 16384

    Instance 2

    Adhoc 663 39.757812 3 16.398437 319
    Proc 222 39.335937 47 15.421875 92
    Prepared 203 14.500000 53 8.585937 126
    Trigger 13 1.945312 3 0.937500 6
    Check 33 0.867187 2 0.460937 16
    UsrTab 10 0.804687 7 0.218750 1
    View 105 15.687500 4 0.000000 0

    SQL Min Mem 12288 SQL Max Mem 24576 Server Total Mem 32767 Server Available Mem 16384

    I’m not sure enabling this setting would do much for me right now, however, when I move to virtual servers and right size the memory this could make a big difference.

  25. SQL 2008 Stnd SP3 – Max server memory not set – total mem avail on the box: very little!!!

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Prepared 206 45.414062 1475 26.781250 67
    Adhoc 251 28.828125 227 23.687500 163
    Proc 205 50.789062 5740 3.062500 11
    UsrTab 2 0.234375 4 0.015625 1
    View 187 21.187500 10 0.000000 0
    Trigger 7 0.742187 17 0.000000 0
    Check 21 0.476562 276 0.000000 0

  26. sql 2008 SP3. 192gig memory. 8 cpu
    running sharepoint. and Adhoc is OFFFFFFF!

    Preprd 29186 5692.820312 1394 877.804687 2859
    Adhoc 45327 1837.726562 868 677.226562 14129
    Proc 5831 1954.671875 326949 143.578125 371
    Check 708 26.687500 2829 8.882812 239
    Trigger 87 8.257812 5496 1.062500 15
    View 3255 368.625000 4460 0.140625 2
    UsrTab 43 4.664062 5909 0.109375 1

  27. Oh, you’ll love this…

    Microsoft SQL Server 2008 R2 (SP1) – 10.50.2811.0 (X64)
    Apr 6 2012 01:59:29
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Prepared 49062 3353.921875 161 2885.164062 44042
    Adhoc 67883 4593.289062 94 2781.453125 43294
    Proc 2147 1099.031250 18476 26.843750 75
    Trigger 88 52.359375 4828 1.898437 3
    Check 23 0.632812 479 0.031250 1
    View 495 73.882812 1613 0.000000 0
    UsrTab 3 0.460937 3 0.000000 0

  28. This is from another server in my client’s environment (SQL 2012 RTM, 24GB RAM, Max memory not set)

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 57243 2264.148437 44 1375.453125 46200
    Prepared 11293 764.054687 113 30.851562 201
    Proc 433 449.390625 12026 7.218750 15
    UsrTab 4 0.078125 1963 0.000000 0
    View 1497 144.070312 202 0.000000 0
    Check 59 1.679687 350 0.000000 0
    Trigger 4 0.226562 96 0.000000 0

  29. CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Prepared 6656 2125.710937 267 940.234375 3601
    Proc 2520 880.835937 53527 78.257812 189
    Adhoc 971 130.570312 1749 14.578125 492
    Trigger 39 4.460937 798 0.132812 1
    UsrTab 29 2.992187 2344 0.109375 1
    Check 4 0.132812 12 0.000000 0
    View 1044 130.390625 296 0.000000 0

    Memory on server – 16384 (MB)
    Max memory to use – 2147483647

  30. Sorry meant to add….

    Microsoft SQL Server 2008 R2 (RTM) – 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

  31. Running: Windows Server 2008R2, SQL Server 2008R2
    Server: 8GB, 2 x 3.6 dual-core Xeons
    MaxMem: 6700 (per Glenn Berry’s recommended settings)

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 27480 2527.296875 367 1364.984375 7254
    Prepared 232 10.757812 2511 2.320312 47
    Proc 18 4.585937 10805 0.414062 1
    UsrTab 1 0.218750 1 0.218750 1
    View 28 2.257812 2 0.000000 0
    Trigger 1 0.109375 9 0.000000 0

  32. Server A Prepared 7182 1468.671875 973 520.390625 2365
    Server A Adhoc 11137 919.054687 79 237.960937 2105
    Server A Proc 3731 788.023437 11989 66.921875 407
    Server A Trigger 79 23.304687 51 3.867187 12
    Server A View 1870 218.617187 340 0.062500 2
    Server A Check 19 0.382812 25 0.000000 0
    Server A UsrTab 14 0.867187 1641 0.000000 0

    Server B Adhoc 39039 1003.750000 1 934.828125 38459
    Server B Prepared 405 50.656250 102 25.757812 215
    Server B Proc 162 43.273437 142 8.539062 35
    Server B Check 20 0.484375 8 0.257812 11
    Server B Trigger 3 0.242187 5 0.000000 0
    Server B View 457 43.468750 15 0.000000 0
    Server B UsrTab 1 0.218750 20 0.000000 0

  33. I had to modify the statement due to an arithmetic overflow:

    “avg(cast(usecounts as bigint)) AS [Avg Use Count]”

    Max server memory (MB) = 102,400
    Server memory = 128GB

    Adhoc 91248 3256.273437 9623 1729.835937 48732
    Prepared 12818 778.218750 57556 338.171875 5233
    Proc 1227 264.226562 2610902 4.390625 24
    Trigger 43 14.945312 1073403 0.140625 1
    UsrTab 1 0.218750 29 0.000000 0
    View 2287 181.140625 78648 0.000000 0
    Check 21 0.585937 14 0.000000 0

    Due to old code not making use of parameters, as also a lot of dynamic SQL … working on getting it sorted with new development, but historical code is still the major culprit. Will investigate the use of “optimized for ad hoc workloads”.

    Regards

  34. Hi Kimberly:
    Here’s what another blogger says about Optimize for Ad Hoc Queries:
    I can’t recommend “optimize for ad hoc workloads” as a blanket recommendation. While this can control the overall growth of the size of the plan cache, it doesn’t control growth of the count of entries in the plan cache. Since search time in a hash table is O(n) (it grows linearly with hash table size), this can become a big problem when you have a lot of ad hoc queries and a high query volume. Basically – you can end up performing enough plan stub lookups to cause performance problems.
    END of QUOTE.
    This is very confusing to me, being a developer trying to play DBA with my app to make it go faster. Can you elaborate in your gracious well defined style? Thanks.

    1. The bigger problem is that they’re letting SQL Server completely control their cache. And, it sounds like they’re letting a HUGE number of plans get into cache and stay in cache (creating these expensive lookups). I can’t say that I’ve seen this but that’s because I usually don’t allow SQL Server ultimate control over the plan cache. Typically I do more than just turn on optimize for adhoc workloads – I also clear the cache when a certain amount of the cache (in terms of GB) has been wasted by single use plans. I wrote about it here: Plan cache, adhoc workloads and clearing the single-use plan cache bloat (https://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/). There are SQL Server 2005 and SQL Server 2008+ versions of the code. Just make sure to modify the numbers as the samples are 10MB.

      So, if they were to check their cache every 30 mins and IF the cache is more than 2GB in single use plans – then, DBCC FREESYSTEMCACHE(‘SQLPlans’) – then, it probably wouldn’t get so out of control!

      Hope that helps!
      kt

  35. CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Prepared 15480 2716.585937 13838 1300.156250 6315
    Adhoc 2846 162.109375 6153 7.000000 134
    Proc 118 40.960937 641114 2.984375 8
    UsrTab 2 0.437500 1 0.437500 2
    View 898 87.328125 3034 0.078125 1
    Check 7 0.117187 63 0.031250 2
    Trigger 5 0.515625 1172 0.000000 0

    1. Missed out on additional information.
      08 R2 Standard
      Allocated 16384Gb
      SQL Capped – 12288
      Environment – VM

  36. MAX Memory 19.456 MB, Server memory 24 GB

    Adhoc 159733 750.181266 9 183.704376 134930
    Prepared 290 67.023437 83577 25.078125 119
    Proc 424 116.625000 1516600 4.906250 27
    UsrTab 9 0.695312 34242 0.242187 2
    Trigger 5 0.468750 190 0.070312 1
    View 251 20.765625 302 0.000000 0
    Check 19 0.554687 8 0.000000 0

  37. Kimberly,

    What is your thoughts on my data below from running your query?

    Adhoc 1990 2032.132812 371890 1016.984375 327
    Prepared 4655 652.632812 489745 35.843750 401
    Proc 121 26.960937 98574 2.742187 14
    Trigger 117 21.335937 117467 2.648437 8
    UsrTab 2 0.437500 2 0.218750 1
    View 191 17.507812 71 0.000000 0

    Thank you,
    Kevin

  38. Hi Kimberly,

    What a great article and do hope that its still active:).

    Here are our stats from out main PROD SQL 2012 SP1 CU4 box.

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 28621 3040.859375 13 2116.640625 17979
    Prepared 12058 1363.195312 39 421.406250 3540
    Proc 428 121.718750 149 26.789062 84
    Trigger 88 80.898437 7 24.445312 28
    Check 16 0.554687 2 0.257812 7
    View 318 45.562500 9 0.000000 0
    UsrTab 1 0.062500 15 0.000000 0

    Our main box has >60gb in ram and I just noticed that cache hit ratio stat dropped below 90% so
    I thought to dig a bit into things. It has been on my todo list to research and evaluate this sql
    server setting.

    Would you advise that i turn on this option given the above stats?

    Any advice appreciated and hope you have a nice day-ta

    Kind Regards,

    Matt

  39. Hi Kimberly

    Hope you can answer my question about Ad Hoc plans and the setting Forced Parameterization option in database. How will the SQL server figure out when Ad hoc is turned on and Forced Parameterization is set on the database. I have one database on one SQL Server 2008 R2 with 128 Gb ram and 8 Cpu’s. And right now the Ad hoc setting is off and the Forced Parameterization is on. But as you can see below I have alot off Adhoc Plans. So should I run with Ad Hoc wordload On and Forced Parameterization or Ad Hoc On and Forced Parameterization off.? I know if Forced Parameterization is on I can use Plan Guides to help with the Queries, but i’m not a big Fan off that. So can you help me with an answer.

    Best ragrads

    René Larsen
    Atea A/S

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 137281 4046.632812 3 2487.523437 92738
    Prepared 22582 4485.742187 7 2333.914062 15191
    Proc 65 46.742187 11869 6.945312 3
    UsrTab 11 0.578125 24194 0.000000 0
    View 117 11.335937 122 0.000000 0
    Trigger 16 8.195312 1007 0.000000 0

    1. Hey there René – This is really a difficult question to answer only with adhoc plan cache information. Generally, I recommend optimize for adhoc workloads as it reduces bloat. But, when there are lots of compiled plan stubs then searching a very large cache can get costly (which is why periodically clearing it is still necessary). As for forced parameterization, this is ONLY when you have ad hoc statements that have incredibly stable plans (checking query_hash and query_plan_hash). I did a full course about this on Pluralsight and really, I’d recommend checking this out so that you really understand both the pros and the cons of implementing these options. The course is called SQL Server: Optimizing Ad Hoc Statement Performance and here’s a link: http://pluralsight.com/training/Courses/Description/sqlserver-optimizing-adhoc-statement-performance.

      Enjoy!
      kt

  40. opinions ? This particular system is developed using Entity Framework, which I think accounts for the high % of ad-hoc plans.

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 16908 1317.054687 644 1068.632812 5457
    Prepared 7428 1888.429687 60240 366.750000 1483
    Proc 1352 632.976562 21246 112.218750 264
    View 1304 115.039062 610 2.265625 2
    Check 33 0.843750 12 0.351562 11
    Trigger 8 0.750000 22 0.273437 1
    UsrTab 5 0.500000 1736 0.000000 0

    1. No new opinions other than the general summary of the posts:
      * turn on optimize for adhoc workloads
      * setup a job to clear the cache when it gets above 1GB of single-use plans

      Also, consider moving stable statements to sp_executesql or stored procedures.

      hth,
      kt

  41. Adhoc 31653 2170.765625 200 1666.593750 8943
    Prepared 5372 821.140625 1866 307.125000 2085
    Trigger 416 163.484375 1095 37.851562 109
    Proc 855 218.320312 15359 11.132812 69
    Check 324 13.445312 687 0.492187 21
    View 1939 179.070312 54 0.351562 5
    UsrTab 4 0.500000 10 0.218750 1

  42. Hi Kiberly,

    Judging from my numbers below do you think I should enable or disable “optimize for adhoc workloads” ????

    CacheType Total Plans Total MBs AVG USE COUNT Total MBs – COUNT 1 Total Plans – COUNT 1
    Prepared 10055 2698.476562 6984 371.882812 2108
    Adhoc 7798 546.100097 897 172.021972 5564
    Proc 459 153.539062 1018 8.289062 22
    Trigger 17 16.367187 330 0.500000 1
    Check 24 0.632812 10 0.023437 1
    View 702 76.226562 13 0.000000 0
    UsrTab 1 0.218750 9 0.000000 0

    Regards,
    Geo

    1. I almost always recommend opt for adhoc. So, generally, yes. And, you’re definitely wasting some cache so this should help. But, that’s not the only step, you also need to regularly check/clear cache once it’s over a certain threshold (also described in this blog post category: plan cache). Commonly, cache is cleared at 2GB or 10%.

      Hope this helps!
      kt

  43. Hello,

    here is my, please can you post any link about memory managment and clean this adhoc query?

    Adhoc 123240 6487.890625 15 4903.195312 95735
    Prepared24748 1054.789062 382 649.148437 18354
    Proc 980 1467.273437 621112 35.445312 101
    View 1669 140.968750 677 2.203125 53
    Check 71 2.156250 734 0.640625 18
    Default 6 0.046875 5 0.000000 0
    Trigger 31 4.710937 81885 0.000000 0
    UsrTab 2 0.031250 1671 0.000000 0

    2. It definitely looks like you’re wasting cache. One of the questions you’ll need to figure out is how quickly cache is wasted. And, you should consider regularly clearing your adhoc plan cache. In this same category/series, I discuss ways to do this. Check out the other posts for information (and code/suggestions) for a job to check and clear cache based on a percentage or fixed MB/GB amount. This will only clear cache once you reach your defined threshold. A common setting is 2GB or over 10%.

      Hope that helps!
      kt

  44. Here’s a set of horrible numbers…
    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 149949 2218.752624 244 1503.689903 126903
    Prepared 10037 625.015625 3622 365.375000 6600
    Proc 489 628.000000 1901148 26.625000 27
    Trigger 18 1.390625 38167 0.320312 2
    View 1346 148.656250 2451 0.085937 2
    Check 4 0.062500 54 0.000000 0
    UsrTab 1 0.218750 20 0.000000 0

  45. When you say clearing the cache , do you mean dbcc freesystemcache(‘Clerkname’) or is it something else???

  46. this is my result for three of my server
    1)
    Prepared 347 231.007812 152 119.804687 194
    Proc 518 215.476562 44578 8.531250 33
    Adhoc 124 11.437500 175 8.445312 87
    Trigger 14 4.031250 32 0.773437 3
    UsrTab 36 1.804687 448 0.140625 2
    Check 1 0.023437 3 0.000000 0
    View 531 49.851562 157 0.000000 0

    2)
    Prepared 39774 2849.421875 955 932.296875 3823
    Adhoc 764 30.515625 924 10.640625 186
    Proc 63 12.390625 36 3.070312 14
    UsrTab 7 0.476562 2 0.000000 0
    View 893 79.687500 123 0.000000 0
    Check 4 0.062500 17 0.000000 0
    Trigger 1 0.070312 4 0.000000 0

    3)
    Prepared 5331 715.703125 132 234.000000 2045
    Adhoc 2205 141.359375 60 7.101562 126
    Proc 19 3.601562 20 2.726562 9
    View 176 15.640625 6 0.000000 0
    Check 2 0.031250 2 0.000000 0
    UsrTab 1 0.218750 2 0.000000 0

    1, 2 an 3 have SQL Server 2008 R2 Sp2
    server 1 have 12GB RAM and max memory is 8GB
    server 2 have 16GB RAM and max memory is 12GB
    server 3 have 16GB RAM and max memory is 12GB

    what do you thing???

  47. Hi Kimberly,

    Below are our results from our PROD server running on SQL 2008 SP3 w/ 64GB RAM. Any thoughts on whether our adhoc plans are excessive compared to overall available memory for the system. Also, we are considering upgrading to SQL 2014. What changes/improvements if any does it have to offer on this issue?

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 15563 4040.812500 3 2038.601562 8864
    Prepared 279 38.882812 110 21.570312 166
    Proc 764 434.937500 6153 7.078125 28
    Trigger 139 108.726562 1195 4.148437 11
    Check 19 0.554687 3 0.257812 8
    View 248 25.531250 5 0.000000 0
    UsrTab 1 0.218750 7 0.000000 0

    Thanks,
    Param

    1. My recommendations are for EVERY server. Turn of optimize for adhoc and then clear the plan cache [DBCC FREESYSTEMCACHE (‘SQL Plans’)] at 1, 2 or 4GB depending on how much memory you have on the machine. With 32GB or less, I’d clear at 1GB. With greater than 32GB and up to 128GB, I’d clear at 2GB. Over 128GB, I’d clear at 4GB. Check out the other posts in this same series for more information on how to do this with an agent job!

      Cheers,
      kt

  48. Prepared 21126 3447.054687 489 1689.710937 9605
    Adhoc 5313 119.886482 6 41.362716 3704
    Proc 48 28.726562 595350 3.796875 2
    Trigger 4 0.773437 104530 0.117187 1
    Check 7 0.164062 190675 0.000000 0
    View 15999 981.117187 16 0.000000 0

    This server creeps back to 1.5Gig of after 10 minutes of activity. Optimize is enabled, its a non-proc based Agile application.

    1. Yep! You too have this all-too-common problem on most systems today – especially those that use a lot of ad hoc / dynamically constructed SQL. You’ll definitely want to check the cache periodically and then clear your plan cache (since it’s predominantly single-use plans). Usually I check every 30 mins and clear at 1 or 2GB. Since you get 1.5 GB after 10 mins, you might want to check more frequently (say, every 10 mins) and clear at 1GB.

      hth,
      kt

  49. This is from a system that I just started working on. What really makes this horrible is that there’s something on the system (investigation in process) that clears the cache at 5:30 every morning and these stats (using your good query) are from 1:30PM on a HOLIDAY!!!! The 3rd party software associated with the server is pretty bad.

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    ——— ———– ———– ————- ———————– ————————-
    Adhoc 158955 2733.914062 6 2467.507812 137766
    Proc 241 64.757812 992 24.179687 53
    Prepared 1049 63.148437 2297 6.718750 98
    Trigger 43 9.296875 441 0.671875 7
    UsrTab 56 2.546875 5 0.570312 13
    Check 17 0.546875 4 0.289062 8
    View 181 20.906250 9 0.000000 0

    1. Yep! This is super common on most systems today – especially those that use a lot of ad hoc / dynamically constructed SQL. You’ll definitely want to check the cache periodically and then clear your plan cache (since it’s predominantly single-use plans). Usually I check every 30 mins and clear at 1 or 2GB.

      hth,
      kt

  50. Hi there Kimberly Tripp

    Was looking for a way to check the workload and found your page. I’m still pretty new to SQL so apologise if I ask something that’s simple. While executing your query I get this:

    Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type int. This Server is actually the one that worries me.

    It works on another one I have and looks like this:

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 5032 97.719879 5 79.344879 4554
    Proc 142 29.304687 1522 2.546875 10
    Prepared 141 12.367187 1756 0.750000 10
    Trigger 10 2.156250 131 0.226562 1
    View 39 3.476562 17 0.062500 1
    Check 2 0.031250 7 0.000000 0

    Thanks for your reply. Best regards from Switzerland

    1. I cleared the cache with DBCC FREEPROCCACHE and issued the statement again and now I’m getting a result :-) thanks for all the info on your page.

      CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
      Adhoc 4379 252.281250 24 129.398437 2567
      Prepared 968 102.718750 211 35.156250 328
      Proc 194 56.164062 669 4.117187 21
      Trigger 13 4.921875 696 0.390625 3
      UsrTab 101 3.148437 10 0.234375 7
      Check 28 0.796875 9 0.023437 1
      View 337 50.695312 5 0.000000 0

    2. Hey there Metin – Yes, you’ll need to change the data type so that you don’t get the overflow. Yes, depending on your numbers / size of cache – you could get an error. Converting to float also works. In addition to reviewing the amount of cache that’s being used by single-use plans, you’ll really want to ALSO clear the cache periodically so that you’re not just letting the cache grow and grow to the SQL max. Please check out the follow on posts for how to deal with plan cache bloat. This post is the most relevant: Plan cache, adhoc workloads and clearing the single-use plan cache bloat (https://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/).

      HTH,
      kt

  51. Had to cast to float, to run the script. Got numeric convertion errors

    SELECT objtype AS [CacheType]
    , count_big(*) AS [Total Plans]
    , sum(CAST(size_in_bytes AS FLOAT))/1024/1024 AS [Total MBs]
    , avg(CAST(usecounts AS FLOAT)) AS [Avg Use Count]
    , sum(CASE WHEN usecounts = 1 THEN CAST(size_in_bytes AS FLOAT) ELSE 0 END)/1024/1024 AS [Total MBs – USE Count 1]
    , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans – USE Count 1]
    FROM sys.dm_exec_cached_plans
    GROUP BY objtype
    ORDER BY [Total MBs – USE Count 1] DESC
    go

    This is from one of our 2012 instances. 12 cores, 200GB memory (Proc cache utilization is 10GB out of 16GB)

    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Prepared 9021 8577,90625 2034,60680634076 4903,71875 5444
    Adhoc 5996 1048,2890625 96,312541694463 830,6015625 4510
    Proc 374 202,09375 819344,721925134 9,8046875 30
    Check 39 1,15625 158,128205128205 0,5390625 15
    Default 3 0,0234375 5 0 0
    Trigger 41 26,984375 107244,951219512 0 0
    UsrTab 3 0,0859375 140594038 0 0
    View 1130 100,6796875 254,252212389381 0 0

    1. Hey there Kasper – Yes, depending on your numbers / size of cache – you could get an error. Converting to float works (although it does make it a bit less readable with good spacing ;-) ). In addition to reviewing the amount of cache that’s being used by single-use plans, you’ll really want to ALSO clear the cache periodically so that you’re not just letting the cache grow and grow to the SQL max. Please check out the follow on posts for how to deal with plan cache bloat. This post is the most relevant: Plan cache, adhoc workloads and clearing the single-use plan cache bloat (https://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/).

      HTH,
      kt

  52. My numbers are ( SQL 2008 R2)
    CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 12259 946.578125 86 484.5 7665
    Prepared 8750 1517.023437 1245 222.867187 1580
    Proc 302 134.015625 37802 20.28125 26
    View 772 69.976562 28 1.539062 23
    Check 11 0.171875 49 0 0
    Trigger 1 0.351562 21 0 0
    UsrTab 1 0.21875 7 0 0

    I have just turned on. I see if it makes a difference.
    Thanks

    1. Hey there Michael – Thanks for the numbers; you don’t even had a lot of cache right now (and you’ve turned on Opt for Ad Hoc) and you’re at half so this is probably good. But, you’ll really want to ALSO clear the cache periodically so that you’re not just letting the cache grow and grow to the SQL max. Please check out the follow on posts for how to deal with plan cache bloat. This post is the most relevant: Plan cache, adhoc workloads and clearing the single-use plan cache bloat (https://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/).

      HTH,
      kt

  53. CacheType Total Plans Total MBs Avg Use Count Total MBs – USE Count 1 Total Plans – USE Count 1
    Adhoc 144643 9039.492187 5 4405.406250 119650
    Prepared 1319 233.625000 5964 55.218750 359
    Proc 743 178.890625 57291 13.554687 56
    Trigger 127 39.843750 413092 4.195312 20
    Check 21 0.515625 996 0.140625 4
    UsrTab 6 0.796875 25 0.125000 2
    View 825 83.125000 109 0.000000 0

Comments are closed.

Other articles

Wow! Wow! Wow! THANK YOU!

I announced my retirement from SQL/tech here and your comments on my blog, on LinkedIn, and on Facebook were overwhelming and humbling! I’m so touched

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course