Skip to content
Home » Balanced Power Saving T-SQL script

Balanced Power Saving T-SQL script

We can easily spend tens of thousands of dollars on core licences for SQL Server, and then we go and install the product on an operating system with the default Balanced Power Plan, which is, well, idiotic.

No! Balanced Mode Is Wrong!
No! Don’t do this! Click “High performance” immediately! Exclamation mark!

Imagine buying one of the fastest road cars money can buy (a Bugatti Veyron), which used to cost US$2,700,000 for the Enterprise Edition Super Sport Edition.

To make this car hit the top speed of over 265 miles per hour, there is a special mode that must be entered when the car is at rest, by toggling a key to the left of the driver’s seat.

Windows has the same setting. It’s in the Power Options under Control Panel, and for all servers, no matter what, it should be set to High Performance.

Here’s a free T-SQL script I wrote that will check for you what the power settings are. We don’t always have desktop access to a server when we are checking diagnostics, but it’s good to know if performance problems can be addressed by a really simple fix that doesn’t require the vehicle to be at rest.

(The script also respects your settings, so if you had xp_cmdshell disabled, it’ll turn it off again when it’s done.)

DECLARE @isCmdShellEnabled BIT;
DECLARE @isShowAdvanced BIT;
SELECT
@isCmdShellEnabled = CAST(value AS BIT)
FROM
sys.configurations
WHERE
name = 'xp_cmdshell';
SELECT
@isShowAdvanced = CAST(value AS BIT)
FROM
sys.configurations
WHERE
name = 'show advanced options';
IF(@isShowAdvanced = 0)
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
END;
IF(@isCmdShellEnabled = 0)
BEGIN
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
END;
--Run xp_cmdshell to get power settings
EXEC xp_cmdshell 'powercfg /list';
--Turn off 'xp_cmdshell'
IF(@isCmdShellEnabled = 0)
BEGIN
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
END;
--Turn off 'show advanced options'
IF(@isShowAdvanced = 0)
BEGIN
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
END;

On a server that is set correctly, this is the output. Notice that the High performance option is active.

All is right with the Power settings

If a different power setting is active, talk to a server admin (maybe that’s you) to change it to high performance.

I’ve also put this script with my Max Server Memory script on GitHub.

If you have any other stories to share about throwing money away, find me on Twitter at @bornsql.

Tags:

4 thoughts on “Balanced Power Saving T-SQL script”

  1. Sharon Rimer

    I used this script –

    --Windows Power Plan
    DECLARE @InstanceNames sysname = @@SERVICENAME
    DECLARE @reg TABLE
    (
    keyname CHAR(200) ,
    value NVARCHAR(1000)
    );

    INSERT @reg EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows\CurrentVersion\explorer\ControlPanel\NameSpace\{025A5937-A6BE-4686-A844-36FE4BEC8B6D}', 'PreferredPlan';

    SELECT 'Windows Power Plan',@InstanceNames,'Power Plan' ,CASE CONVERT(VARCHAR(50),value)
    WHEN '381b4222-f694-41f0-9685-ff5bb260df2e' THEN 'Balanced'
    WHEN 'a1841308-3541-4fab-bc81-f71556f20b4a' THEN 'Power saver'
    WHEN '8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' THEN 'High performance'
    ELSE NULL END
    FROM @reg;

    1. bradc

      I don’t think this is the correct registry key; it shows “balanced” whether it is set or not. The correct key is at HKLM\SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes\ActivePowerScheme

Comments are closed.