{"id":1019,"date":"2019-05-28T08:01:05","date_gmt":"2019-05-28T15:01:05","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1019"},"modified":"2020-06-12T14:19:02","modified_gmt":"2020-06-12T21:19:02","slug":"sql-server-plan-cache-limits","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/","title":{"rendered":"SQL Server Plan Cache Limits"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p>Last week on <a href=\"https:\/\/sqlperformance.com\/\">SQLPerformance.com<\/a> I blogged about <a href=\"https:\/\/sqlperformance.com\/2019\/05\/sql-plan\/perf-impact-adhoc-workload\">ad hoc workloads and how they impact performance<\/a>, noting plan cache bloat, and I had a comment asking about SQL Server plan cache limits.\u00a0 Kimberly mentions them in her classic post <a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/plan-cache-and-optimizing-for-adhoc-workloads\/\">Plan cache and optimizing for adhoc workloads<\/a>, but I thought I&#8217;d put the information into a blog post because I always have trouble finding the original references: <a href=\"https:\/\/docs.microsoft.com\/en-us\/previous-versions\/sql\/sql-server-2008\/ee343986(v=sql.100)\">Plan Caching in SQL Server 2008<\/a> (which is still accurate for SQL Server 2019, as far as I know) and <a href=\"https:\/\/docs.microsoft.com\/en-us\/previous-versions\/tn-archive\/cc293624(v=technet.10)\">Plan Cache Internals<\/a>.<\/p>\n<p>There are two limitations related to the plan cache:<\/p>\n<ul>\n<li>Number of entries<\/li>\n<li>Total size in MB (based on server memory)<\/li>\n<\/ul>\n<p>By default, the plan cache is limited to 160,036 total entries (40,009 entries per bucket), and size based on max server memory (for SQL Server 2008+ and SQL Server 2005 SP2):<\/p>\n<p style=\"padding-left: 40px;\">75% of visible target memory from 0 to 4GB<\/p>\n<p style=\"padding-left: 40px;\">+ 10% of visible target memory from 4GB to 64GB<\/p>\n<p style=\"padding-left: 40px;\">+ 5% of visible target memory &gt; 64GB<\/p>\n<p>Here&#8217;s the math:<\/p>\n<p>&nbsp;<\/p>\n<figure id=\"attachment_1021\" aria-describedby=\"caption-attachment-1021\" style=\"width: 500px\" class=\"wp-caption aligncenter\"><a href=\" plan \" class=\"broken_link\"><img fetchpriority=\"high\" decoding=\"async\" class=\"wp-image-1021\" src=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/05\/plan-cache-limits-300x284.jpg\" alt=\"SQL Server default plan cache size limits\" width=\"500\" height=\"474\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/05\/plan-cache-limits-300x284.jpg 300w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/05\/plan-cache-limits.jpg 703w\" sizes=\"(max-width: 500px) 100vw, 500px\" \/><\/a><figcaption id=\"caption-attachment-1021\" class=\"wp-caption-text\">SQL Server default plan cache size limits<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<p>If you have an ad hoc workload, you&#8217;ll often hit the max number of entries before you hit the space limit, particularly if you have the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/database-engine\/configure-windows\/optimize-for-ad-hoc-workloads-server-configuration-option?view=sql-server-2017\">optimize for ad hoc workloads server option<\/a> enabled, which stores the plan stub for an ad hoc query on initial execution, rather than the entire plan (the plan stub consumes less space, so this is an attempt to reduce bloat).<\/p>\n<p>For pure ad hoc workloads, if you&#8217;re seeing SOS_CACHESTORE spinlock contention, you can use <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017\">trace flag 174<\/a> to increase the number of entries to 160,001 per bucket (640,004 total), which is applicable for:<\/p>\n<ul>\n<li>SQL Server 2012 SP1 CU14+<\/li>\n<li>SQL Server 2012 SP2 CU5+<\/li>\n<li>SQL Server 2014 CU2+<\/li>\n<li>SQL Server 2014 SP1 CU1+<\/li>\n<li>SQL Server 2016+<\/li>\n<\/ul>\n<p><em>Very rarely<\/em>, I have seen instances where customers have used <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017\">trace flag 8032<\/a> to increase the total size of the plan cache.\u00a0 This trace flag uses the plan cache limits for SQL Server 2005 RTM:<\/p>\n<p style=\"padding-left: 40px;\">75% of visible target memory from 0 to 4GB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + 50% of visible target memory from 4GB to 64GB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + 25% of visible target memory &gt; 64GB<\/p>\n<p>The math is below, but note that this can significantly affect the memory available for the buffer pool and this is something I don&#8217;t typically recommend&#8230;<strong><span style=\"color: #ff0000;\">use with caution<\/span><\/strong>.<\/p>\n<figure id=\"attachment_1020\" aria-describedby=\"caption-attachment-1020\" style=\"width: 500px\" class=\"wp-caption aligncenter\"><img decoding=\"async\" class=\"wp-image-1020\" src=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/05\/plan-cache-limits-TF-8032-1024x614.jpg\" alt=\"SQL Server plan cache limits with TF 8032\" width=\"500\" height=\"300\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/05\/plan-cache-limits-TF-8032-1024x614.jpg 1024w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/05\/plan-cache-limits-TF-8032-300x180.jpg 300w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/05\/plan-cache-limits-TF-8032-768x461.jpg 768w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/05\/plan-cache-limits-TF-8032.jpg 1275w\" sizes=\"(max-width: 500px) 100vw, 500px\" \/><figcaption id=\"caption-attachment-1020\" class=\"wp-caption-text\">SQL Server plan cache limits with TF 8032<\/figcaption><\/figure>\n<p><strong>Summary<\/strong><\/p>\n<p>If you&#8217;re seeing plan cache bloat due to an ad hoc workload, take note of the space consumed by your plan cache and consider enabling the optimize for ad hoc setting, and possibly enabling TF 174.\u00a0 Both of those are short term fixes &#8211; ideally you look to parameterize stable queries that are executed most frequently to reduce the number of entries in the plan cache.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; Last week on SQLPerformance.com I blogged about ad hoc workloads and how they impact performance, noting plan cache bloat, and I had a comment asking about SQL Server plan cache limits.\u00a0 Kimberly mentions them in her classic post Plan cache and optimizing for adhoc workloads, but I thought I&#8217;d put the information into a [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[52],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQL Server Plan Cache Limits - Erin Stellato<\/title>\n<meta name=\"description\" content=\"There are SQL Server plan cache limits that affect how large the plan cache can be, and the number of entries is contains.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Plan Cache Limits - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"There are SQL Server plan cache limits that affect how large the plan cache can be, and the number of entries is contains.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2019-05-28T15:01:05+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-06-12T21:19:02+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/05\/plan-cache-limits-300x284.jpg\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/\",\"name\":\"SQL Server Plan Cache Limits - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2019-05-28T15:01:05+00:00\",\"dateModified\":\"2020-06-12T21:19:02+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"There are SQL Server plan cache limits that affect how large the plan cache can be, and the number of entries is contains.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Plan Cache Limits\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Plan Cache Limits - Erin Stellato","description":"There are SQL Server plan cache limits that affect how large the plan cache can be, and the number of entries is contains.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Plan Cache Limits - Erin Stellato","og_description":"There are SQL Server plan cache limits that affect how large the plan cache can be, and the number of entries is contains.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/","og_site_name":"Erin Stellato","article_published_time":"2019-05-28T15:01:05+00:00","article_modified_time":"2020-06-12T21:19:02+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/05\/plan-cache-limits-300x284.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/","name":"SQL Server Plan Cache Limits - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2019-05-28T15:01:05+00:00","dateModified":"2020-06-12T21:19:02+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"There are SQL Server plan cache limits that affect how large the plan cache can be, and the number of entries is contains.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"SQL Server Plan Cache Limits"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1019"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=1019"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1019\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1019"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1019"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1019"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}