Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

PBM Policy / WMI query to see which CPUs are in power-saving mode

By Ben Whitman,

Simply run this script on any SQL 2008+ server to install a condition and related policy which can be used to check target servers to see if they have any CPU-based power-saving features on. This can be extremely detrimental to performance, especially for parallel queries. Servers are considered compliant by this policy if each of their CPU cores are running at the maximum possible speed. If you find any non-compliant servers, you will probably need to boot into the BIOS of the machine and look for any power-related settings and set them to 'High Performance' or similar.

Some notes...

  1. If any of your servers are clustered, then this policy will only target the currently active node, so bear in mind that your passive nodes wont be scanned. If you just want to do a quick one-off check on a server which is currently a passive cluster node (or any server for that matter). You can use powershell to just query WMI directly from the machine:

    gwmi -class win32_Processor | SELECT CurrentClockSpeed, MaxClockSpeed

    Look for any rows where the CurrentClockSpeed is not equal to the MaxClockSpeed.

  2. Don't evaluate the policy at a time when you know the target server is currently experiencing high CPU load. If you do, the CPU cores may already have 'woken up' and the policy will see the server as compliant.
  3. If you scan all your servers only to find out that every single one of them has power-saving turned on, I recommend staggering the change to turn the feature off for each machine. By making this change, you may well shift some bottlenecks from CPU to I/O for example, and you won't want to be investigating lots of new issues all at once especially after having promised to your manager massive improvements to all the SQL servers in your organisation.
  4. If you have never run a WMI-based policy such as this one before you may receive an error when you try to evaluate. If you do, try turning off User Account Control from the Control Panel in Windows or running SQL Management Studio 'as Administrator'.

As an alternative to using Policy-based Management - if you don't have SQL 2008 or a central management server for evaluating groups of servers, or you simply want to check all your servers including passive cluster nodes (and even non-SQL servers!) you can run the Powershell command below. You will need the appropriate rights on the remote machines of course...

First create a CSV file called servers.csv file and lay it out as in the example below - with the header row containing just 'ServerName'

ServerName

YourServerName1

YourServerName2

Then run this from the same directory as the csv file in Powershell

$servers = import-csv servers.csv
foreach ($server in $servers) {$wmiOutput=gwmi -computername $server.ServerName -query "SELECT CurrentClockSpeed
, MaxClockSpeed FROM win32_Processor WHERE CurrentClockSpeed <> MaxClockSpeed"; $wmiOutput | select {$server.ServerName}, CurrentClockSpeed, MaxClockSpeed}

I do recommend the policy as well though, as it means (if you are using the model of Central Management Server + Enterprise Policy Management Framework) every new SQL server you deploy will get automatically checked.

Enjoy!

Ben Seaman

Total article views: 1348 | Views in the last 30 days: 11
 
Related Articles
FORUM

SQL Server 2008 Policy Management

SQL Server 2008 Policy Management - Server Roles

BLOG

SQL Server Policy Based Management Alerts

SQL Server Policy Based Management Alerts Problem Policy-Based Management is used to enf...

FORUM

Enterprise Policy Management Framework with SQL Server 2008

Enterprise Policy Management Framework with SQL Server 2008

FORUM

Enterprise Policy Management Framework on SQL server 2008

Enterprise Policy Management Framework on SQL server 2008

BLOG

SQL Server Denali CTP3: Policy Based Management

Whether you know it or not, Policy-Based Management is an integral part of the new release of ...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones