{"id":1501,"date":"2020-11-03T10:05:00","date_gmt":"2020-11-03T16:05:00","guid":{"rendered":"http:\/\/www.sqlnuggets.com\/?p=1501"},"modified":"2021-12-23T15:33:15","modified_gmt":"2021-12-23T21:33:15","slug":"sql-server-crashes-when-hitting-80-percent-ram-usage","status":"publish","type":"post","link":"https:\/\/sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/","title":{"rendered":"SQL Server Crashes When Hitting 80 Percent RAM Usage"},"content":{"rendered":"\n
\"\"<\/figure>\n\n\n\n

I recently ran into a situation where a new SQL Server would crash hard every time it would get under a load. <\/p>\n\n\n\n

Here is a synopsis of what we were seeing:<\/p>\n\n\n\n

This is a physical server and has 512GB of RAM installed. We have SQL Server 2016 installed, and fully patched (SP2 CU15 at this time). When load testing the server, it would start throwing errors such as: <\/p>\n\n\n\n

\u201cFailed allocate pages: FAIL_PAGE_ALLOCATION\u201d<\/span><\/h3>\n\n\n\n

and <\/p>\n\n\n\n

\u201cThere is insufficient system memory in resource pool ‘default’ to run this query.\u201d<\/span><\/h3>\n\n\n\n

and<\/p>\n\n\n\n

“Failed to allocate BUFs”<\/span><\/h3>\n\n\n\n

It would then write a memory dump to the log, and in most cases the server would become completely unresponsive and would have to be rebooted.<\/p>\n\n\n\n

In our testing, we began experimenting with different Max Memory settings in SQL Server. We found that if we set Max Memory to 400GB everything ran fine, but if we set it to 425GB it would crash. We then honed it in to discover that 410GB of RAM usage was the magic number, once SQL Server hit that it would crash. Every time. <\/p>\n\n\n\n

A little bit of math showed us that this was also 80% of total server RAM. So as soon as SQL Server’s RAM usage hit 80%, the crash would happen. We did multiple checks on the hardware, and even engaged Dell support and couldn’t find any issues there. <\/p>\n\n\n\n

After a lengthy, and very unproductive, engagement with Microsoft support, I inadvertently discovered the problem. While looking at some settings in the SQL Configuration Manager I noticed that SQL Server Analysis Services (SSAS) had been installed on this box by mistake. Even though we were not using it, the SSAS service was active and running. I disabled the SSAS service and tried another load test on the server, and was shocked to find that this time everything worked correctly!<\/p>\n\n\n\n

We ran numerous tests after that, some with SSAS enabled and some with it disabled. We got the exact same results every time: SQL Server would run perfectly fine when SSAS was disabled, and crash at 80% RAM usage when SSAS was running. Every time.<\/p>\n\n\n\n

By this time our case had been escalated to tier 3 support at Microsoft, and they proved to be extremely <\/span><\/em>helpful. In reporting our findings regarding SSAS, here is what they were able to determine:<\/p>\n\n\n\n

In short, you are seeing this memory pressure issue when SSAS is running (even if it is not being used) because by default SSAS allocates 20% of physical RAM on the machine for its minimum working set. That combined with SQL Server’s Max Server Memory being above 80% of physical RAM and having LPIM enabled (which prevents the OS from paging this memory to free up more physical RAM) can lead to memory exhaustion at the OS level and result in the exact symptoms you described.<\/p><\/blockquote>\n\n\n\n

To summarize, our problem was a result of the combination of the following:<\/p>\n\n\n\n

  • SSAS was installed and running (when it shouldn’t have been)<\/li>
  • The default SSAS config is to take 20% of the server’s RAM<\/li>
  • SQL Server Max Memory was set above 80%.<\/li>
  • Lock Pages In Memory (LPIM) was enabled<\/li><\/ul>\n\n\n\n

    In our case, the solution was simple: Get rid of SSAS since we didn’t need it. Once we did that, everything ran fine and we haven’t had this problem since.<\/p>\n\n\n\n

    But What If I Need SSAS?<\/strong><\/p>\n\n\n\n

    If you do need SSAS on your SQL Server, there a few options:<\/p>\n\n\n\n

    • Don’t run SSAS and SQL Server on the same box. Microsoft even recommends having them separated. <\/li>
    • Do the math and figure out the 80% point and set SQL Server’s Max Memory below that.<\/li>
    • If you need more RAM for SQL Server, adjust SSAS’s Maximum Working Set to be less than 20%<\/li>
    • Disable Lock Pages In Memory (LPIM) so that SQL and SSAS and Windows will all play nicely together and share the RAM<\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"

      I recently ran into a situation where a new SQL Server would crash hard every time it would get under a load. Here is a synopsis of what we were …<\/p>\n","protected":false},"author":2,"featured_media":1503,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[68,49],"tags":[39,17],"class_list":["post-1501","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-configurations","category-errors","tag-internals","tag-server-administration"],"yoast_head":"\nSQL Server Crashes When Hitting 80 Percent RAM Usage - SQL Nuggets<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Crashes When Hitting 80 Percent RAM Usage - SQL Nuggets\" \/>\n<meta property=\"og:description\" content=\"I recently ran into a situation where a new SQL Server would crash hard every time it would get under a load. Here is a synopsis of what we were ...\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Nuggets\" \/>\n<meta property=\"article:published_time\" content=\"2020-11-03T16:05:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-12-23T21:33:15+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2020\/10\/car-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1240\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Eric Cobb\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@cfgears\" \/>\n<meta name=\"twitter:site\" content=\"@sqlnugg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Eric Cobb\" \/>\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\":\"Article\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-server-crashes-when-hitting-80-percent-ram-usage\\\/#article\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-server-crashes-when-hitting-80-percent-ram-usage\\\/\"},\"author\":{\"name\":\"Eric Cobb\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\"},\"headline\":\"SQL Server Crashes When Hitting 80 Percent RAM Usage\",\"datePublished\":\"2020-11-03T16:05:00+00:00\",\"dateModified\":\"2021-12-23T21:33:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-server-crashes-when-hitting-80-percent-ram-usage\\\/\"},\"wordCount\":644,\"commentCount\":5,\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-server-crashes-when-hitting-80-percent-ram-usage\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2020\\\/10\\\/car-1.jpg\",\"keywords\":[\"Internals\",\"Server Administration\"],\"articleSection\":[\"Configurations\",\"Errors\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-server-crashes-when-hitting-80-percent-ram-usage\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-server-crashes-when-hitting-80-percent-ram-usage\\\/\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-server-crashes-when-hitting-80-percent-ram-usage\\\/\",\"name\":\"SQL Server Crashes When Hitting 80 Percent RAM Usage - SQL Nuggets\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-server-crashes-when-hitting-80-percent-ram-usage\\\/#primaryimage\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-server-crashes-when-hitting-80-percent-ram-usage\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2020\\\/10\\\/car-1.jpg\",\"datePublished\":\"2020-11-03T16:05:00+00:00\",\"dateModified\":\"2021-12-23T21:33:15+00:00\",\"breadcrumb\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-server-crashes-when-hitting-80-percent-ram-usage\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-server-crashes-when-hitting-80-percent-ram-usage\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-server-crashes-when-hitting-80-percent-ram-usage\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2020\\\/10\\\/car-1.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2020\\\/10\\\/car-1.jpg\",\"width\":1920,\"height\":1240},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-server-crashes-when-hitting-80-percent-ram-usage\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Crashes When Hitting 80 Percent RAM Usage\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"name\":\"SQL Nuggets\",\"description\":\"Nuggets Of SQL Server Knowledge\",\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\\\/\\\/www.sqlnuggets.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\",\"name\":\"SQL Nuggets\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"width\":320,\"height\":54,\"caption\":\"SQL Nuggets\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/sqlnugg\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/ericcobb\\\/\"]},{\"@type\":\"Person\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\",\"name\":\"Eric Cobb\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"caption\":\"Eric Cobb\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/cfgears\"],\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/author\\\/eric-cobb\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Crashes When Hitting 80 Percent RAM Usage - SQL Nuggets","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":"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Crashes When Hitting 80 Percent RAM Usage - SQL Nuggets","og_description":"I recently ran into a situation where a new SQL Server would crash hard every time it would get under a load. Here is a synopsis of what we were ...","og_url":"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/","og_site_name":"SQL Nuggets","article_published_time":"2020-11-03T16:05:00+00:00","article_modified_time":"2021-12-23T21:33:15+00:00","og_image":[{"width":1920,"height":1240,"url":"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2020\/10\/car-1.jpg","type":"image\/jpeg"}],"author":"Eric Cobb","twitter_card":"summary_large_image","twitter_creator":"@cfgears","twitter_site":"@sqlnugg","twitter_misc":{"Written by":"Eric Cobb","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/#article","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/"},"author":{"name":"Eric Cobb","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a"},"headline":"SQL Server Crashes When Hitting 80 Percent RAM Usage","datePublished":"2020-11-03T16:05:00+00:00","dateModified":"2021-12-23T21:33:15+00:00","mainEntityOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/"},"wordCount":644,"commentCount":5,"publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/10\/car-1.jpg","keywords":["Internals","Server Administration"],"articleSection":["Configurations","Errors"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/","url":"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/","name":"SQL Server Crashes When Hitting 80 Percent RAM Usage - SQL Nuggets","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/#website"},"primaryImageOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/#primaryimage"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/10\/car-1.jpg","datePublished":"2020-11-03T16:05:00+00:00","dateModified":"2021-12-23T21:33:15+00:00","breadcrumb":{"@id":"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/#primaryimage","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/10\/car-1.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/10\/car-1.jpg","width":1920,"height":1240},{"@type":"BreadcrumbList","@id":"http:\/\/www.sqlnuggets.com\/sql-server-crashes-when-hitting-80-percent-ram-usage\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.sqlnuggets.com\/"},{"@type":"ListItem","position":2,"name":"SQL Server Crashes When Hitting 80 Percent RAM Usage"}]},{"@type":"WebSite","@id":"http:\/\/www.sqlnuggets.com\/#website","url":"http:\/\/www.sqlnuggets.com\/","name":"SQL Nuggets","description":"Nuggets Of SQL Server Knowledge","publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.sqlnuggets.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"http:\/\/www.sqlnuggets.com\/#organization","name":"SQL Nuggets","url":"http:\/\/www.sqlnuggets.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","width":320,"height":54,"caption":"SQL Nuggets"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/sqlnugg","https:\/\/www.linkedin.com\/in\/ericcobb\/"]},{"@type":"Person","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a","name":"Eric Cobb","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","url":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","caption":"Eric Cobb"},"sameAs":["https:\/\/x.com\/cfgears"],"url":"https:\/\/sqlnuggets.com\/author\/eric-cobb\/"}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/10\/car-1.jpg","jetpack_shortlink":"https:\/\/wp.me\/pdyDvE-od","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":897,"url":"https:\/\/sqlnuggets.com\/using-dbatools-powershell-module-to-set-maxdop-and-max-memory\/","url_meta":{"origin":1501,"position":0},"title":"Using dbatools PowerShell Module To Set MAXDOP And Max Memory","author":"Eric Cobb","date":"November 27, 2018","format":false,"excerpt":"As I continue my blog series on using\u00a0the\u00a0dbatools PowerShell module, today I want to discuss using it to set MAXDOP and Max Memory on your SQL Server.\u00a0 This builds directly off of my previous post on\u00a0Using dbatools PowerShell Module To Configure SQL Server, so if you haven't seen that one\u2026","rel":"","context":"In "PowerShell"","block_context":{"text":"PowerShell","link":"https:\/\/sqlnuggets.com\/category\/powershell\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":613,"url":"https:\/\/sqlnuggets.com\/notes-sql-2017-azure-install-using-load-balancer-availability-group-listener\/","url_meta":{"origin":1501,"position":1},"title":"Notes From A SQL 2017 Azure Install \u2013 Using A Load Balancer As An Availability Group Listener","author":"Eric Cobb","date":"November 9, 2017","format":false,"excerpt":"When setting up\u00a0SQL Server Availability Groups on Azure virtual machines, one of the requirements is that you use a load balancer for your Availability Group listener.\u00a0\u00a0The load balancer stores the IP address for the listener, and then you configure the cluster to use the IP address from the load balancer\u2026","rel":"","context":"In "Availability Groups"","block_context":{"text":"Availability Groups","link":"https:\/\/sqlnuggets.com\/category\/availability-groups\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/10\/Cloud-Azure.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/10\/Cloud-Azure.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/10\/Cloud-Azure.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/10\/Cloud-Azure.jpg?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":1307,"url":"https:\/\/sqlnuggets.com\/disable-telemetry-on-multiple-sql-servers-with-powershell\/","url_meta":{"origin":1501,"position":2},"title":"Disable Telemetry On Multiple SQL Servers With PowerShell","author":"Eric Cobb","date":"February 18, 2020","format":false,"excerpt":"Starting with SQL Server 2016, when you install SQL Server it also installs the CEIP (Customer Experience Improvement Program) Services, also known at Telemetry. In previous versions of SQL Server this was optional, but as of SQL Server 2016 it is automatically installed and enabled by default. Now whether this\u2026","rel":"","context":"In "Configurations"","block_context":{"text":"Configurations","link":"https:\/\/sqlnuggets.com\/category\/configurations\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/02\/binoculars-431488_1280.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/02\/binoculars-431488_1280.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/02\/binoculars-431488_1280.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/02\/binoculars-431488_1280.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/02\/binoculars-431488_1280.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":1350,"url":"https:\/\/sqlnuggets.com\/setting-up-database-mail-and-sql-agent-alerts-with-powershell\/","url_meta":{"origin":1501,"position":3},"title":"Setting Up Database Mail And SQL Agent Alerts With PowerShell","author":"Eric Cobb","date":"April 21, 2020","format":false,"excerpt":"As a DBA, you need to know when there's a problem on your SQL Servers. And while I highly recommend you use a full-fledged monitoring system, there are also some things you can set up on your SQL Servers so that they will tell you when certain things go wrong.\u2026","rel":"","context":"In "Configurations"","block_context":{"text":"Configurations","link":"https:\/\/sqlnuggets.com\/category\/configurations\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/04\/banner-1165979_1280.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/04\/banner-1165979_1280.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/04\/banner-1165979_1280.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/04\/banner-1165979_1280.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/04\/banner-1165979_1280.png?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":1615,"url":"https:\/\/sqlnuggets.com\/yikes-cannot-recover-the-master-database\/","url_meta":{"origin":1501,"position":4},"title":"Yikes! Cannot recover the master database!","author":"Eric Cobb","date":"May 11, 2021","format":false,"excerpt":"Recently, during patching for a 2019 SQL Server, the SQL services refused to start after the patching. It was throwing the following heart attack inducing error: Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. This is\u2026","rel":"","context":"In "Corruption"","block_context":{"text":"Corruption","link":"https:\/\/sqlnuggets.com\/category\/corruption\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/corrupt-147974_1280.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/corrupt-147974_1280.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/corrupt-147974_1280.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/corrupt-147974_1280.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/corrupt-147974_1280.png?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":93,"url":"https:\/\/sqlnuggets.com\/sql-server-maintenance-scripts\/","url_meta":{"origin":1501,"position":5},"title":"SQL Server Maintenance Scripts","author":"Eric Cobb","date":"July 9, 2015","format":false,"excerpt":"When it comes to everyday routine maintenance of your SQL Server databases, you can't beat Ola Hallengren's\u00a0SQL Server Maintenance Solution. \u00a0Focusing on\u00a0SQL Server Backups,\u00a0SQL Server Integrity Checks, and SQL Server Index and Statistics Maintenance, it\u00a0has pretty much become the go-to\u00a0maintenance solution for many SQL Server database administrators. \u00a0We have it\u2026","rel":"","context":"In "Tools"","block_context":{"text":"Tools","link":"https:\/\/sqlnuggets.com\/category\/tools\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/1501","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/comments?post=1501"}],"version-history":[{"count":5,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/1501\/revisions"}],"predecessor-version":[{"id":1696,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/1501\/revisions\/1696"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media\/1503"}],"wp:attachment":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media?parent=1501"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/categories?post=1501"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/tags?post=1501"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}