Identify SQL Servers with inefficient power plans using Policy Based Management

Problem

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.

Solution

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:

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:

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:

In Object Explorer, expand Management > Policy Management

Right-click Conditions and choose "New Condition..."

Enter the Name "Power Plan is High performance"

Change the Facet from Application Role to Server

Under Field, click on the [...] button (this takes you to the Advanced Edit screen)

In the Cell value: textarea, enter the following code (slightly more compact for brevity):

In the Value field, paste the following GUID value (including the single quotes):

'8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c'

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:

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:

In Object Explorer, expand Management > Policy Management

Right-click Policies and choose "New Policy..."

Enter the name "All servers should have a High performance Power Plan"

Change the Check condition: drop-down to the condition you created above

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:

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):

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

Create a condition and policy as described above.

Export the policy to all of your SQL Server instances running on Windows Server 2008 and above.

Evaluate the policy and correct the setting for any servers that are not using the High performance power plan.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Hi aj, I have tested this with VMWare and was also pointed to this thread (http://communities.vmware.com/thread/259158), 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.

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

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.