Ensuring Maximum CPU Performance via Group Policy

There has been quite a bit of talk recently about a performance issue I see regularly on Windows Server 2008 and 2008 R2 servers, and that’s the use of the default Power Plan setting of “Balanced.” Glenn Berry posted about the impact of this setting on CPU performance; Brent Ozar blogged about a real world case where query CPU time actually doubled on new hardware; Paul Randal is conducting a survey to ascertain the extent of the issue (when Paul does a survey like this it’s usually a sign that all may not be well in the SQL Server world); and Steve Jones recently covered it in his editorial.

There isn’t much more I can add to the discussion of the impact of this setting on CPU and SQL Server performance, however, I will cover how easy it can be to make it a non-issue in your environment. I first ran across this issue and solution last year when deploying new server hardware. Our environment is based around the Intel Modular Server system and Windows Server 2008 R2, and we needed to be able to run hundreds of Hyper-V virtual machines and SQL Servers for QA testing, scalability testing, and production. The hosts themselves, and the majority of the VMs, are Windows Server 2008 (or R2), and so with the default “Balanced” plan we wouldn’t be able to take full advantage of the significant CPU horsepower in which we’d invested (six “big blades,” each with dual quad-core, HT-capable Intel X5550s).

The solution is simple: Group Policy. Like most other OS settings, the Power Plan can be set to whatever you want via policy, and any existing or new servers that use the policy will inherit this setting. There are a few ways to check and see whether a power plan policy has already been set; which you use will depend on the size of your environment and your level of access to group policy. The most basic approach is simply to inspect the power plan setting for each of your SQL Server machines and see whether the plan is set and/or editable:

If you see the “Some settings are managed by your sys admin” message, it’s read-only and set to High performance, then policy is already set and you are good to go.

Alternatively, if you have access to group policy, open up the Group Policy Management Console in Server Manager, navigate down to Group Policy Objects, then select the Settings tab for each policy. Expand down to Administrative Templates > System/Power Management and see if “High Performance” has been set for Active Power Plan as shown below:

If you have multiple policies and it’s not clear which your SQL Server machines may be using, you can use either the Group Policy Results or Group Policy Modeling wizards to view the Resultant Set of Policy for one or more servers. Once the results have been generated, check the Settings tab as above.

If it turns out that no policy has been set, you will need to either edit an existing policy or create a new policy that covers your SQL Server machines and apply the settings shown below:

If you need more details on this process, see Edit Administrative Template Policy Settings. If you don’t have access to group policy at all, I’m sure you will tell whoever needs to know about this and get it changed… and hopefully they aren’t too taken by the modest power savings of “Balanced” to accommodate. If they are, tell them to save the money on less powerful CPUs next time since that’s the net effect. 😉

For the life of me I don’t understand why a server OS would ship with this default (well, I do know why, but I certainly don’t agree). I don’t expect it will be changing anytime soon though, so hopefully a little group policy will help you get the most out of your CPU resources in the meantime.

Greg Gonzalez

Greg is founder and Chief Scientist at SentryOne. He is a Microsoft developer by background and has been working with SQL Server since the mid 90s. He is perpetually engaged in the design and development of SQL Sentry, Plan Explorer and other SentryOne solutions for optimizing performance on the Microsoft Data Platform.