Few thoughts on Windows Power Options

I ran into and old issue recently I hadn’t seen in a while. A database server was experiencing some intermittent performance issues that didn’t make much sense. It didn’t take me long to figure out what was going on though, after firing up the Resource Monitor.

The issue I’m referring to in this case is the CPU Cores ending up in “Parked” state with the default Windows Server Power Options. After installation of Windows 2008 and Windows 2008 R2 servers they have their Power Options set to “Balanced”, which is unfortunately is not a good option when using the box for running SQL Server. In Windows 2012 these options have changed a bit and are based on the Processor you have installed, example for Intel processors this feature is disabled by default.

Basically what’s happening is, that to conserve power Windows will only use a certain number of cores while the rest are in a “Parked” state, waiting to be activated once a load in the currently active CPU’s go high enough. In practice this means that the core parking algorithm will try to reduce the number of active hardware threads (Physical or Logical Processors) and have scheduler to target to target other threads. Easy way to check if your server is using this feature is to look at the Resource Monitor and the CPU tab when your system is under light load.

Parked CPU core

Fortunately for us, this feature is pretty easy to disable. Go to Control Panel, select Hardware and there’s a section called Power Options. Choose Change power-saving settings and from Preferred plans pick High performance one. And you’re done! Even better option is to set this through AD Group Policy and target all your SQL Servers with it.

Power options

Update

I realized that I had forgotten to mention something else regarding this issue. It will not only “Park” your CPU cores, but it can and will also drop the clockspeed of your CPU by lowering the processor multiplier value. There are few tools that can detect this, such as CPU-Z but it can also be checked, in some cases, with simple WMIC query.

WMIC CPU GET NAME, CURRENTCLOCKSPEED, MAXCLOCKSPEED

azure_wmic

If you add /EVERY:1 to end of the query, it’ll be fired up every 1 seconds showing the changes happening constantly. Note though, that the WMIC query is nowhere near as accurate as is the CPU-Z, which will show you even the slightest changes in multiplier, as show below. In that same server, WMIC query didn’t report any changes on clockspeed with multiplier changing from 11 to 10.

azure_cpuz1

azure_cpuz2

The other side of the coin.

While most DBAs, myself included, will give you the advice to turn off this setting there’s one thing to keep in mind. While this annoying little setting is affecting our precious database servers, we’re looking things from a very narrow point of view. There’s also another one, bit broader one, available.

I’ve seen some studies on this that at 50% workload of maximum CPU capacity power requirements can go down 15 to 25% depending on your power-saving settings. This isn’t truly all that much in scope of a single server, but when you’re running data center of thousands of servers, that’s a completely different thing. Data centers need electricity, a lot of it, to run the server and then to cool the premises from the heat generated from running the said servers. The person that is out there paying for all that electricity, he’s going to love anything that cut downs the electricity consumption by 15%

So if your servers are running in a data center somewhere, be polite about it. Don’t just flip the switch but go talk to your sysadmins about it and ask them to do it through previously mentioned Group Policy setting. If you’re wondering why, it’s because that they might later on implement a Group Policy (to get the 15% savings data center wide) that’ll set this option on and change your manually defined setting. If you have one already set on the OU you have your database servers on, it’ll stay on performance setting.

Author: Mika Sutinen

Hi, My name is Mika Sutinen and I'm a Senior Database Administrator for a company called Tieto. I've been working in IT-industry for two decades and I've spend most of my career working with healthcare information systems. I've worked with SQL Server for most of my career, starting with version 6.5 a long, long time ago. My other interests are high availability, everything related to performance (testing, monitoring, etc), Windows operating systems and I'm currently learning more about Azure.

1 thought on “Few thoughts on Windows Power Options”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s