Are Power Saving Settings Killing Your SQL Server Performance? [Quick Tip]

At Straight Path Solutions, we love performing SQL Server Health Assessments. These are our exams that dig quickly into the root of your SQL Server instance and find problems that you are currently suffering from. Then we give you an action plan to stop suffering from them today. Rather than just performing the checks and finding the same problems each time, we wanted to make life a little more difficult for us and a little easier for those reading our blog posts. So we decided to give you a few cheats on how to stop some of our most common findings yourself before we even start.

This post will give you our tips on performance-killing power-saving settings.

Most SQL Server DBAs Still Miss This One

By now we aren’t surprised when we find an environment running in power savings mode. This topic has been talked about a lot in the SQL Server blogging world, at conferences, and even by Microsoft somewhat. Yet, we often still find servers running in balanced mode and suffering from some of the effects of this setting, which can really kill your performance.

What Is Balanced Mode and Why Does It Matter to SQL Server Performance?

Balanced power mode has a major impact on SQL Server performance. Simply put, you’ve told Windows Server (through Control Panel) or your server hardware (through BIOS settings) to sacrifice a few performance-minded things for the sake of using a little less power. In fact, in plenty of studies and blog posts by folks in the community (including this post by Glenn Berry), you can see the effect of CPU power saving, especially. In essence, the CPUs will run at a lower clock multiplier when demand isn’t deemed high enough, and that clock speed will only increase when demand is high enough for long enough. This results in a slower CPU speed during normal operations. That setting actually works well on my laptop or tablet when I want to conserve battery life and don’t have a workload that is sensitive to CPU speed.

For a SQL Server, though? That is horrible for performance. Windows balanced mode also can cause other components to run slower or behave differently than when the server is running in High Performance mode. For instance, USB ports can be underpowered and network interfaces can be under-powered or even go to sleep. Frankly, for a SQL Serve, nothing good comes out of these modes.

Is My SQL Server in a Bad Spot?

To find out if these power settings are affecting your SQL Server instance, you can check three ways, and you should check and verify all three ways:

1. BIOS – Several manufacturers have power saving settings and options in BIOS settings. When a server restarts, look to your startup settings at the BIOS level and ensure these power schemes are disabled. This requires a restart and isn’t often necessary for immediate relief, but you should check this and make it a policy to know what the right settings are.
2. Windows Power Settings – Go to the Control Panel and into power settings. There are a few ways to get to the Power Settings, depending on your machine. You should be able to browse to it through Control Panel or run “powercfg.cpl” in most, if not all, editions of Windows that would currently be running SQL Server. Look and see if you are in Balanced mode or High Performance mode. If you are in Balanced mode, it is possible that your CPU and other configurations are suffering. Now if your BIOS is correct here, you may be okay but you should change to High Performance mode anyway.
3. CPU-Z – This is a free tool available at this website.  In the below video, I walk through the tool and how you can both see the effect of CPU power saving and see the immediate relief that comes from changing your power saving mode. Please note: As with all software from the Internet, get familiar with the tool in a non-production environment first. I’ve used it in several environments. One time the tool wouldn’t die and the .exe couldn’t be killed until a server was restarted. That was once and only once out of numerous uses – 100-like I say in the video, or possibly a lot more – and even there nothing bad came of it. We just waited for the planned maintenance restart.

Make Your SQL Server Faster

So. To make things better? Just go into those power settings areas above that you checked and disable power saving. Go to High-Performance mode. Use CPU-Z to verify it is all working, and you should see a performance difference. Depending on your workload and where your bottlenecks have historically been, you’ll feel an improvement.

Want more help?

We’re happy to work with you on a SQL Server Health Assessment. We’ll explore your SQL  Server environment to see what other bottlenecks are lurking behind the scenes and what areas of concern or risk you have. Some customers have an exam and then implement the changes on their own from our detailed handover of information. Some customers have us mentor them on the how and why behind the changes so that knowledge moves in-house. Either way, we’d love to talk with you and see what we can do to help.