CPU Usage for SQL Instance Datasource

Microsoft.SQLServer.2012.DBEngine.CPUUsagePercent.DataSource (DataSourceModuleType)

Datasource for CPU Usage for SQL Instance

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2012.DBEngine.CPUUsagePercent.DataSource" Accessibility="Public">

  <Configuration>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="IntervalSeconds" type="xsd:integer"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="TimeoutSeconds" type="xsd:integer"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="SyncTime" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="ComputerName" type="xsd:string"/>

  </Configuration>

  <OverrideableParameters>

    <OverrideableParameter ID="IntervalSeconds" Selector="$Config/IntervalSeconds$" ParameterType="int"/>

    <OverrideableParameter ID="TimeoutSeconds" Selector="$Config/TimeoutSeconds$" ParameterType="int"/>

    <OverrideableParameter ID="SyncTime" Selector="$Config/SyncTime$" ParameterType="string"/>

  </OverrideableParameters>

  <ModuleImplementation>

    <Composite>

      <MemberModules>

        <DataSource ID="Scheduler" TypeID="System!System.Scheduler">

          <Scheduler>

            <SimpleReccuringSchedule>

              <Interval>$Config/IntervalSeconds$</Interval>

              <SyncTime>$Config/SyncTime$</SyncTime>

            </SimpleReccuringSchedule>

            <ExcludeDates/>

          </Scheduler>

        </DataSource>

        <ProbeAction ID="PS" TypeID="Windows!Microsoft.Windows.PowerShellPropertyBagProbe">

          <ScriptName>CPUUsagePercentDataSource.ps1</ScriptName>

          <ScriptBody><Script>#CPUUsagePercentDataSource.ps1

param($computerName, $SQL_WMI_NAMESPACE, $serviceName, $sqlInstanceName)



$SCRIPT_EVENT_ID = 4001



$DEBUG_MODE = 0

$DEBUG_MODULE = "CPUUsagePercentDataSource.ps1"

$DEBUG_MSG = ""

$DEBUG_SA = $null

$DEBUG_PWD = $null



#Event Severity values

$INFORMATION_EVENT_TYPE = 0

$ERROR_EVENT_TYPE = 1 



$ManagementGroupID = '$Target/ManagementGroup/Id$'



function BuildConnectionString(

    [String] $pHostName, 

    [String] $pInstanceName, 

    [String] $databaseName, 

    [String] $sql_namespace,

    [String] $connectionString = $null,

    [int] $port = 0,

    [String] $user = $null, 

    [String] $password = $null) 

{    

    if (($connectionString.Length -ne 0) -and ($port -ne 0)) {

        $builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder

        $builder["Data Source"] = $connectionString + "," + $port

        $builder["Initial Catalog"] = $databaseName

        if (($user.Length -ne 0) -and ($password.Length -ne 0)) {

            $builder["User ID"] = $user

            $builder["Password"] = $password

        }

        else {

            $builder["Integrated Security"] = 'SSPI'

        }

        return $builder.ConnectionString

    }   



    $namespace = "root\Microsoft\SqlServer\" + $sql_namespace

    $class = "ServerNetworkProtocolProperty"

    $serverName =  $pHostName + "\" + $pInstanceName

    $listenAll = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.PropertyName -eq "ListenOnAllIPs")}

    if($listenAll.PropertyNumVal -eq 1) {

        $tcpipAll = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {

                        ($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq "IPAll")-and($_.PropertyName -eq "TcpPort")}

            if($tcpipAll.PropertyStrVal -eq '') {

                $tcpipAll = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {

                                ($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq "IPAll")-and($_.PropertyName -eq "TcpDynamicPorts")}

            }

            if($tcpipAll.PropertyStrVal -ne '') {

                    $serverName =  $serverName + "," + $tcpipAll.PropertyStrVal

            }

        }

        else {

        $ipAddressName = (Get-WmiObject -Namespace $namespace -Class $class | Where-Object {

                            ($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -ne "")-and($_.PropertyName -eq "Enabled") -and ($_.PropertyNumVal -eq 1)}) | select -first 1 | select -ExpandProperty IPAddressName

        if($ipAddressName -ne $null) {

            $tcp = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {

                            ($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq $ipAddressName)-and( ($_.PropertyName -eq "TcpPort") -or ($_.PropertyName -eq "TcpDynamicPorts")) -and ($_.PropertyStrVal -ne '')} | select -ExpandProperty PropertyStrVal

            $ip = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {

                            ($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq $ipAddressName)-and( $_.PropertyName -eq "IpAddress") -and ($_.PropertyStrVal -ne '')} | select -ExpandProperty PropertyStrVal

            if($ip -ne $null) {

                $serverName =  $ip + "," + $tcp

            }

            else {

                $serverName =  $serverName + "," + $tcp

            }

        }

    } 



    if (($user.Length -ne 0) -and ($password.Length -ne 0)) {

        return 'Data Source="' + ($serverName -replace '"', '""') + '";Initial Catalog="' + ($databaseName -replace '"', '""') + '";User ID="' + ($user -replace '"', '""')  + '";Password="' + ($password -replace '"', '""')+ '";' 

    }



	return 'Data Source="' + ($serverName -replace '"', '""') + '";Initial Catalog="' + ($databaseName -replace '"', '""') + '";Integrated Security=SSPI'

}



