Art of the DBA Rotating Header Image

I need more power!

Man, sometimes blogging is hard. I meant to get this out earlier in the week, but better late than never, right?

When I was out at SQL Rally a couple weeks ago, I went to Argenis Fernandez(b|t) and Robert Smith’s(b|t) pre-con for SQL Server best practices. It was a good session, with lots of interesting discussion on ways to configure your environments for SQL. One gotcha we talked about was SQL 2008’s annoying power plans and how they get set to Balanced by default. Now, I get what Microsoft is trying to do with these plans, but defaulting to Balanced on servers? Really? Let’s use some common sense.

Anyway, before I get on a rant (don’t worry, I’ve got a couple of those in store for later blog posts), the question that popped into my mind when we started talking about this was how to do it on Server Core. Changing the plans on your standard Windows installs is easy, you just browse into Power Plans (click your start and type “power plans” in the search box). What about when we have no GUI? Do we panic? Of course not, because we have the command line!

Some quick Googling provided me with two items. One is powercfg, a command line utility that Microsoft has provided to allow us to edit these plans. It’s handy, but a little confusing, especially because the power plans use GUIDs for management and that adds a whole new level of trickiness. So, the second Google nugget to appear was this handy script from Aaron Saikovski, where he wraps powercfg in some Powershell to give us more graceful management. Here it is with some editing/tweaking from me for use:

function Set-PowerPlan(
    [ValidateSet("High performance", "Balanced", "Power Saver")] [string]$PreferredPlan)
{
	$currplan = powercfg -getactivescheme
	Write-Host "Current plan is $currplan.substring($currplan.Indexof('{')) [$(Get-Date)]"

    Write-Host "Setting Powerplan to $PreferredPlan"
    $guid = (Get-WmiObject -Class win32_powerplan -Namespace root\cimv2\power -Filter "ElementName='$PreferredPlan'").InstanceID.tostring()
    $regex = [regex]"{(.*?)}$"
    $newpowerVal = $regex.Match($guid).groups[1].value

    #Set the powerplan
    powercfg -S  $newpowerVal

	$currplan = powercfg -getactivescheme
	Write-Host "Current plan is $currplan.substring($currplan.Indexof('{')) [$(Get-Date)]"
}

#set Preferred powerplan
Set-PowerPlan $args[0]

Save the script and call it with whatever plan you want as the argument, or put it in your profile and call it as a function. Technically, you can use this to set the power plan to any of the default options, but my main use of it is to set the plan to High Performance for my Core servers.

UPDATE: Kendall Van Dyke (b|t) provides a handy way to update the plan using the WMI. I’ve not used it much, but I’ll definitely play around with it. In general, I think you could go either way for managing your powersettings.

Get-WmiObject -Class win32_powerplan -Namespace root\cimv2\power | ? { $_.ElementName -ieq 'high performance' } | % { $_.Activate() }

8 Comments

  1. Have you tried calling the Activate() method on the WMI object? It would look something like this:

    Get-WmiObject -Class win32_powerplan -Namespace root\cimv2\power | ? { $_.ElementName -ieq 'high performance' } | % { $_.Activate() }

    You could even throw the -computername parameter in there to set the power plan on another machine (assuming you have the proper rights).

    1. Mike Fal says:

      This is why I love the SQL community. I’ll be honest, that method didn’t come up in my searches. I’m a big fan of using the WMI, but it can be a little mysterious. Many things are not documented very well within there. I think the advantage of using powercfg is that your interface for changes is a well documented utility, but I could really go either way. I’ll have to play with the WMI method for a better understanding of it.

  2. Shaun says:

    Another option, if your company utilizes Windows policies, is to have your Windows admin set this preference as part of your policy settings so all your SQL Servers (or even all your severs period) get set to High Performance. This has the added advantage of configuring any new servers that come online too.

    1. Mike Fal says:

      Overall, I would prefer using policies to manage this, because it’s one configuration point. However, there’s a lot of times these settings get overlooked for one reason or another. Also, the DBAs may not have access to these policies (I know I don’t). In those events, it’s good to know what your options are for managing things.

  3. Jeff Sherard says:

    “What about when we have no GUI? Do we panic? Of course not, because we have the command line!”

    I would panic :-)

    CLI GUY to the rescue – that could be your super hero persona.

  4. […] I need more power! – Mike Fal (Blog|Twitter) […]

  5. […] Setting your SQL Server Power Plan to High Performance is ALWAYS a good idea, regardless of version, or cluster, or anything. I discovered this little gem from a blog comment. Please read the comment from Kendal Van Dyke on this blog. […]

Leave a Reply

Your email address will not be published. Required fields are marked *