SQL Server Performance and Windows Power Plan

Conserving energy is good. It’s good for the environment and it’s good for the bottom line. That’s why Windows Server has built-in power settings. But there’s a trade off. With reduced power comes reduced performance for most SQL Servers.

The Windows Power Plan Setting

Windows Server has three pre-defined power settings: Balanced, High Performance, and Power Saver. I’ll briefly summarize the settings without going into much detail. The names are pretty descriptive. (Microsoft has some nice documentation about how Windows uses and calculates energy efficiency. See Power and performance tuning for more information.)

The Balanced Power Plan

The Balanced power plan is the default Windows Power Plan setting. Unless you’ve intentionally changed the setting, that’s the scheme your Windows Server is using. It’s a good setting for many servers since it balances performance with power consumption. Typically, this setting is not good for SQL Servers, however. A reduction in processing power translates into a reduction in performance.

The High Performance Power Plan

The High Performance scheme prioritizes server performance over power conservation. All cores are unparked and all processors are running at their highest level. This is the best setting for resource intensive applications and services where throughput and responsiveness is critical.

The Power Saver Power Plan

Power Saver is exactly that. Priority is given to reducing heat, conserving energy, and lowering operating costs. This may a good setting for some workloads where latency and other performance metrics are less important. It’s hard to imagine a case when SQL Server would benefit from this setting.

Checking the Windows Power Plan on your SQL Server

A Windows Server running SQL Server should almost always use the High Performance setting. Yet, the Windows Power Plan setting is often overlooked when a new server is provisioned. In fact, we routinely find the Balanced Plan setting in place during our SQL Server Assessments.

So, to help eek out the best performance on your SQL Server, check the Windows Power Plan on your servers. Here are a few ways.

Using the Windows Power Plan Configuration

Unless you’re running Windows Server Core or Nano, you have access to the point-and-click GUI environment. To check the Power setting, open the Power Options as shown below. You can see that this server is set for High Performance.

If your server is set to one of the other plans, you can choose another plan and close the window. No reboot is required for the new setting to take affect.

Using Powercfg to check the Windows Power Setting

The point-and-click method may be fine for just a few servers. But if you want to check much more than that scripting, will be your friend. Fortunately, Windows Server has a command line utility to help – the Power Configuration (Powercfg).

Powercfg has a lot of options, including the ability to check and change the Windows Power Plan. To see the current Power Plan setting, use the command line to run the utility with the /getactivescheme option as shown below.

powercfg /getactivescheme

The current setting is printed to the screen.

If you want to change the current setting. use the /setactive option.

Using DbaTools to check the Windows Power Setting

For those of use who are PowerShell fans, dbatools.io has a great command available called Get-DbaPowerPlan. From within PowerShell, run the command and specify the server(s) you’d like to check. In the following example, I’m checking only the local server.

get-dbapowerplan -ComputerName .

You can also use the Set-DbaPowerPlan command to change the setting.

More on SQL Server performance

If your SQL Server is running with the default Balanced Power Plan, you may be running with less power than you have available. Checking and changing the Windows Power Plan can help boost the performance of your SQL Server.

Here are a few other posts on SQL Server’s performance, as well.

• Is My SQL Server Configured Properly?
• Identify Disk I/O Performance Issues for Your SQL Server Using DiskSpd
• How Many tempdb Data Files Should My SQL Server Have?
• 3 Ways to Find Your SQL Server tempdb Data Files
• Is There an Update for My SQL Server?

Want to work with The Sero Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. Let’s schedule a short call.