Identify SQL Servers with inefficient power plans using Policy Based Management

By:   |   Comments (8)   |   Related: > Performance Tuning


A lot of people are coming to the realization that their CPU performance may be hindered due to a poor default (and recommended) setting in Windows Server 2008 and Windows Server 2008 R2: the "Balanced" power plan. As Glenn Berry reports in a recent blog post, with no other changes, simply switching from the balanced power plan to the high performance power plan can yield an improvement of roughly 20%. If you are currently using the "Power Saver" plan, you can expect an even more dramatic improvement.

These numbers are based on GeekBench scores; exactly how you will see the performance impact in your environment will depend on a variety of factors, including how CPU-bound your workloads are, concurrency levels, whether you are on 64-bit, and even down to the model number of your CPUs. Regardless of the level of CPU load you currently sustain, you are very likely to benefit from correcting this option, and are almost guaranteed to not make things worse. But knowing how to correct the issue is the easy part; it's a simple setting in the Control Panel. The main issue is that it can be tedious to check this setting across all the servers in your environment.


If you are using SQL Server 2008 or later, you can use Policy-Based Management (PBM) to check whether all of your servers are using an optimal power plan, then go directly to those that violate the policy and correct them.

First, because PBM can't navigate to the Control Panel and open the Power Options applet interactively, you'll need to find a way to determine the current power plan settings programmatically. As you might expect, this value is stored in the registry; however, unlike most settings with few options, which are usually stored as integer coefficients, the effective power plan is stored as a GUID. The following GUIDs are currently possible; you can get this list, and most likely the following exact result, by running "powercfg /list" at a command prompt:

Existing Power Schemes (* Active)
Power Scheme GUID: 381b4222-f694-41f0-9685-ff5bb260df2e (Balanced) *
Power Scheme GUID: 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c (High performance)
Power Scheme GUID: a1841308-3541-4fab-bc81-f71556f20b4a (Power saver)

Dealing with this output in PBM would not be very much fun either; you'd have to enable xp_cmdshell just to run the command, then you'd have to parse the output for the row containing the star. Since we know that the value is stored in the registry, it's just a matter of determining the path. It's a little more cryptic than you might expect, but here it is:


Then, in order to obtain this value from T-SQL, we can use the extended procedure xp_regread:

@value VARCHAR(64),
@key VARCHAR(512) = 'SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\'
+ 'ControlPanel\NameSpace\{025A5937-A6BE-4686-A844-36FE4BEC8B6D}';

EXEC master..xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @key,
@value_name = 'PreferredPlan',
@value = @value OUTPUT;

SELECT @value;