function GetCoresCountThreshold($connectionString, $coresCount) {



	[int]$maxWorkersCount = SqlQueryScalar $connectionString "SELECT max_workers_count as value FROM sys.dm_os_sys_info"

	

	if ($maxWorkersCount -eq $null -or $maxWorkersCount -eq 0) {

		

		$maxWorkersCount = (256 +($coresCount - 4) * 8) 

		

		$os = (Get-WmiObject Win32_OperatingSystem).OSArchitecture

		$is64bit = ($os -eq "64-bit")

		

		if ($is64bit) {

			$maxWorkersCount *= 2

		}

	}

	

	return $maxWorkersCount;

}



function GetSQLInstanceNameBySQLServiceName ($sqlServiceName) {



	$instanceName = [regex]::match($sqlServiceName, '(?' + [char]60 + '=\w+\$)\w+').Groups[0].Value

	if ($instanceName -eq '') {

		$instanceName = $sqlServiceName

	}	

	return $instanceName

}



function GetSQLAllowedCoreCount($connectionString, $logicalCoresCount) {



	[int]$affinity = SqlQueryScalar $connectionString "SELECT value FROM sys.configurations WHERE NAME = 'AFFINITY MASK'"	

		 

	$affinityBinStr = [Convert]::ToString($affinity, 2)

	$allowedCoreCount = ($affinityBinStr.ToCharArray() | where { $_ -eq '1' }).Length

	

	if ($allowedCoreCount -eq $null -or $allowedCoreCount -eq 0) {

		$allowedCoreCount = $logicalCoresCount

	}

	return $allowedCoreCount		

}





function SqlQueryScalar($connectionString, $query) {			

	

	$res = "";

		

	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

	try

	{			

		$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

		$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

		$DataSet = New-Object System.Data.DataSet

		

		$SqlConnection.ConnectionString = $connectionString

		 

		$SqlCmd.CommandText = $query

		$SqlCmd.Connection = $SqlConnection

		$SqlAdapter.SelectCommand = $SqlCmd

		$SqlAdapter.Fill($DataSet)|out-null			

		

		$res = ($DataSet.Tables[0] | select -First 1).value		

	}

	catch

	{

		$SqlConnection.Close()

		throw $_

	}

	$SqlConnection.Close()

	return $res

}



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

# localized performance counters 

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



# Retrieve the default language identifier of the current user. For most languages,

# you use the primary language identifier only to retrieve the text. In Windows XP and 

# Windows Server 2003, you use the complete language identifier to retrieve Chinese 

# text. In Windows Vista, you use the complete language identifier to retrieve Portuguese text.

function Get-GetLanguageId([String[]] $osVersion)

