{"id":872,"date":"2016-11-22T09:00:13","date_gmt":"2016-11-22T16:00:13","guid":{"rendered":"https:\/\/bornsql.ca\/?p=872"},"modified":"2016-11-28T00:22:21","modified_gmt":"2016-11-28T07:22:21","slug":"updated-max-server-memory-script","status":"publish","type":"post","link":"https:\/\/bornsql.ca\/blog\/updated-max-server-memory-script\/","title":{"rendered":"Updated Max Server Memory Script"},"content":{"rendered":"<p><a href=\"https:\/\/bornsql.ca\/2016\/08\/max-server-memory-revisited\/\">Earlier this year<\/a>\u00a0I released a free\u00a0<a href=\"https:\/\/github.com\/bornsql\/scripts\/blob\/master\/max_server_memory.sql\">T-SQL script<\/a> that will calculate the correct amount of RAM you should allocate to SQL Server, assuming it is a standalone instance.<\/p>\n<p>After attending the PASS Summit in Seattle in October, I visited the SQL Server Tiger team&#8217;s <a href=\"https:\/\/github.com\/Microsoft\/tigertoolbox\/\">GitHub repository<\/a>\u00a0and discovered something similar, but not quite the same, in the <em>Maintenance Solution<\/em> folder.<\/p>\n<p>I have taken the best ideas from their <a href=\"https:\/\/github.com\/Microsoft\/tigertoolbox\/blob\/master\/MaintenanceSolution\/0_database_server_options.sql\">Database Server Options<\/a> script and merged them into my Max Server Memory Calculator script.<\/p>\n<p><strong>New Features<\/strong><\/p>\n<p>The SQL Server <em>thread stack<\/em> is now taken into account. This value depends on the CPU architecture (32-bit, or x64 \/ IA64) and the maximum worker threads configured for the SQL Server instance.<\/p>\n<p>On my 64-bit laptop with 16GB RAM, the new recommended amount for Max Server Memory has dropped from 11,264 MB to 10,112 MB (1,125 MB of RAM is now reserved for the thread stack).<\/p>\n<p><strong>Improvements<\/strong><\/p>\n<p>By default, the generated script will enable <code>show advanced options<\/code>\u00a0before trying to set the <code>max server memory (MB)<\/code> value.<\/p>\n<p>The <code>@ProductVersion<\/code> parameter uses a new method to calculate the major SQL Server version. \u00a0Previously it was a hack based on the string returned by the\u00a0<code>@@VERSION<\/code>\u00a0function, but now it uses the <code>@@MICROSOFTVERSION<\/code> function.<\/p>\n<p>This code is also from the Tiger team&#8217;s repository, and I&#8217;m sharing it here because I think it&#8217;s pretty clever how it works.<\/p>\n<p><code>-- Get SQL Server Major Version<br \/>\nSELECT\u00a0CONVERT(INT, (@@MICROSOFTVERSION \/ 0x1000000) &amp; 0xFF);<\/code><\/p>\n<p>I have also added a note on the <a href=\"\/memory\/\">Max Server Memory Matrix<\/a> page to note that the script now accounts for the thread stack.<\/p>\n<p>I hope you enjoy this new version of the script. If you have any comments or suggestions, please contact me on Twitter at <a href=\"https:\/\/twitter.com\/bornsql\">@bornsql<\/a> .<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Earlier this year\u00a0I released a free\u00a0T-SQL script that will calculate the correct amount of RAM you should allocate to SQL Server, assuming it is a standalone instance. After attending the PASS Summit in Seattle in October, I visited the SQL Server Tiger team&#8217;s GitHub repository\u00a0and discovered something similar, but not quite the same, in the&hellip;&nbsp;<\/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":[38,124,84,66],"class_list":["post-872","post","type-post","status-publish","format-standard","hentry","category-general","tag-community","tag-configuration","tag-max-server-memory","tag-t-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Updated Max Server Memory Script - 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\/updated-max-server-memory-script\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Updated Max Server Memory Script - Born SQL\" \/>\n<meta property=\"og:description\" content=\"Earlier this year\u00a0I released a free\u00a0T-SQL script that will calculate the correct amount of RAM you should allocate to SQL Server, assuming it is a standalone instance. After attending the PASS Summit in Seattle in October, I visited the SQL Server Tiger team&#8217;s GitHub repository\u00a0and discovered something similar, but not quite the same, in the&hellip;&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bornsql.ca\/blog\/updated-max-server-memory-script\/\" \/>\n<meta property=\"og:site_name\" content=\"Born SQL\" \/>\n<meta property=\"article:published_time\" content=\"2016-11-22T16:00:13+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-11-28T07:22:21+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=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/updated-max-server-memory-script\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/updated-max-server-memory-script\\\/\"},\"author\":{\"name\":\"Randolph\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/#\\\/schema\\\/person\\\/df20853d6458bc0aca0d5f17202e608d\"},\"headline\":\"Updated Max Server Memory Script\",\"datePublished\":\"2016-11-22T16:00:13+00:00\",\"dateModified\":\"2016-11-28T07:22:21+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/updated-max-server-memory-script\\\/\"},\"wordCount\":274,\"keywords\":[\"Community\",\"Configuration\",\"Max Server Memory\",\"T-SQL\"],\"articleSection\":[\"General\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/updated-max-server-memory-script\\\/\",\"url\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/updated-max-server-memory-script\\\/\",\"name\":\"Updated Max Server Memory Script - Born SQL\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/#website\"},\"datePublished\":\"2016-11-22T16:00:13+00:00\",\"dateModified\":\"2016-11-28T07:22:21+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/#\\\/schema\\\/person\\\/df20853d6458bc0aca0d5f17202e608d\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/updated-max-server-memory-script\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/bornsql.ca\\\/blog\\\/updated-max-server-memory-script\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/updated-max-server-memory-script\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/bornsql.ca\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Updated Max Server Memory Script\"}]},{\"@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":"Updated Max Server Memory Script - 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\/updated-max-server-memory-script\/","og_locale":"en_US","og_type":"article","og_title":"Updated Max Server Memory Script - Born SQL","og_description":"Earlier this year\u00a0I released a free\u00a0T-SQL script that will calculate the correct amount of RAM you should allocate to SQL Server, assuming it is a standalone instance. After attending the PASS Summit in Seattle in October, I visited the SQL Server Tiger team&#8217;s GitHub repository\u00a0and discovered something similar, but not quite the same, in the&hellip;&nbsp;","og_url":"https:\/\/bornsql.ca\/blog\/updated-max-server-memory-script\/","og_site_name":"Born SQL","article_published_time":"2016-11-22T16:00:13+00:00","article_modified_time":"2016-11-28T07:22:21+00:00","author":"Randolph","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Randolph","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/bornsql.ca\/blog\/updated-max-server-memory-script\/#article","isPartOf":{"@id":"https:\/\/bornsql.ca\/blog\/updated-max-server-memory-script\/"},"author":{"name":"Randolph","@id":"https:\/\/bornsql.ca\/#\/schema\/person\/df20853d6458bc0aca0d5f17202e608d"},"headline":"Updated Max Server Memory Script","datePublished":"2016-11-22T16:00:13+00:00","dateModified":"2016-11-28T07:22:21+00:00","mainEntityOfPage":{"@id":"https:\/\/bornsql.ca\/blog\/updated-max-server-memory-script\/"},"wordCount":274,"keywords":["Community","Configuration","Max Server Memory","T-SQL"],"articleSection":["General"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/bornsql.ca\/blog\/updated-max-server-memory-script\/","url":"https:\/\/bornsql.ca\/blog\/updated-max-server-memory-script\/","name":"Updated Max Server Memory Script - Born SQL","isPartOf":{"@id":"https:\/\/bornsql.ca\/#website"},"datePublished":"2016-11-22T16:00:13+00:00","dateModified":"2016-11-28T07:22:21+00:00","author":{"@id":"https:\/\/bornsql.ca\/#\/schema\/person\/df20853d6458bc0aca0d5f17202e608d"},"breadcrumb":{"@id":"https:\/\/bornsql.ca\/blog\/updated-max-server-memory-script\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bornsql.ca\/blog\/updated-max-server-memory-script\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/bornsql.ca\/blog\/updated-max-server-memory-script\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bornsql.ca\/"},{"@type":"ListItem","position":2,"name":"Updated Max Server Memory Script"}]},{"@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\/872","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=872"}],"version-history":[{"count":0,"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/posts\/872\/revisions"}],"wp:attachment":[{"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/media?parent=872"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/categories?post=872"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/tags?post=872"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}