Identify SQL Servers with inefficient power plans using Policy Based Management

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

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:

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

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):
   
   '8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c'
   
   
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:

   

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:

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: http://www.mssqltips.com/sqlservertip/1493/evaluate-and-implement-policies-in-multiple-sql-server-instances/.

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.
• Review the following tips and other resources:
  
  • Using Policy-Based Management in SQL Server 2008
  • Evaluating and Implementing Policies in Multiple Instances in SQL Server 2008
  • Policy-Based Management tips at mssqltips.com
  • The Importance of Windows Power Plans for Server Performance
  • Windows Power Plans and CPU Performance
  • SQL Server on Power-Saving CPUs? Not So Fast.
  • Ensuring Maximum CPU Performance via Group Policy

About the author

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, SQLPerformance.com, and also blogs at sqlblog.org.

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

View all my tips