{

    $LANG_PORTUGUESE = 0x16

    $LANG_CHINESE = 0x04

    

    $cul = Get-UICulture

    $lcid = $cul.LCID

        

    $lcidPrimary = $($lcid -band 0xff)

    

    if ( 

        ($LANG_PORTUGUESE -eq $lcidPrimary -and $osVersion[0] -gt 5) -or #Windows Vista and later

        ($LANG_CHINESE -eq $lcidPrimary -and ($osVersion[0] -eq 5 -and $osVersion[1] -ge 1)) #XP and Windows Server 2003

        )

    {

        return $lcid

    }

    

    return $lcidPrimary

}



# Build a hash array of offsets into the counter buffer. Use the index

# values from the performance data queries to access the offsets.

function Get-RegLocalizedPerfCounterNames($computerName = $env:COMPUTERNAME)

{

    $path  = "" 

    #get OS version

    $osvi = Get-WmiObject -class Win32_OperatingSystem -computerName $computerName

    $osVersion = $osvi.Version.split(".")

    #get language ID

    $language = "{0:X0}" -f $(Get-GetLanguageId($osVersion))

    if($language.Length -eq 2)

    {

        $language = "0{0}" -f $language

    }

    #if OS windows 7, 8, 2008R2 and 2012

    if( $osVersion[0] -ge 6 -and $osVersion[1] -ge 1 )

    {

        $path  = "hklm:SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\CurrentLanguage"

    }

    #if OS Windows XP, 2003, Vista, 2008

    if( ($osVersion[0] -eq 6 -and $osVersion[1] -eq 0 ) -or ($osVersion[0] -eq 5 -and $osVersion[1] -ge 1 ) )

    {

        $path  = "hklm:SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\{0}" -f $language

    }

    #if no data in regestry when return null

    $val = Get-ItemProperty -Path $path -Name "Counter" -ErrorAction SilentlyContinue

    if (($val -eq $null) -or ($val.Length -eq 0))

    {

        return $null

    }

    #return registry values  

    $hashArray = @{}

    for($i=0; $i -lt $val.Counter.Count; $i=$i+2)

    {

        [UInt32] $key = [UInt32]$val.Counter[$i]

        if ($hashArray.ContainsKey($key) -ne $true)

        {

            $hashArray.Add($key, $val.Counter[$i+1]) 

        }

    }

    return $hashArray

}



#Returns the performance object name or counter name corresponding to the specified index.

Function Get-PdhLookupPerfNameByIndex([UInt32] $ID, $computerName = $env:COMPUTERNAME)

{

    $code = '[DllImport("pdh.dll", SetLastError=true, CharSet=CharSet.Unicode)] public static extern UInt32 PdhLookupPerfNameByIndex(string szMachineName, uint dwNameIndex, System.Text.StringBuilder szNameBuffer, ref uint pcchNameBufferSize);'

 

    $Buffer = New-Object System.Text.StringBuilder(1024)

    [UInt32]$BufferSize = $Buffer.Capacity

 

    $t = Add-Type -MemberDefinition $code -PassThru -Name PerfCounter -Namespace Utility

    $rv = $t::PdhLookupPerfNameByIndex($computerName, $id, $Buffer, [Ref]$BufferSize)

 

    if ($rv -eq 0)

    {

        return $Buffer.ToString().Substring(0, $BufferSize-1)

    }

    else

    {

        Throw 'Get-PdhLookupPerfNameByIndex : Unable to retrieve localized name for performance counter ID ({0}) on computer "{1}".' -f $ID, $ComputerName

    }

}



function Get-LocalizedPerfCounter($arrayCounters, [String] $format, $paramters)

