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.
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.
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
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.
|
|
|
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.