Get the Active Power Plan of Multiple Servers with PowerShell

It is a widely known and discussed performance hit when you don’t have an optimal power plan set on your database servers.  This is one of those easy ways to get great gains by simply flicking a switch…almost literally.

But what happens if you have hundreds or thousands of servers under your responsibility?  The thought of that daunting task is enough to make the typical DBA shrug and push it off until another rainy day.  After all, no users are complaining about performance right now.

But alas, in one swift script you can easily reach across all of your servers to determine what the current active power plan is.  This gives you the time savings while not having to manually execute a monotonous task by going to each server.   My proposition is to user PowerShell and WMI in order to get this information.

Because I’m such a fan of reusable code, I will wrap all of my logic in a function that is good at doing just one thing:  Retrieving the active power plan of a list of servers.

Function Get-PowerPlan {

    param (

        [Parameter(Mandatory = $false, Position = 0, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]

        [String[]]$ServerNames = $env:COMPUTERNAME

    )

    ### function body – see the rest of the code below ###

}

The skeleton of the function appears above.  We declare only one single parameter, which is a string array that’ll store the list of server names to reach across.  It isn’t a mandatory parameter, as I wanted to have a default to allow a user to execute this function parameter-less to get the active power plan on the local machine.

Now onto the function’s body:

process {

    foreach ($ServerName in $ServerNames) {

        try {

            Get-WmiObject -ComputerName $ServerName -Class Win32_PowerPlan -Namespace "root\cimv2\power" |

                Where-Object {$_.IsActive -eq $true} |

                Select-Object @{Name = "ServerName"; Expression = {$ServerName}}, @{Name = "PowerPlan"; Expression = {$_.ElementName}}

        }

        catch {

            Write-Error $_.Exception

        }

    }

}

All the rest of this function does is loop through the list of server names supplied and by utilizing the Win32_PowerPlan WMI class and a few cmdlets to filter out and grab certain data.  The output of this function is simply the server name and the current active power plan on the server.  Here is a sample way to call this function:

$ServerNames = "SERVER1", "SERVER2", "SERVER3"

$ServerNames |  Get-PowerPlan

Most likely you will not have only three servers, so you will ideally want to grab this string array from a source listing of the server names.  If you store your server names in a text file (each server on its own line), you can use the Get-Content cmdlet to get the string array to pass to Get-PowerPlan:

ServerList.txt

SERVER1

SERVER2

SERVER3

Get-Content -Path "C:\Your\Directory\ServerList.txt" | Get-PowerPlan

Or, say you store your server names in a management database.  You can retrieve the list by using SMO, and pipe that string array to Get-PowerPlan.

Sample Server List Table

use TestDB;

go

create table dbo.server_list

(

       name sysname not null

);

go

insert into dbo.server_list

values

       ('SERVER1'),

       ('SERVER2'),

       ('SERVER3');

go

And the PowerShell code could resemble the following:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("YourSqlServer")

$SqlServer.Databases["TestDB"].ExecuteWithResults("select name from dbo.server_list;").Tables[0] |

    Select-Object -ExpandProperty Name |  Get-PowerPlan

In case you just want an output of all servers that aren’t using the “High performance” power plan, you can pipe the above output to a Where-Object cmdlet and specify a predicate for the PowerPlan property.

Get-Content -Path "C:\Your\Directory\ServerList.txt" |

Get-PowerPlan |  Where-Object {$_.PowerPlan -ne "High performance"}    

Likewise, you could pipe all of this to a file (such as a CSV) for later viewing and reporting. 

Get-Content -Path "C:\Your\Directory\ServerList.txt" |    

Get-PowerPlan | Export-Csv -Path "C:\Your\Directory\OutputPowerPlan.csv" -NoTypeInformation      

Note that on most newer servers, Power Management settings in the BIOS also need to be set correctly. If set incorrectly, despite the Windows power plan settings, the system could still favor electrical savings instead of performance.

Also from a Virtual environment, you would have to consider the host as well as the guest as in the case of VmWare Power Plan and Hyper-V Power Plan settings .

I have shown you today how to gather the active power Plans across a variable number of servers.  Enjoy!

Script:  Download Powershell Script here

Thomas Stringer – SQL Server PFE

Twitter: @SQLife