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

Ben Whitman, 2017-06-06 (first published: 2011-01-14)

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...

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.
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.
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.
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

Declare @condition_id int EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'CPU cores are running at maximum clock speed', @description=N'', @facet=N'Server', @expression=N'<Operator> <TypeClass>Bool</TypeClass> <OpType>EQ</OpType> <Count>2</Count> <Function> <TypeClass>String</TypeClass> <FunctionType>IsNull</FunctionType> <ReturnType>String</ReturnType> <Count>2</Count> <Function> <TypeClass>String</TypeClass> <FunctionType>ExecuteWql</FunctionType> <ReturnType>String</ReturnType> <Count>3</Count> <Constant> <TypeClass>String</TypeClass> <ObjType>System.String</ObjType> <Value>String</Value> </Constant> <Constant> <TypeClass>String</TypeClass> <ObjType>System.String</ObjType> <Value>root\CIMV2</Value> </Constant> <Constant> <TypeClass>String</TypeClass> <ObjType>System.String</ObjType> <Value>SELECT SocketDesignation FROM win32_processor WHERE CurrentClockSpeed!=MaxClockSpeed</Value> </Constant> </Function> <Constant> <TypeClass>String</TypeClass> <ObjType>System.String</ObjType> <Value /> </Constant> </Function> <Constant> <TypeClass>String</TypeClass> <ObjType>System.String</ObjType> <Value /> </Constant> </Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT Select @condition_id GO Declare @object_set_id int EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'CPU core power saving_ObjectSet', @facet=N'Server', @object_set_id=@object_set_id OUTPUT Select @object_set_id Declare @target_set_id int EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'CPU core power saving_ObjectSet', @type_skeleton=N'Server', @type=N'SERVER', @enabled=True, @target_set_id=@target_set_id OUTPUT Select @target_set_id GO Declare @policy_id int EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'CPU core power saving', @condition_name=N'CPU cores are running at maximum clock speed', @description=N'Checks that every CPU core is running at its maximum clock speed. If any are not, it is likely that a BIOS power-saving option has been turned on.', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'CPU core power saving_ObjectSet' Select @policy_id GO