{

    $cul = Get-UICulture

    #English language

    if(  $($cul.LCID -band 0xff) -ne 0x09)

    {

        #try to get locolized values

        if ( $arrayCounters -ne $null)

        {

            #from regestry

            for($i=0; $i -lt $paramters.Count; $i++)

            {   

                [UInt32] $id = [UInt32]$paramters[$i][0]

                if ($arrayCounters.ContainsKey($id) -eq $true)

                {

                    $paramters[$i][1] = $arrayCounters[$id]

                }

                else

                {

                    Throw 'Get-PerfCounter : Unable to retrieve localized name. (Index : {0}  Name : {1})' -f $id,$paramters[$i][1]

                }

            }  

        }

        else

        {

            #win32 api

            for($i=0; $i -lt $paramters.Count; $i++)

            {   

                [UInt32] $id = [UInt32]$paramters[$i][0]

                $paramters[$i][1] = Get-PdhLookupPerfNameByIndex $id

            }   

        }

    }

    #get counter

    $counter = $format -f $paramters[0][1], $paramters[1][1]

    return (Get-Counter ($counter)) 

}

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

#The function returns service or "Unknown" state

#Input:

#	server - compute name

#	service - system service name

#	InstanceName - sql server instance name

#Output:

#	service state or "Unknown" state

function GetServiceState($server, $service, $InstanceName)

{

	try {

		if ($service -eq "MSSQL") {

			$service = "MSSQL`${0}" -f $InstanceName 

		}

		$namespace = "root/cimv2"

        $obje = Get-WmiObject -Namespace $namespace -ComputerName $server -Class "win32_service" -ErrorAction SilentlyContinue | where {$_.name -like $service } 

		if ($obje -ne $null) {

            return $obje.State

        }

    }

    catch {

    }

    return "Unknown"

}



