{"id":4442,"date":"2014-12-09T22:15:11","date_gmt":"2014-12-10T06:15:11","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4442"},"modified":"2017-04-13T09:52:16","modified_gmt":"2017-04-13T16:52:16","slug":"problems-lots-server-memory","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/","title":{"rendered":"Problems from having lots of server memory"},"content":{"rendered":"<p>A month ago I kicked off a <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/survey-much-server-memory-largest-machine\/\" target=\"_blank\">survey<\/a> asking how much memory is installed on your largest server that&#8217;s running SQL Server. Thank you to everyone that responded.<\/p>\n<p>Here are the results:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2014\/12\/memory.jpg\"><img fetchpriority=\"high\" decoding=\"async\" class=\"alignleft size-full wp-image-4443\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2014\/12\/memory.jpg\" alt=\"memory\" width=\"578\" height=\"342\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2014\/12\/memory.jpg 578w, https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2014\/12\/memory-300x177.jpg 300w\" sizes=\"(max-width: 578px) 100vw, 578px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>The &#8220;other&#8221; values are:<\/p>\n<ul>\n<li>3 more for the &#8216;128 GB or more, but less than 256 GB&#8217; count<\/li>\n<li>1 more for the &#8216;Less than 16 GB&#8217; count<\/li>\n<li>One poor soul who only has 512 MB in their server!<\/li>\n<\/ul>\n<p>This is very interesting:<\/p>\n<ul>\n<li>I expected the majority of servers to fall into the middle of the range (around 128GB), and it&#8217;s actually only 37% that fit into the 64 GB to 256 GB range.<\/li>\n<li>I&#8217;m surprised at the percentage of servers (41%) of servers with 256 GB or more.<\/li>\n<li>I didn&#8217;t know what percentage would have more than 1 TB, so almost 10% is really cool to see.<\/li>\n<\/ul>\n<p>So what do these results mean? Well, the number of servers out there with lots (more than 128GB) of memory is more than half of all respondents. The more memory you have, the more important it is that you make sure that the memory is being used efficiently and that you&#8217;re not wasting space in the buffer pool (see <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/\" target=\"_blank\">here<\/a>) and that you&#8217;re not churning the buffer pool with poor query plans causing lots of reads (see <a href=\"http:\/\/sqlperformance.com\/2014\/06\/io-subsystem\/knee-jerk-waits-pageiolatch-sh\" target=\"_blank\">here<\/a>).<\/p>\n<p>What other things could be problems with large amounts of memory?<\/p>\n<ul>\n<li>Shutting down the instance. This will checkpoint all the databases, which could take quite a long time (minutes to hours) if suddenly all databases have lots of dirty pages that all need to be flushed out to disk. This can eat into your maintenance window, if you&#8217;re shutting down to install an SP or a CU.<\/li>\n<li>Starting up the instance. If the server&#8217;s POST checks memory, the more memory you have, the longer that will take. This can eat into your allowable downtime if a crash occurs.<\/li>\n<li>Allocating\u00a0the buffer pool. We&#8217;ve worked with clients with terabyte+ buffer pools where they\u00a0hit a bug on 2008 R2 (also in 2008 and 2012) around NUMA memory allocations that would cause SQL Server to take many minutes to start up. That bug has been fixed in all affected versions and you can read about in <a href=\"https:\/\/support.microsoft.com\/kb\/2819662\" target=\"_blank\">KB 2819662<\/a>.<\/li>\n<li>Warming up the buffer pool. Assuming you don&#8217;t hit the memory allocation problem above, how do you warm up such a large buffer pool so that you&#8217;re not waiting a long time for your &#8216;working set&#8217; of data file pages to be memory resident? One solution is to analyze your buffer pool when it&#8217;s warm, to figure out which tables and indexes are in memory, and then write some scripts that will read much of that data into memory quickly\u00a0as part of starting up the instance. For one of the same customers that hit the allocation bug above, doing this\u00a0produced a big boost in getting to the steady-state workload performance compared to waiting for the buffer pool to warm up naturally.<\/li>\n<li>Complacency. With a large amount of memory available, there might be a tendency to slacken off proactively looking for unused and missing index tuning opportunities or plan cache bloat or wasted buffer pool space (I mentioned above), thinking that having all that memory will be more forgiving. Don&#8217;t fall into this trap. If one of these things becomes such a problem that it&#8217;s noticeable on your server with lots of memory, it&#8217;s a *big* problem that may be harder to get under control quickly.<\/li>\n<li>Disaster recovery. If you&#8217;ve got lots of memory, it probably means your databases are getting larger. You need to start considering the need for multiple filegroups to allow small, targeted restores for fast disaster recovery. This may also mean you need to think about breaking up large tables, using partitioning for instance, or archiving old, unused data so that tables don&#8217;t become unwieldy.<\/li>\n<\/ul>\n<p>Adding more memory is one of the easiest ways to alleviate some performance issues (as a band-aid, or seemingly risk-free temporary fix), but don&#8217;t think it&#8217;s a simple thing to just max out the server memory and then forget about it. As you can see, more memory leads to more potential problems,\u00a0and these are just a few things that spring to mind as I&#8217;m sitting in the back of class here in Sydney.<\/p>\n<p>Be careful out there!<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A month ago I kicked off a survey asking how much memory is installed on your largest server that&#8217;s running SQL Server. Thank you to everyone that responded. Here are the results: &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; The &#8220;other&#8221; values are: 3 more for the &#8216;128 GB or more, [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,60,66],"tags":[],"class_list":["post-4442","post","type-post","status-publish","format-standard","hentry","category-buffer-pool","category-memory","category-performance-tuning"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Problems from having lots of server memory - Paul S. Randal<\/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:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Problems from having lots of server memory - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"A month ago I kicked off a survey asking how much memory is installed on your largest server that&#8217;s running SQL Server. Thank you to everyone that responded. Here are the results: &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; The &#8220;other&#8221; values are: 3 more for the &#8216;128 GB or more, [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2014-12-10T06:15:11+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:52:16+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2014\/12\/memory.jpg\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/\",\"name\":\"Problems from having lots of server memory - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2014\/12\/memory.jpg\",\"datePublished\":\"2014-12-10T06:15:11+00:00\",\"dateModified\":\"2017-04-13T16:52:16+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2014\/12\/memory.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2014\/12\/memory.jpg\",\"width\":578,\"height\":342},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Problems from having lots of server memory\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Problems from having lots of server memory - Paul S. Randal","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\/paul\/problems-lots-server-memory\/","og_locale":"en_US","og_type":"article","og_title":"Problems from having lots of server memory - Paul S. Randal","og_description":"A month ago I kicked off a survey asking how much memory is installed on your largest server that&#8217;s running SQL Server. Thank you to everyone that responded. Here are the results: &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; The &#8220;other&#8221; values are: 3 more for the &#8216;128 GB or more, [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/","og_site_name":"Paul S. Randal","article_published_time":"2014-12-10T06:15:11+00:00","article_modified_time":"2017-04-13T16:52:16+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2014\/12\/memory.jpg","type":"","width":"","height":""}],"author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/","name":"Problems from having lots of server memory - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2014\/12\/memory.jpg","datePublished":"2014-12-10T06:15:11+00:00","dateModified":"2017-04-13T16:52:16+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2014\/12\/memory.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2014\/12\/memory.jpg","width":578,"height":342},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/problems-lots-server-memory\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Problems from having lots of server memory"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4442","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4442"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4442\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4442"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4442"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4442"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}