AKN480157

Monitor_AKN480157 (UnitMonitor)

SQL Server performance may degrade when running on Windows Server 2008 R2 not using “High Performance” power plan

Knowledge Base article:

Element properties:

Source Code:

<UnitMonitor ID="Monitor_AKN480157" Comment="SupportTopic=TBD;VersionNumber=1.0.0.0;" Accessibility="Public" Enabled="true" Target="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="KnowledgeServicesLibrary!Microsoft.KnowledgeServices.Library.PowerShellMonitorEx" ConfirmDelivery="true">

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessage9c98f42f56c24ee0b3f8ef20b0d752d1">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Property[@Name='OSVersion']$</AlertParameter1>

      <AlertParameter2>$Data/Context/Property[@Name='PowerPlan']$</AlertParameter2>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Success" MonitorTypeStateID="Success" HealthState="Success"/>

    <OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <ScriptName>AKN480157.ps1</ScriptName>

    <Parameters/>

    <ScriptBody><Script>



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject



# Set up the output

$global:scriptoutput = new-object psobject

$scriptoutput | add-member NoteProperty "HasIssue" $false

$scriptoutput | add-member NoteProperty "OSVersion" ""

$scriptoutput | add-member NoteProperty "PowerPlan" ""

#----------------------------------------------------- 

# MAIN CODE SECTION

#----------------------------------------------------- 



# Environment



$scriptenv = New-Object PSObject

$scriptenv | Add-Member NoteProperty "ErrorFlag" $false



#$scriptenv | Add-Member NoteProperty "DatabaseLastBackupTime" $null



# Helper functions



# Main function

Function AdvisorRule($scriptargs, $scriptoutput) { 

	# All parameters should be populated outside of the main function. 

	# The main function should only include the detection logic so that it can be easyly reused by the Atlanta authoring tool.

	

	trap [Exception] { 

		$scriptenv.ErrorFlag = $true

		continue

	}

	

	# Initialize parameters

	$scriptenv.ErrorFlag = $false

	

	$scriptoutput.HasIssue = $false

	$scriptoutput.OSVersion = $null  

	$scriptoutput.PowerPlan = $null 

	



	# Set parameter values

	# Get OS Version

	$scriptoutput.OSVersion = ([Environment]::OSVersion).Version.ToString()

	# Get current Power Plan Setting

	$scriptoutput.PowerPlan = (Get-WmiObject -Class win32_powerplan -Namespace root\cimv2\power | Where-Object {$_.IsActive -eq $true}).ElementName



	# Detection Logic

	# If the OS is Winows Server 2008 R2 

	If(([Version]($scriptoutput.OSVersion)).Major -eq 6 -and ([Version]($scriptoutput.OSVersion)).Minor -eq 1){

		# If Power Plan is not set to High Performance 

		If($scriptoutput.PowerPlan -ne "High Performance"){

			If($scriptenv.ErrorFlag -ne $true){

				#Raise Alert

				$scriptoutput.HasIssue = $true

			}

		}

	}

}

AdvisorRule $scriptargs $scriptoutput



# set the output

$mom = new-object -comobject "MOM.ScriptAPI"

$bag = $mom.CreatePropertyBag()



if ($scriptoutput.HasIssue -ne $null)

{

	$bag.AddValue("HasIssue", $scriptoutput.HasIssue)

}



if ($scriptoutput.OSVersion -ne $null)

{

	$bag.AddValue("OSVersion", $scriptoutput.OSVersion)

}



if ($scriptoutput.PowerPlan -ne $null)

{

	$bag.AddValue("PowerPlan", $scriptoutput.PowerPlan)

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86397</Schedule>

    <ErrorExpression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

          <Value Type="Boolean">true</Value>

        </ValueExpression>

      </SimpleExpression>

    </ErrorExpression>

    <SuccessExpression>

      <Not>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Boolean">true</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </Not>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>