$ErrorActionPreference = "Stop"
$server = "$(ESCAPE_DQUOTE(SRVR))"
$sampleInterval = 1
$maxSamples = 15
$numberOfHeaderRowsToSkip = 1
$date = Get-Date
$reportBody = "
Report as of $date
"
$mailServer = "YOURSMTPSERVER"
$mailFrom = "from@domain.com"
$mailTo = "to@domain.com"
$mailSubject = "$(ESCAPE_DQUOTE(SRVR)) email subject"
$reportHeader = ""
$perfMonExclusions = @("SERVERDEV01")
$perfMonExclusionsProcessor = @()
$perfMonExclusionsMemory = @()
$perfMonExclusionsDisk = @()
$perfMonExclusionsNetwork = @()
if (-Not ($perfMonExclusions -contains $server)) {
$list = New-Object System.Collections.Generic.List[System.Object]
$computerName = $server
if ($computerName.Contains("\")) {
$computerName = $computerName.Substring(0, $computerName.IndexOf("\"))
}
$result2 = New-Object System.Data.DataTable
$result2.Columns.Add("LogDate", "System.DateTime") | Out-Null
$result2.Columns.Add("Text", "System.String") | Out-Null
$curDate = (Get-Date).ToString()
[System.Collections.ArrayList]$counters = @()
$counters += "\\$computerName\VM Processor(_Total)\% Processor Time"
$counters += "\\$computerName\VM Processor(_Total)\CPU Stolen Time"
$counters += "\\$computerName\VM Memory\Memory Ballooned in MB"
$counters += "\\$computerName\VM Memory\Memory Swapped in MB"
$prefix = ""
if (!$server.contains("\")) {
$prefix = "\\$computerName\SQLServer"
}
else {
$prefix = "\\$computerName\MSSQL$" + $server.Substring($server.IndexOf("\") + 1)
}
$counters += ($prefix + ":Buffer Manager\total pages")
$counters += ($prefix + ":Buffer Manager\Page life expectancy")
$counters += ($prefix + ":Memory Node(000)\Total Node Memory (KB)")
try {
# counters that may vary: VM-related, buffer manager, etc.
$availCounters = powershell.exe -command "Get-Counter -ComputerName$computerName -ListSet * | select -expand Counter"
$availCounters = $availCounters | foreach {if ($_ -inotmatch [RegEx]::Escape("Memory Node(*)")) {$_.replace('(*)', '(_Total)')} else {$_.replace('(*)', '(000)')}} | where {$counters -contains $_} | foreach {"'$_'"}
if (-Not ($availCounters -contains ("'" + $prefix + ":Buffer Manager\Page life expectancy'"))) {
throw "Some SQL Server perf counters are unavailable, exiting"
}
$availCounters = $availCounters -join ","
# counters that must exist
$availCounters += ",'\\$computerName\System\Processor Queue Length'"
$availCounters += ",'\\$computerName\Processor(_Total)\% Processor Time'"
$availCounters += ",'\\$computerName\Processor(_Total)\% Privileged Time'"
$availCounters += ",'\\$computerName\Process(*)\% Processor Time'"
$availCounters += ",'\\$computerName\Memory\Available Bytes'"
$availCounters += ",'\\$computerName\Process(*)\Working Set'"
$availCounters += ",'\\$computerName\Memory\Cache Bytes'"
$availCounters += ",'\\$computerName\Memory\Pool Nonpaged Bytes'"
$availCounters += ",'\\$computerName\Paging File(_Total)\% Usage'"
$availCounters += ",'\\$computerName\Memory\Pages/sec'"
$availCounters += ",'\\$computerName\PhysicalDisk(*)\Avg. Disk sec/Read'"
$availCounters += ",'\\$computerName\PhysicalDisk(*)\Avg. Disk sec/Write'"
$availCounters += ",'\\$computerName\PhysicalDisk(*)\Disk Bytes/sec'"
$availCounters += ",'\\$computerName\PhysicalDisk(*)\Current Disk Queue Length'"
$availCounters += ",'\\$computerName\Network Interface(*)\Bytes Received/sec'"
$availCounters += ",'\\$computerName\Network Interface(*)\Bytes Sent/sec'"
$availCounters += ",'\\$computerName\Network Interface(*)\Current Bandwidth'"
$availCounters += ",'\\$computerName\Network Interface(*)\Output Queue Length'"
$availCounters += ",'\\$computerName\Network Interface(*)\Packets Outbound Errors'"
$availCounters += ",'\\$computerName\Network Interface(*)\Packets Received Errors'"
$availCounters += ",'\\$computerName\Process(*)\IO Data Bytes/sec'"
$results = powershell.exe -command "`$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size(500,300); Get-Counter -ComputerName$computerName -Counter @($availCounters) -SampleInterval$sampleInterval -MaxSamples$maxSamples -ErrorAction SilentlyContinue | select -ExpandProperty CounterSamples | select Path, '|', CookedValue | Format-Table -Property * -AutoSize | Out-String -Width 500"
if ($results[0].Contains("The specified counter path could not be interpreted")) {
throw "Some perf counters are unavailable, exiting"
}
$pos = $results[1].IndexOf("|")
$results2 = $results | where {$_.StartsWith("\\")} | select @{l="Path";e={$_.Substring(0, $pos).Trim()}}, @{l="CookedValue";e={$_.Substring($pos, $_.length - $pos).Trim()}}
$results = @{}
foreach ($result in $results2) {
if (!$results.ContainsKey($result.Path)) {
$results.($result.Path) = [math]::Round(($results2 | where {$_.Path -eq $result.Path} | Measure-Object -Property CookedValue -Average).Average, 2)
}
}
# Processor
if (-Not ($perfMonExclusionsProcessor -contains $server)) {
$cores = (Get-WmiObject Win32_Processor -computer $computerName | select SocketDesignation | Measure-Object).Count
if ($results["\\$computerName\System\Processor Queue Length"] -gt $cores) {
if ($results["\\$computerName\Processor(_Total)\% Processor Time"] -gt 80 `
-Or $results["\\$computerName\Processor(_Total)\% Privileged Time"] -gt 80 `
-Or $results["\\$computerName\VM Processor(_Total)\% Processor Time"] -gt 80) {
$ProcessorQueueLength = $results["\\$computerName\System\Processor Queue Length"]
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "Processor Queue Length$ProcessorQueueLength >$cores cores and Processor/Privileged Time > 80%"
$result2.Rows.Add($newRow)
$TopCPUKey = ""
$TopCPUValue = 0
foreach ($r2 in ($results.Keys | where {$_.EndsWith("% processor time")})) {
if ($results[$r2] -gt $TopCPUValue -And !$r2.Contains("(_total)") -And !$r2.Contains("(idle)")) {
$TopCPUKey = $r2
$TopCPUValue = $results[$r2]
}
}
$TopCPUKey = [regex]::match($TopCPUKey,'\(([^\)]+)\)').Groups[1].Value
$TopCPUValue = [math]::Round($TopCPUValue, 2)
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "Top CPU consumer: '$TopCPUKey' with$TopCPUValue %"
$result2.Rows.Add($newRow)
}
}
if ($results["\\$computerName\VM Processor(_Total)\CPU Stolen Time"] -gt 40) {
$CPUStolenTime = $results["\\$computerName\VM Processor(_Total)\CPU Stolen Time"]
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "VM CPU Stolen Time$CPUStolenTime > 40 ms"
$result2.Rows.Add($newRow)
}
}
# Memory
if (-Not ($perfMonExclusionsMemory -contains $server)) {
$MemUsage = 0
if (!$results["\\$computerName\Memory\Available Bytes"] -Or !$results["\\$computerName\Process(_Total)\Working Set"] -Or !$results["\\$computerName\Memory\Cache Bytes"] -Or !$results["\\$computerName\Memory\Pool Nonpaged Bytes"]) {
$MemUsage = 91
}
else {
$MemUsage = 100 - ($results["\\$computerName\Memory\Available Bytes"] / ($results["\\$computerName\Memory\Available Bytes"] + $results["\\$computerName\Process(_Total)\Working Set"] + $results["\\$computerName\Memory\Cache Bytes"] + $results["\\$computerName\Memory\Pool Nonpaged Bytes"]) * 100)
}
if ($MemUsage -gt 90 `
-And $results["\\$computerName\Paging File(_Total)\% Usage"] -gt 90 `
-And $results["\\$computerName\Memory\Pages/sec"] -gt 25) {
$PagingFile = $results["\\$computerName\Paging File(_Total)\% Usage"]
$PagesSec = $results["\\$computerName\Memory\Pages/sec"]
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "RAM Utilization is$MemUsage % and Page File Utilization is$PagingFile % and Memory Pages Per Second$PagesSec > 25 pps"
$result2.Rows.Add($newRow)
$TopMemKey = ""
$TopMemValue = 0
foreach ($r2 in ($results.Keys | where {$_.EndsWith("working set")})) {
if ($results[$r2] -gt $TopMemValue -And !$r2.Contains("(_total)") -And !$r2.Contains("(idle)")) {
$TopMemKey = $r2
$TopMemValue = $results[$r2]
}
}
$TopMemKey = [regex]::match($TopMemKey,'\(([^\)]+)\)').Groups[1].Value
$TopMemValue = [math]::Round($TopMemValue, 2)
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "Top memory consumer: '$TopMemKey' with$TopMemValue bytes"
$result2.Rows.Add($newRow)
}
if ($results["\\$computerName\VM Memory\Memory Ballooned in MB"] -gt 512) {
$MemBallooned = $results["\\$computerName\VM Memory\Memory Ballooned in MB"]
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "VM Memory Ballooned is " + $MemBallooned + " MB and can't be used"
$result2.Rows.Add($newRow)
}
if ($results["\\$computerName\VM Memory\Memory Swapped in MB"] -gt 512) {
$MemSwapped = $results["\\$computerName\VM Memory\Memory Swapped in MB"]
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "VM Memory Swapped is " + $MemSwapped + "MB and can't be used"
$result2.Rows.Add($newRow)
}
$TotalMemoryMB = $results[$prefix + ":Memory Node(000)\Total Node Memory (KB)"] / 1000
if (!$TotalMemoryMB) {
$TotalMemoryMB = $results[$prefix + ":Buffer Manager\total pages"] / 128
}
$PageLifeExpectancy = $results[$prefix + ":Buffer Manager\Page life expectancy"]
if ($TotalMemoryMB -And $PageLifeExpectancy) {
$TotalMemoryMB = [math]::Round($TotalMemoryMB, 2)
$BufferPoolRate = [math]::Round($TotalMemoryMB / $PageLifeExpectancy, 2)
if ($BufferPoolRate -gt 20) {
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "Buffer Pool Rate$BufferPoolRate > 20 MB/sec. Memory:$TotalMemoryMB PLE:$PageLifeExpectancy"
$result2.Rows.Add($newRow)
}
}
}
$IOissuesFound = 0
# Disk
if (-Not ($perfMonExclusionsDisk -contains $server)) {
foreach ($r in ($results.Keys | where {($_.EndsWith("avg. disk sec/read") -Or $_.EndsWith("avg. disk sec/write")) -And !$_.Contains("(_total)")})) {
$counter = $r.Substring($r.LastIndexOf("\") + 1)
if ($results[$r] -gt 0.015) {
$IOissuesFound = 1
$drive = [regex]::match($r,'\(([^\)]+)\)').Groups[1].Value
$val = $results[$r]
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "'$drive'$counter$val > 0.015 sec"
$result2.Rows.Add($newRow)
$complement = $r
if ($counter.EndsWith("read")) {
$complement = $complement.Replace("read", "write")
}
else {
$complement = $complement.Replace("write", "read")
}
if ($results[$complement] -gt 0.015) {
$counter2 = $complement.Substring($complement.LastIndexOf("\") + 1)
$val = $results[$complement]
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "'$drive'$counter2$val > 0.015 sec"
$result2.Rows.Add($newRow)
$results[$complement] = 0
}
$DiskBytesSec = $results["\\$computerName\PhysicalDisk($drive)\Disk Bytes/sec"]
if ($DiskBytesSec -lt 204800) {
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "'$drive' bytes/sec$DiskBytesSec < 204800"
$result2.Rows.Add($newRow)
}
$CurDiskQueueLength = $results["\\$computerName\PhysicalDisk($drive)\Current Disk Queue Length"]
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "'$drive' current queue length$CurDiskQueueLength"
$result2.Rows.Add($newRow)
}
}
}
# Network
if (-Not ($perfMonExclusionsNetwork -contains $server)) {
foreach ($r in ($results.Keys | where {$_.EndsWith("current bandwidth") -And !$_.Contains("(_total)")})) {
$interface = [regex]::match($r,'\(([^\)]+)\)').Groups[1].Value
$bandwidth = $results[$r]/8
$val = $results[$r.Replace("current bandwidth", "bytes received/sec")]
$NetworkIssuesFound = 0
if ($val -gt $bandwidth*0.8) {
$NetworkIssuesFound = 1
$IOIssuesFound = 1
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "'$interface' bytes received/sec$val > 80% bandwidth"
$result2.Rows.Add($newRow)
}
$val = $results[$r.Replace("current bandwidth", "bytes sent/sec")]
if ($val -gt $bandwidth*0.8) {
$NetworkIssuesFound = 1
$IOIssuesFound = 1
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "'$interface' bytes sent/sec$val > 80% bandwidth"
$result2.Rows.Add($newRow)
}
if ($NetworkIssuesFound) {
$val = $results[$r.Replace("current bandwidth", "output queue length")]
if ($val -gt 2) {
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "'$interface' output queue length$val > 2"
$result2.Rows.Add($newRow)
}
$val = $results[$r.Replace("current bandwidth", "packets outbound errors")]
if ($val -gt 0) {
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "'$interface' packets outbound errors$val"
$result2.Rows.Add($newRow)
}
$val = $results[$r.Replace("current bandwidth", "packets received errors")]
if ($val -gt 0) {
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "'$interface' packets received errors$val"
$result2.Rows.Add($newRow)
}
}
}
}
# Disk / Network
if (-Not ($perfMonExclusionsDisk -contains $server -And $perfMonExclusionsNetwork -contains $server)) {
if ($IOissuesFound) {
$TopIOKey = ""
$TopIOValue = 0
foreach ($r2 in ($results.Keys | where {$_.EndsWith("io data bytes/sec")})) {
if ($results[$r2] -gt $TopIOValue -And !$r2.Contains("(_total)")) {
$TopIOKey = $r2
$TopIOValue = $results[$r2]
}
}
$TopIOKey = [regex]::match($TopIOKey,'\(([^\)]+)\)').Groups[1].Value
$TopIOValue = [math]::Round($TopIOValue, 2)
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = "Top I/O consumer (file, network and device): '$TopIOKey' with$TopIOValue bytes/sec"
$result2.Rows.Add($newRow)
}
}
}
catch {
$newRow = $result2.NewRow()
$newRow.LogDate = $curDate
$newRow.Text = " at line " + $_.InvocationInfo.ScriptLineNumber + " " + $_.Exception.Message
$result2.Rows.Add($newRow)
}
if ($result2.Rows.Count -gt 0) {
foreach ($r in ($result2 | select LogDate, Text)) { $list.add($r) }
}
}
if ($list -eq $NULL -or $list.count -eq 0) {
exit
}
[string]$result = $list | ConvertTo-HTML -Fragment | Out-String
[xml]$result = $result.Replace("`0", "")
for ($i = 0; $i -lt $result.table.tr.count - $numberOfHeaderRowsToSkip; $i++) {
$class = $result.CreateAttribute("class")
$class.value = if($i % 2 -eq 0) {"even"} else {"odd"}
$result.table.tr[$i+$numberOfHeaderRowsToSkip].attributes.append($class) | Out-Null
}
$reportBody += $result.InnerXml
$message = New-Object System.Net.Mail.MailMessage $mailFrom, $mailTo
$message.Subject = $mailSubject
$message.IsBodyHTML = $true
$message.Body = ConvertTo-HTML -head $reportHeader -body $reportBody
$smtp = New-Object Net.Mail.SmtpClient($mailServer)
$smtp.Send($message)
$message.Dispose()