{"id":1271,"date":"2017-07-05T09:00:22","date_gmt":"2017-07-05T16:00:22","guid":{"rendered":"https:\/\/bornsql.ca\/?p=1271"},"modified":"2017-07-03T09:26:31","modified_gmt":"2017-07-03T16:26:31","slug":"balanced-power-saving-t-sql-script","status":"publish","type":"post","link":"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/","title":{"rendered":"Balanced Power Saving T-SQL script"},"content":{"rendered":"

We can easily spend tens of thousands of dollars on core licences for SQL Server, and then we go and install the product on an operating system with the default Balanced Power Plan<\/em>, which is, well, idiotic.<\/p>\n

\"No!<\/a>
No! Don’t do this! Click “High performance” immediately! Exclamation mark!<\/figcaption><\/figure>\n

Imagine buying one of the fastest road cars money can buy (a Bugatti Veyron<\/a>), which used to cost US$2,700,000 for the Enterprise Edition<\/span> Super Sport Edition.<\/p>\n

To make this car hit the top speed of over 265 miles per hour, there is a special mode that must be entered when the car is at rest, by toggling a key to the left of the driver’s seat.<\/p>\n

Windows has the same setting. It’s in the Power Options<\/em> under Control Panel<\/em>, and for all servers, no matter what, it should be set to High Performance<\/strong>.<\/p>\n

Here’s a free T-SQL script I wrote that will check for you what the power settings are. We don’t always have desktop access to a server when we are checking diagnostics, but it’s good to know if performance problems can be addressed by a really simple fix that doesn’t require the vehicle to be at rest.<\/p>\n

(The script also respects your settings, so if you had xp_cmdshell<\/code> disabled, it’ll turn it off again when it’s done.)<\/p>\n

DECLARE @isCmdShellEnabled BIT;
\nDECLARE @isShowAdvanced BIT;
\nSELECT
\n@isCmdShellEnabled = CAST(value AS BIT)
\nFROM
\nsys.configurations
\nWHERE
\nname = 'xp_cmdshell';
\nSELECT
\n@isShowAdvanced = CAST(value AS BIT)
\nFROM
\nsys.configurations
\nWHERE
\nname = 'show advanced options';
\nIF(@isShowAdvanced = 0)
\nBEGIN
\nEXEC sp_configure 'show advanced options', 1;
\nRECONFIGURE;
\nEND;
\nIF(@isCmdShellEnabled = 0)
\nBEGIN
\nEXEC sp_configure 'xp_cmdshell', 1;
\nRECONFIGURE;
\nEND;
\n--Run xp_cmdshell to get power settings
\nEXEC xp_cmdshell 'powercfg \/list';
\n--Turn off 'xp_cmdshell'
\nIF(@isCmdShellEnabled = 0)
\nBEGIN
\nEXEC sp_configure 'xp_cmdshell', 0;
\nRECONFIGURE;
\nEND;
\n--Turn off 'show advanced options'
\nIF(@isShowAdvanced = 0)
\nBEGIN
\nEXEC sp_configure 'show advanced options', 0;
\nRECONFIGURE;
\nEND;<\/code><\/p>\n

On a server that is set correctly, this is the output. Notice that the High performance<\/em> option is active.<\/p>\n

\"All<\/a><\/p>\n

If a different power setting is active, talk to a server admin (maybe that\u2019s you) to change it to high performance.<\/p>\n

I’ve also put this script with my Max Server Memory script on GitHub<\/a>.<\/p>\n

If you have any other stories to share about throwing money away, find me on Twitter at @bornsql<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"

We can easily spend tens of thousands of dollars on core licences for SQL Server, and then we go and install the product on an operating system with the default Balanced Power Plan, which is, well, idiotic. Imagine buying one of the fastest road cars money can buy (a Bugatti Veyron), which used to cost… <\/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":[1633,60,1632,66],"class_list":["post-1271","post","type-post","status-publish","format-standard","hentry","category-general","tag-high-performance","tag-performance","tag-power","tag-t-sql"],"yoast_head":"\nBalanced Power Saving T-SQL 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\/balanced-power-saving-t-sql-script\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Balanced Power Saving T-SQL script - Born SQL\" \/>\n<meta property=\"og:description\" content=\"We can easily spend tens of thousands of dollars on core licences for SQL Server, and then we go and install the product on an operating system with the default Balanced Power Plan, which is, well, idiotic. Imagine buying one of the fastest road cars money can buy (a Bugatti Veyron), which used to cost… \" \/>\n<meta property=\"og:url\" content=\"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/\" \/>\n<meta property=\"og:site_name\" content=\"Born SQL\" \/>\n<meta property=\"article:published_time\" content=\"2017-07-05T16:00:22+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bornsql.ca\/wp-content\/uploads\/2017\/06\/Screenshot-2017-06-30-18.13.41-1024x344.png\" \/>\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\\\/balanced-power-saving-t-sql-script\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/balanced-power-saving-t-sql-script\\\/\"},\"author\":{\"name\":\"Randolph\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/#\\\/schema\\\/person\\\/df20853d6458bc0aca0d5f17202e608d\"},\"headline\":\"Balanced Power Saving T-SQL script\",\"datePublished\":\"2017-07-05T16:00:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/balanced-power-saving-t-sql-script\\\/\"},\"wordCount\":308,\"image\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/balanced-power-saving-t-sql-script\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/bornsql.ca\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/Screenshot-2017-06-30-18.13.41-1024x344.png\",\"keywords\":[\"High Performance\",\"Performance\",\"Power\",\"T-SQL\"],\"articleSection\":[\"General\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/balanced-power-saving-t-sql-script\\\/\",\"url\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/balanced-power-saving-t-sql-script\\\/\",\"name\":\"Balanced Power Saving T-SQL script - Born SQL\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/balanced-power-saving-t-sql-script\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/balanced-power-saving-t-sql-script\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/bornsql.ca\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/Screenshot-2017-06-30-18.13.41-1024x344.png\",\"datePublished\":\"2017-07-05T16:00:22+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/#\\\/schema\\\/person\\\/df20853d6458bc0aca0d5f17202e608d\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/balanced-power-saving-t-sql-script\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/bornsql.ca\\\/blog\\\/balanced-power-saving-t-sql-script\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/balanced-power-saving-t-sql-script\\\/#primaryimage\",\"url\":\"https:\\\/\\\/bornsql.ca\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/Screenshot-2017-06-30-18.13.41.png\",\"contentUrl\":\"https:\\\/\\\/bornsql.ca\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/Screenshot-2017-06-30-18.13.41.png\",\"width\":1204,\"height\":404,\"caption\":\"No!\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/bornsql.ca\\\/blog\\\/balanced-power-saving-t-sql-script\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/bornsql.ca\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Balanced Power Saving T-SQL 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":"Balanced Power Saving T-SQL 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\/balanced-power-saving-t-sql-script\/","og_locale":"en_US","og_type":"article","og_title":"Balanced Power Saving T-SQL script - Born SQL","og_description":"We can easily spend tens of thousands of dollars on core licences for SQL Server, and then we go and install the product on an operating system with the default Balanced Power Plan, which is, well, idiotic. Imagine buying one of the fastest road cars money can buy (a Bugatti Veyron), which used to cost… ","og_url":"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/","og_site_name":"Born SQL","article_published_time":"2017-07-05T16:00:22+00:00","og_image":[{"url":"https:\/\/bornsql.ca\/wp-content\/uploads\/2017\/06\/Screenshot-2017-06-30-18.13.41-1024x344.png","type":"","width":"","height":""}],"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\/balanced-power-saving-t-sql-script\/#article","isPartOf":{"@id":"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/"},"author":{"name":"Randolph","@id":"https:\/\/bornsql.ca\/#\/schema\/person\/df20853d6458bc0aca0d5f17202e608d"},"headline":"Balanced Power Saving T-SQL script","datePublished":"2017-07-05T16:00:22+00:00","mainEntityOfPage":{"@id":"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/"},"wordCount":308,"image":{"@id":"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/#primaryimage"},"thumbnailUrl":"https:\/\/bornsql.ca\/wp-content\/uploads\/2017\/06\/Screenshot-2017-06-30-18.13.41-1024x344.png","keywords":["High Performance","Performance","Power","T-SQL"],"articleSection":["General"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/","url":"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/","name":"Balanced Power Saving T-SQL script - Born SQL","isPartOf":{"@id":"https:\/\/bornsql.ca\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/#primaryimage"},"image":{"@id":"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/#primaryimage"},"thumbnailUrl":"https:\/\/bornsql.ca\/wp-content\/uploads\/2017\/06\/Screenshot-2017-06-30-18.13.41-1024x344.png","datePublished":"2017-07-05T16:00:22+00:00","author":{"@id":"https:\/\/bornsql.ca\/#\/schema\/person\/df20853d6458bc0aca0d5f17202e608d"},"breadcrumb":{"@id":"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/#primaryimage","url":"https:\/\/bornsql.ca\/wp-content\/uploads\/2017\/06\/Screenshot-2017-06-30-18.13.41.png","contentUrl":"https:\/\/bornsql.ca\/wp-content\/uploads\/2017\/06\/Screenshot-2017-06-30-18.13.41.png","width":1204,"height":404,"caption":"No!"},{"@type":"BreadcrumbList","@id":"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bornsql.ca\/"},{"@type":"ListItem","position":2,"name":"Balanced Power Saving T-SQL 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\/1271","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=1271"}],"version-history":[{"count":0,"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/posts\/1271\/revisions"}],"wp:attachment":[{"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/media?parent=1271"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/categories?post=1271"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bornsql.ca\/wp-json\/wp\/v2\/tags?post=1271"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}