function Main {		

	param($computerName, $SQL_WMI_NAMESPACE, $serviceName, $sqlInstanceName)



	# 

	# Prepare MOM API and property bag object

	#

	$api = New-Object -comObject "MOM.ScriptAPI"

	$msg  = [Environment]::NewLine



	if ($serviceName -eq "MSSQL") {

		$serviceName = "MSSQL`${0}" -f $sqlInstanceName 

	}



    $msg += "Computer Name = '$computerName' WMI = '$SQL_WMI_NAMESPACE' Service Name = '$serviceName' SQL Instance Name = '$sqlInstanceName'"

    $msg += [Environment]::NewLine

	

	#if service is not in running state when exit without any error

	$state = GetServiceState $computerName $serviceName $sqlInstanceName

	if(($state -ne "Running") -and ($state -ne "Unknown"))

	{

		if($DEBUG_MODE -eq 1) {

			$api.LogScriptEvent($DEBUG_MODULE, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $msg)

		}

		return

	}



	try

	{

		$perfCounterNames = Get-RegLocalizedPerfCounterNames

		$counters = (Get-LocalizedPerfCounter $perfCounterNames $("\{0}(sqlservr*)\{1}") $(@( 230 , "Process" ), @( 784 , "ID Process" ))).CounterSamples

				

		$logicalCoresCount = (Get-WmiObject Win32_ComputerSystem).NumberOfLogicalProcessors		

						 

		$counters | foreach {

			try {

                $nmspace = "root\Microsoft\SqlServer\"+$SQL_WMI_NAMESPACE

				

                $processID = $_.CookedValue

				$counterName = [regex]::match($_.Path,'(?' + [char]60 + '=\().*(?=\))').Groups[0].Value

				$serviceName = (get-wmiobject win32_Service | Where { $_.ProcessID -eq $processID }) | select -first 1 | select -ExpandProperty Name

                

                #check service

                $INSTANCEID = (Get-WmiObject -Namespace $nmspace -Query "select * from SqlServiceAdvancedProperty where SQLServiceType ='1' AND ServiceName='$serviceName' and PropertyName='INSTANCEID'").PropertyStrValue

                if(($INSTANCEID -ne $null) -and ($INSTANCEID -ne ''))

                {

                    $cpuUsage = (Get-LocalizedPerfCounter $perfCounterNames $("\{0}($counterName)\{1}") $(@( 230 , "Process" ), @( 6 , "% Processor Time" ))).CounterSamples[0].CookedValue

				    $threadCount = (Get-LocalizedPerfCounter $perfCounterNames $("\{0}($counterName)\{1}") $(@( 230 , "Process" ), @( 680 , "Thread Count" ))).CounterSamples[0].CookedValue

				

                    $vsname = (Get-WmiObject -Namespace $nmspace -Query "select * from SqlServiceAdvancedProperty where SQLServiceType ='1' AND ServiceName='$serviceName' and PropertyName='VSNAME'").PropertyStrValue

                    if(($vsname -ne $null) -and ($vsname -ne '')){

                        $computerName = $vsname

                    }



				    $instanceName = GetSQLInstanceNameBySQLServiceName $serviceName



                    $connectionString = BuildConnectionString $computerName $instanceName 'master' $SQL_WMI_NAMESPACE

				

				    $allowedCoreCount = GetSQLAllowedCoreCount $connectionString $logicalCoresCount

				

				    $cpuUsage /= [int]$allowedCoreCount				

				

				    $threshold  = GetCoresCountThreshold $connectionString $logicalCoresCount				

				    $freeThreadCount = $threshold - $threadCount

				

				    $bag = $api.CreatePropertyBag()

				    $bag.AddValue("Name", $serviceName)             

				    $bag.AddValue("CPUUsage", [double]$cpuUsage)

				    $bag.AddValue("ThreadCount", [UInt32]$threadCount)

				    $bag.AddValue("ThreadCountThreshold", $threshold)

				    $bag.AddValue("FreeThreadCount", $freeThreadCount)	

				

				    $bag



				    if($DEBUG_MODE -eq 1) {

                        $msg += "'$connectionString' CPUUsage=$cpuUsage ThreadCount=$threadCount ThreadCountThreshold=$threshold FreeThreadCount=$freeThreadCount"

		                $msg += [Environment]::NewLine

                    }

                }

			}		

			catch {

				$msg += [Environment]::NewLine

				$msg += $_.Exception.Message

				$header = "Management Group: $Target/ManagementGroup/Name$. Script: {0} Module: {1}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE

				$msg += "Error occured during CPU Usage for SQL Instances data source executing.{0}Computer:{1} {0}Reason: {2}" -f [Environment]::NewLine, $env:COMPUTERNAME, $_.Exception.Message

				$api.LogScriptEvent($header, $SCRIPT_EVENT_ID, $ERROR_EVENT_TYPE, $msg)		

				# in the case of service was stopped or something wrong another was happend	

			}

		}	

	}

	catch

	{

		$header = "Management Group: $Target/ManagementGroup/Name$. Script: {0} Module: {1}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE

		$msg += "Error occured during CPU Usage for SQL Instances data source executing.{0}Computer:{1} {0}Reason: {2}" -f [Environment]::NewLine, $env:COMPUTERNAME, $_.Exception.Message

		$msg += $err

		$api.LogScriptEvent($header, $SCRIPT_EVENT_ID, $ERROR_EVENT_TYPE, $msg)		

	}



	if($DEBUG_MODE -eq 1) {

    	$api.LogScriptEvent($DEBUG_MODULE, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $msg)

    }

}



main $computerName $SQL_WMI_NAMESPACE $serviceName $sqlInstanceName</Script></ScriptBody>

          <SnapIns/>

          <Parameters>

            <Parameter>

              <Name>computerName</Name>

              <Value>$Config/ComputerName$</Value>

            </Parameter>

            <Parameter>

              <Name>SQL_WMI_NAMESPACE</Name>

              <Value>ComputerManagement11</Value>

            </Parameter>

            <Parameter>

              <Name>serviceName</Name>

              <Value>$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$</Value>

            </Parameter>

            <Parameter>

              <Name>sqlInstanceName</Name>

              <Value>$Target/Property[Type="SQL!Microsoft.SQLServer.ServerRole"]/InstanceName$</Value>

            </Parameter>

          </Parameters>

          <TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>

          <StrictErrorHandling>true</StrictErrorHandling>

        </ProbeAction>

      </MemberModules>

      <Composition>

        <Node ID="PS">

          <Node ID="Scheduler"/>

        </Node>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>System!System.PropertyBagData</OutputType>

</DataSourceModuleType>