Quick Scan Report – Power Settings
What this check looks for
The active Windows power plan, read through xp_cmdshell running powercfg, checking whether the active plan is (High performance). The check is skipped on Amazon RDS, and it reports separately when it cannot read the setting at all.
Why it matters
Balanced throttles the processors, and SQL Server cannot see it happening.
Under the Balanced plan Windows reduces the processor clock when it judges the machine to be lightly loaded, and raises it again when load increases. The judgement is made over a window, and SQL Server’s workload does not suit it: bursts of short, latency sensitive queries look like a lightly loaded machine right up to the moment they need full speed. The processors spend their time ramping rather than running.
Measured effects on database servers are routinely 10 to 30 percent of throughput, and on some hardware worse, for a workload of many small queries.
The reason this is worth its own check is that it is invisible from inside SQL Server. Every counter you would look at is consistent with a healthy machine:
- CPU utilization looks lower, not higher, because the work is being done more slowly at a reduced clock.
- There is no wait type for “the processor is running at 1.2 GHz instead of 3.5 GHz”. The time shows up as signal waits and as plain elapsed time.
- Query duration rises across the board with no query, index or plan to blame, which is the hardest kind of slowdown to diagnose.
Windows recommends Balanced, and it is right to for a laptop. It is not right for a server whose job is to answer queries quickly.
Check the BIOS as well as Windows, because many servers ship with their own power management that overrides or constrains the Windows plan. A machine set to High Performance in Windows and to a balanced or OS-controlled profile in the BIOS is still throttled, and that combination is very common on hardware installed by a vendor.
How to confirm it yourself
From Windows, on the server:
powercfg /listThe plan with an asterisk is the active one. Or, to get it directly:
powercfg /getactiveschemeThe current clock speed against the rated speed is the confirmation that matters:
wmic cpu get name, currentclockspeed, maxclockspeedA currentclockspeed well below maxclockspeed on a busy server is the finding in a single line.
From SQL Server, if xp_cmdshell is available:
EXEC xp_cmdshell 'powercfg /getactivescheme';If xp_cmdshell is disabled, and it very often should be, read it on the server instead rather than enabling it for this.
How to fix it
Set the Windows plan to High Performance:
powercfg /setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635cThat GUID is the well known identifier for High Performance and is the same on every installation. Through the interface: Control Panel, Power Options, High performance. The change takes effect immediately, with no restart and no SQL Server outage.
Then check the BIOS, which is the step that gets skipped. The setting is named differently by every vendor: “Power Regulator”, “System Profile”, “Power Management”, “CPU Power Management”. You want the maximum performance or OS-independent maximum profile rather than Balanced, Dynamic or OS Control. This one does need a reboot, so it goes in the next window.
On virtual machines, check the host. A guest showing High Performance on a host that is power managed is throttled by the host, and the guest cannot see it or change it. That is a conversation with whoever runs the virtualization platform.
Then confirm. Run the wmic query above under load. A current clock speed at or near the maximum is the proof, and it is worth capturing because this setting has a habit of being reset by group policy or by a firmware update.
How long it takes
About an hour for the Windows setting and the confirmation. The BIOS change waits for a reboot window.
Related reports
| Report | Why you would go there |
|---|---|
| Server Overview | The hardware this instance is running on. |
| SQL CPU Schedulers | CPU pressure from inside SQL Server, for a before and after. |
| CPU by Query | Whether query CPU falls after the change. |
| Performance History | The same workload before and after, which is the honest measure. |
| Waits | Signal waits, which is where throttling partly shows up. |
Related checks
| Check | |
|---|---|
| Unable to check the server power setting | When the check cannot read the plan at all. |
| Not using all cores | Cores the instance cannot use, a different CPU limit. |
| Max degree of parallelism | Parallelism settings, also commonly wrong. |
| Lightweight pooling | Another CPU related setting worth reviewing. |
Frequently asked questions
Does this really make 10 to 30 percent of difference? On a workload of many short queries, yes, and it is well documented. On a batch workload that keeps every core busy continuously, much less, because the processors stay ramped up.
We set High Performance and saw no change. Check the BIOS, and on a virtual machine check the host. Both override the guest setting, and both are more common than the Windows plan being wrong on its own.
Is there a downside? More power and more heat. On a server whose purpose is answering queries quickly that is the trade you want, and on modern processors the idle draw difference is smaller than it used to be.
What about Ultimate Performance? It exists on newer Windows Server versions and removes a few more latency optimizations. High Performance captures nearly all the benefit and is the usual recommendation.