{"id":1360,"date":"2017-08-30T09:00:10","date_gmt":"2017-08-30T16:00:10","guid":{"rendered":"https:\/\/bornsql.ca\/?p=1360"},"modified":"2022-08-01T16:04:42","modified_gmt":"2022-08-01T22:04:42","slug":"need-know-memory-limits-sql-server","status":"publish","type":"post","link":"https:\/\/bornsql.ca\/blog\/need-know-memory-limits-sql-server\/","title":{"rendered":"What you need to know about memory limits on SQL Server"},"content":{"rendered":"

Last week, I posted this statement on Twitter, along with a screen capture of the official Microsoft documentation:<\/p>\n

Reminder: Max Server Memory is *not* just for the buffer pool. The RAM limit Standard Edition can use *is* just for the buffer pool.<\/em><\/p><\/blockquote>\n

Joey D\u2019Antoni was quick to remind me that this only applies to SQL Server 2012 and higher, which I gratefully acknowledge. For the sake of this post, I am talking specifically about SQL Server 2012 and higher.<\/p>\n

Let\u2019s break this down.<\/p>\n

On all editions of SQL Server (Enterprise, Standard, Web, Express), there is a configuration setting called Max Server Memory. This value should not be left at the default 2.1 petabytes, because at that value SQL Server will use all the memory you allow it (plus a little bit more if it needs to), and once it has it, it doesn\u2019t release it back to the operating system without a fight.<\/p>\n

What does Max Server memory control, though? According to the documentation<\/a>, it\u2019s the buffer pool, compilation memory, all the caches including the plan cache, and a bunch of other stuff. Here\u2019s a quote:<\/p>\n

Max Server Memory controls the SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and clr memory (essentially any memory clerk found in sys.dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked server providers other than SQL Server, and any memory allocated by a non SQL Server DLL are not controlled by max server memory.<\/em><\/p><\/blockquote>\n

The main takeaway here is that Max Server Memory is not limited to the buffer pool. There\u2019s a lot of other stuff going on too.<\/p>\n

Now let\u2019s consider the RAM limit of all non-Enterprise editions of SQL Server, as specified in the license agreement. At the time of this writing, using Standard Edition as an example, the license has a RAM limit of 128GB, which is specifically the maximum memory available for the buffer pool only. (This clarification in the documentation occurred after the release of SQL Server 2016 Service Pack 1 last year, but the license limit has referred to the buffer pool since at least SQL Server 2012.)<\/p>\n

This means that in a server with Standard Edition and a lot of RAM, you could set the Max Server Memory configuration setting higher than 128GB comfortably, and SQL Server will use more than 128GB perfectly legally, because there’s a lot more to Max Server Memory than just the buffer pool.<\/p>\n

So when I read in forums and elsewhere that the license limit of 128GB for Standard Edition is the highest you can set Max Server Memory, this is demonstrably wrong.<\/p>\n

To make this more interesting, SQL Server 2016 and higher supports Columnstore and In-Memory OLTP in non-Enterprise editions anyway, which means that you can easily consume all the physical RAM on a server, even if your buffer pool is using the legal limit.<\/p>\n

Tell me your licensing story on Twitter at @bornsql<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"

Last week, I posted this statement on Twitter, along with a screen capture of the official Microsoft documentation: Reminder: Max Server Memory is *not* just for the buffer pool. The RAM limit Standard Edition can use *is* just for the buffer pool. Joey D\u2019Antoni was quick to remind me that this only applies to SQL… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[1835,21,84,60,152],"class_list":["post-1360","post","type-post","status-publish","format-standard","hentry","category-general","tag-buffer-pool","tag-compatibility","tag-max-server-memory","tag-performance","tag-standard-edition"],"yoast_head":"\nWhat you need to know about memory limits on SQL Server - Born SQL<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/bornsql.ca\/blog\/need-know-memory-limits-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What you need to know about memory limits on SQL Server - Born SQL\" \/>\n<meta property=\"og:description\" content=\"Last week, I posted this statement on Twitter, along with a screen capture of the official Microsoft documentation: Reminder: Max Server Memory is *not* just for the buffer pool. The RAM limit Standard Edition can use *is* just for the buffer pool. Joey D\u2019Antoni was quick to remind me that this only applies to SQL… \" \/>\n<meta property=\"og:url\" content=\"https:\/\/bornsql.ca\/blog\/need-know-memory-limits-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Born SQL\" \/>\n<meta property=\"article:published_time\" content=\"2017-08-30T16:00:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-01T22:04:42+00:00\" \/>\n<meta name=\"author\" content=\"Randolph\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Randolph\" \/>\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\":\"Article\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/need-know-memory-limits-sql-server\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/need-know-memory-limits-sql-server\\\/\"},\"author\":{\"name\":\"Randolph\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/#\\\/schema\\\/person\\\/df20853d6458bc0aca0d5f17202e608d\"},\"headline\":\"What you need to know about memory limits on SQL Server\",\"datePublished\":\"2017-08-30T16:00:10+00:00\",\"dateModified\":\"2022-08-01T22:04:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/need-know-memory-limits-sql-server\\\/\"},\"wordCount\":508,\"keywords\":[\"Buffer Pool\",\"Compatibility\",\"Max Server Memory\",\"Performance\",\"Standard Edition\"],\"articleSection\":[\"General\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/need-know-memory-limits-sql-server\\\/\",\"url\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/need-know-memory-limits-sql-server\\\/\",\"name\":\"What you need to know about memory limits on SQL Server - Born SQL\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/#website\"},\"datePublished\":\"2017-08-30T16:00:10+00:00\",\"dateModified\":\"2022-08-01T22:04:42+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/#\\\/schema\\\/person\\\/df20853d6458bc0aca0d5f17202e608d\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/need-know-memory-limits-sql-server\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/bornsql.ca\\\/blog\\\/need-know-memory-limits-sql-server\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/need-know-memory-limits-sql-server\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/bornsql.ca\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What you need to know about memory limits on SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/#website\",\"url\":\"https:\\\/\\\/bornsql.ca\\\/\",\"name\":\"Born SQL\",\"description\":\"A blog about the Microsoft Data Platform\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/bornsql.ca\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/#\\\/schema\\\/person\\\/df20853d6458bc0aca0d5f17202e608d\",\"name\":\"Randolph\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/e464dca1b55497d15e725afa9728080478c391a7492d0065a7fbeb1d456a1986?s=96&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/e464dca1b55497d15e725afa9728080478c391a7492d0065a7fbeb1d456a1986?s=96&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/e464dca1b55497d15e725afa9728080478c391a7492d0065a7fbeb1d456a1986?s=96&r=g\",\"caption\":\"Randolph\"},\"sameAs\":[\"https:\\\/\\\/bornsql.ca\"],\"url\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/author\\\/bornsql\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"What you need to know about memory limits on SQL Server - Born SQL","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:\/\/bornsql.ca\/blog\/need-know-memory-limits-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"What you need to know about memory limits on SQL Server - Born SQL","og_description":"Last week, I posted this statement on Twitter, along with a screen capture of the official Microsoft documentation: Reminder: Max Server Memory is *not* just for the buffer pool. The RAM limit Standard Edition can use *is* just for the buffer pool. Joey D\u2019Antoni was quick to remind me that this only applies to SQL… ","og_url":"https:\/\/bornsql.ca\/blog\/need-know-memory-limits-sql-server\/","og_site_name":"Born SQL","article_published_time":"2017-08-30T16:00:10+00:00","article_modified_time":"2022-08-01T22:04:42+00:00","author":"Randolph","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Randolph","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/bornsql.ca\/blog\/need-know-memory-limits-sql-server\/#article","isPartOf":{"@id":"https:\/\/bornsql.ca\/blog\/need-know-memory-limits-sql-server\/"},"author":{"name":"Randolph","@id":"https:\/\/bornsql.ca\/#\/schema\/person\/df20853d6458bc0aca0d5f17202e608d"},"headline":"What you need to know about memory limits on SQL Server","datePublished":"2017-08-30T16:00:10+00:00","dateModified":"2022-08-01T22:04:42+00:00","mainEntityOfPage":{"@id":"https:\/\/bornsql.ca\/blog\/need-know-memory-limits-sql-server\/"},"wordCount":508,"keywords":["Buffer Pool","Compatibility","Max Server Memory","Performance","Standard Edition"],"articleSection":["General"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/bornsql.ca\/blog\/need-know-memory-limits-sql-server\/","url":"https:\/\/bornsql.ca\/blog\/need-know-memory-limits-sql-server\/","name":"What you need to know about memory limits on SQL Server - Born SQL","isPartOf":{"@id":"https:\/\/bornsql.ca\/#website"},"datePublished":"2017-08-30T16:00:10+00:00","dateModified":"2022-08-01T22:04:42+00:00","author":{"@id":"https:\/\/bornsql.ca\/#\/schema\/person\/df20853d6458bc0aca0d5f17202e608d"},"breadcrumb":{"@id":"https:\/\/bornsql.ca\/blog\/need-know-memory-limits-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bornsql.ca\/blog\/need-know-memory-limits-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/bornsql.ca\/blog\/need-know-memory-limits-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bornsql.ca\/"},{"@type":"ListItem","position":2,"name":"What you need to know about memory limits on SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/bornsql.ca\/#website","url":"https:\/\/bornsql.ca\/","name":"Born SQL","description":"A blog about the Microsoft Data Platform","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/bornsql.ca\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/bornsql.ca\/#\/schema\/person\/df20853d6458bc0aca0d5f17202e608d","name":"Randolph","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/e464dca1b55497d15e725afa9728080478c391a7492d0065a7fbeb1d456a1986?s=96&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/e464dca1b55497d15e725afa9728080478c391a7492d0065a7fbeb1d456a1986?s=96&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/e464dca1b55497d15e725afa9728080478c391a7492d0065a7fbeb1d456a1986?s=96&r=g","caption":"Randolph"},"sameAs":["https:\/\/bornsql.ca"],"url":"https:\/\/bornsql.ca\/blog\/author\/bornsql\/"}]}},"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/posts\/1360","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/comments?post=1360"}],"version-history":[{"count":0,"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/posts\/1360\/revisions"}],"wp:attachment":[{"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/media?parent=1360"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/categories?post=1360"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/tags?post=1360"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}