.SYNOPSIS
    List Sql Server Related Processes (Remote)
  .DESCRIPTION
    A good tool to monitor processes performance and utilization is Windows PerfMon; it uses WMI Provider
    to query performance values.
    This PoSh script queries process data with WMI provider from (remote) machines from a given list of machines for
    SQL Server related processes to give a quick overview of the utilization.
    It returns detail for memory and CPU usage in absolute and relative values.
    It returns the following values:
    - PID = Process ID
    - Name = Name of the process
    - Mem KB = Current working set memory in kilo byte
    - Mem % = Working set memory in relation to the total working set.
    - CPU sec = Total time in seconds of the process since start.
    - Cpu abs% = Percentage of total cpu usage time related to all current processes.
    - Cpu rel% = Percentage of cpu utilization within the last 250ms.
  .KNOWN ISSUSES
    - Not an issues, but a requirement on my own: Get the Sql Server instance name for the processes.
  .NOTE
    Author:  Olaf Helper
    Release: 2011-12-07
  .REQUIREMENTS
    PowerShell 1.0
    Permissions on the (remote) machines to query WMI data.

PowerShell
Skript bearbeiten|Remove
<# 
  .SYNOPSIS 
    List Sql Server Related Processes (Remote) 
  .DESCRIPTION 
    A good tool to monitor processes performance and utilization is Windows PerfMon; it uses WMI Provider 
    to query performance values. 
    This PoSh script queries process data with WMI provider from (remote) machines from a given list for 
    SQL Server related processes to give a quick overview of the utilization. 
    It returns detail for memory and CPU usage in absolute and relative values. 
    It returns the following values: 
    - PID = Process ID 
    - Name = Name of the process 
    - Mem KB = Current working set memory in kilo byte 
    - Mem % = Working set memory in relation to the total working set. 
    - CPU sec = Total time in seconds of the process since start. 
    - Cpu abs% = Percentage of total cpu usage time related to all current processes. 
    - Cpu rel% = Percentage of cpu utilization within the last 250ms. 
  .KNOWN ISSUSES 
    - Not an issues, but a requirement on my own: Get the Sql Server instance name for the processes. 
  .NOTE 
    Author:  Olaf Helper 
    Release: 2011-12-07 
  .REQUIREMENTS 
    PowerShell 1.0 
    Permissions on the (remote) machines to query WMI data. 
#> 
 
[String[]] $servers = @("localhost", ` 
                        "server1", ` 
                        "server2"); 
 
[String[]] $names = @("sqlservr.exe""sqlbrowser.exe""sqlwriter.exe", ` 
                      "msdtc.exe""msftesql.exe""msmdsrv.exe", ` 
                      "sqlagent.exe""sqlagent90.exe""ReportingServicesService.exe"); 
 
Clear-Host; 
Write-Host ("Processes snapshot from " + (Get-Date -format  yyyy-MM-ddTHH:mm:ss) + "`n"); 
 
# Table output format definition. 
$format = (@{ Label="PID"     ; Alignment="right" ;Width=5 ; ` 
              Expression={if     ($_.CpuRel -le 15) {$Host.UI.RawUI.ForegroundColor = "Black"} ` 
                          elseif ($_.CpuRel -lt 25) {$Host.UI.RawUI.ForegroundColor = "Blue"} ` 
                          elseif ($_.CpuRel -lt 45) {$Host.UI.RawUI.ForegroundColor = "Magenta"} ` 
                          else {$Host.UI.RawUI.ForegroundColor = "Red"} ` 
                          ;$_.Handle}; }, ` 
           @{ Label="Name"    ; Alignment="left"  ;Width=30; Expression={$_.ProcessName}; }, ` 
           @{ Label="Mem KB"  ; Alignment="right" ;Width=14; Expression={$_.WS / 1024}; FormatString="N0"; }, ` 
           @{ Label="Mem %"   ; Alignment="right" ;Width=6 ; Expression={100.0 * $_.WS / $mem.Sum}; FormatString="N1"; }, ` 
           @{ Label="CPU sec" ; Alignment="right" ;Width=13; Expression={($_.KernelModeTime + $_.UserModeTime) / 10000000}; FormatString="N1";}, ` 
           @{ Label="CPU abs%"; Alignment="right" ;Width=9 ; Expression={100.0 * ($_.KernelModeTime + $_.UserModeTime) / $cpuTotal1}; FormatString="N3";}, ` 
           @{ Label="CPU rel%"; Alignment="right" ;Width=9 ; Expression={$_.CpuRel}; FormatString="N1";} ` 
          ); 
 
foreach ($server in $servers) 
{ 
    Write-Host ("Processes on $server"-ForegroundColor DarkBlue; 
    $prc = Get-WmiObject Win32_Process -ComputerName $server | Sort-Object "Name""Handle"; 
 
    $mem = $prc | Measure-Object -Sum -Property "WS"; 
    $cpuUser = $prc | Measure-Object -Sum -Property "UserModeTime"; 
    $cpuKern = $prc | Measure-Object -Sum -Property "KernelModeTime"; 
    $cpuTotal1 = $cpuUser.Sum + $cpuKern.Sum; 
 
    $prc = $prc | Where-Object {$names -contains $_.Name}; 
    $result = $prc | Select-Object -Property Handle, ProcessName, WS, KernelModeTime, UserModeTime, CpuRel; 
     
    # Wait 200 ms and fetch processes again to build current CPU utilization. 
    [System.Threading.Thread]::Sleep(250); 
    $prc = Get-WmiObject Win32_Process -ComputerName $server | Sort-Object "Name""Handle"; 
    $cpuUser = $prc | Measure-Object -Sum -Property "UserModeTime"; 
    $cpuKern = $prc | Measure-Object -Sum -Property "KernelModeTime"; 
    $cpuTotal2 = $cpuUser.Sum + $cpuKern.Sum; 
 
    foreach ($res in $result) 
    { 
        $newPrc = $prc | Where-Object {$_.Handle -eq $res.Handle} ; 
         
        $cpuOld = $res.KernelModeTime + $res.UserModeTime; 
        $cpuNew = $newPrc.KernelModeTime + $newPrc.UserModeTime; 
        $procent = 100.0 * ($cpuNew - $cpuOld/ ($cpuTotal2 - $cpuTotal1); 
         
        $res.CpuRel = $procent; 
    } 
 
    # Output as formated table. 
    Write-Host ("Total CPU Time (sec): " + ($cpuTotal1 / 10000000).ToString("N1")); 
    Write-Host ("Total WorkingSet  KB: " + ($mem.Sum / 1024).ToString("N0")); 
 
    Write-Output $result | Format-Table  ($format); 
 
} 
 
Write-Host ("Finished");