If the output from this query is anything other than the high performance GUID (8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c), then you could be suffering from CPU performance issues without even realizing it. So now let's look at how we can use Policy-Based Management to automate this check across your environment. First, we will create a condition called "Power Plan is High performance" - the condition is expressed in the desired behavior; we want the failure of the condition to make the policy around it fail. To do this:

  1. In Object Explorer, expand Management > Policy Management
  2. Right-click Conditions and choose "New Condition..."
  3. Enter the Name "Power Plan is High performance"
  4. Change the Facet from Application Role to Server
  5. Under Field, click on the [...] button (this takes you to the Advanced Edit screen)
  6. In the Cell value: textarea, enter the following code (slightly more compact for brevity):
    ExecuteSql('String', 'DECLARE
    @v VARCHAR(64),
    @k VARCHAR(512) = ''SOFTWARE\Microsoft\Windows\''
    + ''CurrentVersion\Explorer\ControlPanel\NameSpace\''
    + ''{025A5937-A6BE-4686-A844-36FE4BEC8B6D}'';

    EXEC master..xp_regread ''HKEY_LOCAL_MACHINE'', @k,
    ''PreferredPlan'', @v OUTPUT;

    SELECT @v;')

  7. Click OK
  8. In the Value field, paste the following GUID value (including the single quotes):

  9. You will need to tab away from the Value field in order to refresh the validation, enabling the OK button. It should look like this:

    using pbm in sql server 2008 to check whether all your servers are using an optimal power plan

  10. Click OK

Now that we have a condition, we can create a policy that checks for servers that don't meet the condition (or, in other words, violate the policy). To create a policy:

  1. In Object Explorer, expand Management > Policy Management
  2. Right-click Policies and choose "New Policy..."
  3. Enter the name "All servers should have a High performance Power Plan"
  4. Change the Check condition: drop-down to the condition you created above
  5. Click OK

To evaluate the policy, you can simply right-click the policy in Object Explorer, choose "Evaluate..." and click the Evaluate button. As I mentioned in a previous tip, when you use methods such as ExecuteSql within conditions, you will have to approve a warning dialog before the policy will evaluate. Eventually you will be able to click Run to evaluate the policy. If the policy evaluation fails, you will see something like this:

right click on object explorer and choose evaluate

You can use PBM to automate and centralize discovery, and you can easily export the policy to new servers as they come online (and periodically evaluate servers over time, as their power plans can change). Edwin Sarmiento wrote a great tip on exporting policies to multiple servers and evaluating them in one shot from within Object Explorer:

Currently you cannot have PBM actually enforce this policy, so it does mean that you will have to go to each non-conforming server and manually change the power plan. You can do this by going to Control Panel > Power Options and choosing the High performance plan (this may look slightly different, depending on your flavor of Windows):

you cannot have PBM actually enforce this policy, so it does mean that you will have to go to each non-conforming server and manually change the power plan

I would suggest automating this further using xp_regwrite to set the high performance GUID value, but you are more than likely to run across permission issues that will prove to be a hassle (see Knowledge Base article #887165). The KB article talks about SQL Server 2000, but the ability to write to registry keys is still very much an issue, regardless of the SQL Server version.

Of course, there are other ways to skin this cat that may be more appropriate. Greg Gonzalez recently blogged about using group policy to enforce the high performance plan across your enterprise. While you may not have the privileges required to implement group policy, if you do, the nice thing about it is that, unlike PBM, it is not limited to servers running SQL Server, and the setting will automatically affect any new servers that come online. If you can't enforce group policy, you can at least show this information to the IT personnel that can, and can even use Policy-Based Management to point them to the servers that violate the policy that you want to enforce.

In the end, no matter how you decide to implement your solution, this is a setting that should at the very least pique your interest. I'm sure you are all for green computing, but not when it means substantially underpowering your servers for little or no measurable gain.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog,, and also blogs at

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, September 9, 2019 - 4:19:07 PM - Aaron Bertrand Back To Top (82328)

BradC I'm sure you're right, a lot about Windows has changed in 8 years. I'll mark this article on my "to update" queue.

Monday, September 9, 2019 - 3:40:55 PM - BradC Back To Top (82327)

Old post, but wanted to confirm Paul's comment below, that this doesn't appear to be the correct registry key, or at least that it no longer works on all my Windows 2012 R2 and Windows 2016 servers. This key returns "balanced" whether the setting has been updated or not.

The registry key that actually shows the current state of this setting is HKLM\SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes\ActivePowerScheme, or you can use "EXEC xp_cmdshell 'powercfg /list" instead (although I'm not sure how that would interact with policy based management). 

The nice thing about this last option is that you can use it to change the setting as well, with "EXEC xp_cmdshell 'powercfg.exe /setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c'"

Tuesday, February 14, 2012 - 7:36:42 AM - Aaron Bertrand Back To Top (16004)

Thanks Paul, I will look into it.

Tuesday, February 14, 2012 - 6:24:09 AM - Paul Egan Back To Top (16003)

Hi Aaron,

I'm not sure this actually works. See which says do not use for internal testing only.

I have tested as well. The registry entry doesn't actually return the active power plan. Comparing the results of the script with powercfg -list and the control panel can give you different results.

I think you should use the win32_powerplan (win2008R2 & Win7) as one of the other comments suggest or use powercfg.


Wednesday, January 19, 2011 - 11:20:02 AM - robert matthew cook Back To Top (12640)

for people already on windows server 2008 r2 instead of executesql and xp_regread and could probably use executewql and the win32_powerplan class 

Tuesday, January 18, 2011 - 1:59:41 PM - Amy Jo Para Back To Top (12630)

This is what I suspected.  Thanks!

Tuesday, January 18, 2011 - 1:26:12 PM - Aaron Bertrand Back To Top (12629)

Hi aj, I have tested this with VMWare and was also pointed to this thread (, both of which confirmed that the guest does not have any direct control over the physical CPU of the host.  So (and granted I have yet to test on Hyper-V, but suspect the same limitations), you won't see the performance boost by changing from balanced/power save to high performance, because Windows wasn't really able to cut back the CPU to achieve those goals in the first place.  But just to be safe (and because you don't want to have to worry about which servers are virtual and which are not, especially if you ever perform P2V conversions), I would just set the high performance plan on all servers, regardless of their current status.

Tuesday, January 18, 2011 - 12:40:12 PM - Amy Jo Para Back To Top (12627)

I appreciate your article Aaron as we are always looking for improved performance on some heavily hit SQL servers within our company.  Does the information you give regarding 'High Performance' power configuration setting also apply to VM servers to any degree?  Please explain.  Thanks, aj

get free sql tips
agree to terms