{"id":1623,"date":"2013-01-17T12:52:14","date_gmt":"2013-01-17T17:52:14","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/jonathan\/?p=1623"},"modified":"2017-04-13T12:55:28","modified_gmt":"2017-04-13T16:55:28","slug":"performance-tuning-sql-server-on-windows-server-2012","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/","title":{"rendered":"Performance Tuning SQL Server on Windows Server 2012"},"content":{"rendered":"<p>Properly configuring Windows and SQL Server to get the best performance from your server hardware is an important task for database administrators.\u00a0 There is a lot of information available online with different recommendations about how to configure your servers for the best performance.\u00a0 The challenge is knowing what recommendations are correct and what advice you should follow when setting up a new server or performance tuning the configuration of an existing server.<\/p>\n<p>Last week I received an email from a member of the community that had attended a user group meeting where the presenter was talking about configuration options for SQL Server on Windows Server 2012.\u00a0 The source of the information that was being presented is actually a Microsoft whitepaper titled <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/windows\/hardware\/jj248719.aspx\" target=\"_blank\">Performance Tuning Guidelines for Windows Server 2012<\/a> which has a section titled \u201cPerformance Tuning for OLTP Workloads\u201d.\u00a0 While the document mentions the TPC-E benchmark from the <a href=\"http:\/\/www.tpc.org\/tpce\/default.asp\" target=\"_blank\">Transaction Processing Performance Council<\/a>, what is not made clear in this document is that the recommendations in this section DO NOT apply to a majority of the SQL Server installations that exist in the world, they are strictly for servers trying to achieve a high score on the benchmark. \u00a0This section also existed in the whitepaper\u00a0<a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/windows\/hardware\/gg463392.aspx\" target=\"_blank\">Performance Tuning Guidelines for Windows Server 2008 R2<\/a>, but under the title \u201cPerformance Tuning for TPC-E Workload\u201d, which is more a accurate indication of what it applies to.<\/p>\n<p>If you want to know why this paper doesn\u2019t apply to most workloads, read on.\u00a0 If you want to know how to configure a SQL Server with general best practices, jump down to the \u201c<a href=\"#how\">How should I really configure SQL?<\/a>\u201d section at the end of this post.<\/p>\n<h2>Tuner Specials<\/h2>\n<p>The \u201cSQL Server Tunings for OLTP Workloads\u201d section in the whitepaper has a lot of items that I refer to as <em>tuner specials<\/em> because they exist for benchmarks like TPC-C and TPC-E, and they are documented because the benchmarks require full disclosure of the configuration before the results can be published.\u00a0 Most of the tuner specials can be found in KB article <a title=\"http:\/\/support.microsoft.com\/kb\/920093\" href=\"https:\/\/support.microsoft.com\/kb\/920093\">920093<\/a>.\u00a0 The startup configuration options mentioned in the whitepaper include:<\/p>\n<ul>\n<li><b>-x<\/b> : Disable SQL Server perfmon counters<\/li>\n<li><b>-T661<\/b>: Disable the ghost record removal process<\/li>\n<li><b>-T834<\/b>: Use Microsoft Windows large-page allocations for the buffer pool<\/li>\n<li><b>-T652<\/b>: Disable page-prefetching scans<\/li>\n<li><b>-T8744<\/b>: Disallow prefetch for ranges<\/li>\n<\/ul>\n<p>Of these, the only one that applies to general systems running SQL Server is \u2013T834, to use large-page allocations in servers with large amounts of RAM installed.\u00a0 Even this trace flag has some important considerations around its usage, since the buffer pool has to allocate its total size at startup from contiguous memory.\u00a0 If a contiguous allocation is not possible, the instance tries to allocate a smaller value until it finds a contiguous memory region to allocate from. This can significantly increase the instance startup time and is explained in further detail by Bob Ward in his blog post <a title=\"SQL Server and Large Pages Explained\" href=\"https:\/\/blogs.msdn.microsoft.com\/b\/psssql\/archive\/2009\/06\/05\/sql-server-and-large-pages-explained.aspx\" target=\"_blank\">SQL Server and Large Pages Explained<\/a>.<\/p>\n<p>While there are some workloads where using all these startup options can be beneficial, they don\u2019t apply to general SQL Server workloads.<\/p>\n<h2>Priority Boost?<\/h2>\n<p>The most interesting thing I found in the whitepaper was the recommendation to set the \u2018priority boost\u2019 value to 1 in sp_configure.\u00a0 This goes against <a href=\"https:\/\/support.microsoft.com\/kb\/319942\" target=\"_blank\">Microsoft\u2019s own recommendations<\/a> for this configuration option.\u00a0 When I am doing a <a href=\"https:\/\/www.sqlskills.com\/audit.asp\" target=\"_blank\">health check of a server<\/a> for a client, this configuration option is a big, red flashing light.\u00a0 When I see this set to 1 it usually means that I&#8217;m going to find a bunch more incorrectly configured options.\u00a0 Additionally this option has been <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms188709(v=sql.105).aspx\" target=\"_blank\">marked as deprecated<\/a> since SQL Server 2008 R2 released and will be removed from future releases of the product.\u00a0 This option is used for TPC-E to gain a slight advantage on a server where all unnecessary background services have been disabled, SQL Server is the only thing running, and the goal is strictly to obtain the best benchmark.<\/p>\n<p><a name=\"how\"><\/a><\/p>\n<h2>How should I really configure SQL?<\/h2>\n<p>My colleague Glenn Berry (<a href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\" target=\"_blank\">Blog<\/a> | <a href=\"https:\/\/twitter.com\/glennalanberry\" target=\"_blank\">Twitter<\/a>) knows a lot about performance tuning SQL Server hardware configurations, and he knows a lot about how to provision and configure a new SQL Server Instance, which he shared in his three part series on SimpleTalk (<a href=\"https:\/\/www.simple-talk.com\/sql\/database-administration\/provisioning-a-new-sql-server-instance-%e2%80%93-part-one\/\" target=\"_blank\">Part 1<\/a> | <a href=\"https:\/\/www.simple-talk.com\/sql\/database-administration\/provisioning-a-new-sql-server-instance-%e2%80%93-part-two\/\" target=\"_blank\">Part 2<\/a> | <a href=\"https:\/\/www.simple-talk.com\/sql\/database-administration\/provisioning-a-new-sql-server-instance-%e2%80%93-part-three\/\" target=\"_blank\">Part 3<\/a>) last year, and in a new <a href=\"https:\/\/www.pluralsight.com\/training\/Courses\/TableOfContents\/sqlserver-2012ic\" target=\"_blank\">Pluralsight online course<\/a> this year.\u00a0 If you&#8217;re looking for a guide to setting up a new SQL Server I\u2019d start off there.\u00a0 I published <a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-server-installation-checklist\/\" target=\"_blank\">my own checklist for new server configuration<\/a> back in 2010, but upon reviewing some of the recommendations I think I\u2019ll update that with a new revised checklist in a future post (for example I\u2019d leave hyper-threading turned on today, and set \u2018max degree of parallelism\u2019 based on the NUMA configuration and workload).<\/p>\n<p>We&#8217;ve been helping a lot of clients with upgrading to SQL Server 2012 on Windows Server 2012 &#8211; if you need help, <a href=\"mailto:request@SQLskills.com?subject=2012 tuning request\" target=\"_blank\">drop us a line<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Properly configuring Windows and SQL Server to get the best performance from your server hardware is an important task for database administrators.\u00a0 There is a lot of information available online with different recommendations about how to configure your servers for the best performance.\u00a0 The challenge is knowing what recommendations are correct and what advice you [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,19,38,39],"tags":[],"class_list":["post-1623","post","type-post","status-publish","format-standard","hentry","category-configuration","category-database-administration","category-sql-server-2008","category-sql-server-2012"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Performance Tuning SQL Server on Windows Server 2012 - Jonathan Kehayias<\/title>\n<meta name=\"description\" content=\"Information about performance tuning SQL Server on Windows best practices and what advice not to follow for normal OLTP\/mixed workloads.\" \/>\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\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Performance Tuning SQL Server on Windows Server 2012 - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"Information about performance tuning SQL Server on Windows best practices and what advice not to follow for normal OLTP\/mixed workloads.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2013-01-17T17:52:14+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:55:28+00:00\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\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\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/performance-tuning-sql-server-on-windows-server-2012\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/performance-tuning-sql-server-on-windows-server-2012\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"Performance Tuning SQL Server on Windows Server 2012\",\"datePublished\":\"2013-01-17T17:52:14+00:00\",\"dateModified\":\"2017-04-13T16:55:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/performance-tuning-sql-server-on-windows-server-2012\\\/\"},\"wordCount\":817,\"commentCount\":3,\"articleSection\":[\"Configuration\",\"Database Administration\",\"SQL Server 2008\",\"SQL Server 2012\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/performance-tuning-sql-server-on-windows-server-2012\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/performance-tuning-sql-server-on-windows-server-2012\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/performance-tuning-sql-server-on-windows-server-2012\\\/\",\"name\":\"Performance Tuning SQL Server on Windows Server 2012 - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"datePublished\":\"2013-01-17T17:52:14+00:00\",\"dateModified\":\"2017-04-13T16:55:28+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"description\":\"Information about performance tuning SQL Server on Windows best practices and what advice not to follow for normal OLTP\\\/mixed workloads.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/performance-tuning-sql-server-on-windows-server-2012\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/performance-tuning-sql-server-on-windows-server-2012\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/performance-tuning-sql-server-on-windows-server-2012\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Configuration\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/configuration\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Performance Tuning SQL Server on Windows Server 2012\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?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\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Performance Tuning SQL Server on Windows Server 2012 - Jonathan Kehayias","description":"Information about performance tuning SQL Server on Windows best practices and what advice not to follow for normal OLTP\/mixed workloads.","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\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/","og_locale":"en_US","og_type":"article","og_title":"Performance Tuning SQL Server on Windows Server 2012 - Jonathan Kehayias","og_description":"Information about performance tuning SQL Server on Windows best practices and what advice not to follow for normal OLTP\/mixed workloads.","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/","og_site_name":"Jonathan Kehayias","article_published_time":"2013-01-17T17:52:14+00:00","article_modified_time":"2017-04-13T16:55:28+00:00","author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"Performance Tuning SQL Server on Windows Server 2012","datePublished":"2013-01-17T17:52:14+00:00","dateModified":"2017-04-13T16:55:28+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/"},"wordCount":817,"commentCount":3,"articleSection":["Configuration","Database Administration","SQL Server 2008","SQL Server 2012"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/","name":"Performance Tuning SQL Server on Windows Server 2012 - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"datePublished":"2013-01-17T17:52:14+00:00","dateModified":"2017-04-13T16:55:28+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"description":"Information about performance tuning SQL Server on Windows best practices and what advice not to follow for normal OLTP\/mixed workloads.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/performance-tuning-sql-server-on-windows-server-2012\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Configuration","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/configuration\/"},{"@type":"ListItem","position":3,"name":"Performance Tuning SQL Server on Windows Server 2012"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?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\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1623","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=1623"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1623\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=1623"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=1623"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=1623"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}