Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Identify SQL Servers with inefficient power plans using Policy Based Management


By:   |   Read Comments (6)   |   Related Tips: More > Performance Tuning

Attend a SQL Server Conference for FREE >> click to learn more


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:

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:

HKEY_LOCAL_MACHINE\
SOFTWARE\
Microsoft\
Windows\
CurrentVersion\
Explorer\
ControlPanel\
NameSpace\
{025A5937-A6BE-4686-A844-36FE4BEC8B6D}\
PreferredPlan

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

DECLARE
@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):
    '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:

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

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources





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    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

Thanks Paul, I will look into it.


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

Hi Aaron,

I'm not sure this actually works. See http://technet.microsoft.com/en-us/library/ff716470%28v=ws.10%29.aspx 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.

Thanks.


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

for people already on windows server 2008 r2 instead of executesql and xp_regread and could probably use executewql http://sqlserverpedia.com/blog/sql-server-bloggers/executewql-in-policy-based-management/ and the win32_powerplan class http://msdn.microsoft.com/en-us/library/dd904531.aspx 


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

This is what I suspected.  Thanks!


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

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.


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

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


Learn more about SQL Server tools