SQL Server Perfmon powershell script

Topics: User Forum
Aug 13, 2012 at 2:27 PM

Here's a sample Powershell script I wrote for Polymon to monitor several key perfmon metrics for SQL Servers.  

Create a new blank Powershell monitor, and paste the code below.  Set your desired hostname for which SQL server to monitor.

It's pretty intuitive what to do if you wish to add other perfmon counters, but you'll need to look up the exact textual name of the perfmon counter you'd like to add.

No guarantees that it's the most elegant code ever written, but it's at least minimally effective for my purposes...

#Set Monitor status as follows:
#   $Status.StatusText=" Page File - % Usage: "+$pagefileusage
# where StatusID is: 1=OK, 2=Warn, 3=Fail

 $targetserver = "enter your hostname here"
$processor_count = 0 ;#Enter "0" for "Total" otherwise enter the number of processors.
$availablebytes = ((get-counter -counter "Memory\Available Bytes" -comp $targetserver).countersamples[0].cookedvalue)/1024
$pagefileusage = (get-counter -counter "Paging File(_Total)\% Usage" -comp $targetserver).countersamples[0].cookedvalue
$Disksecperwrite = (get-counter -counter "PhysicalDisk(_Total)\Avg. Disk sec/Write" -comp $targetserver).countersamples[0].cookedvalue
$disksecperread = (get-counter -counter "PhysicalDisk(_Total)\Avg. Disk sec/Read" -comp $targetserver).countersamples[0].cookedvalue
$diskreadpersec = (get-counter -counter "PhysicalDisk(_Total)\Disk Reads/sec" -comp $targetserver).countersamples[0].cookedvalue
$diskwritepersec = (get-counter -counter "PhysicalDisk(_Total)\Disk Writes/sec" -comp $targetserver).countersamples[0].cookedvalue

$bchr = (get-counter -counter "SQLServer:Buffer Manager\Buffer cache hit ratio" -comp $targetserver).countersamples[0].cookedvalue
$pagelife = (get-counter -counter "SQLServer:Buffer Manager\Page Life Expectancy" -comp $targetserver).countersamples[0].cookedvalue
$memgrants = (get-counter -counter "SQLServer:Memory Manager\Memory Grants Pending" -comp $targetserver).countersamples[0].cookedvalue
$batchreqst = (get-counter -counter "SQLServer:SQL Statistics\Batch Requests/sec" -comp $targetserver).countersamples[0].cookedvalue
$compilats = (get-counter -counter "SQLServer:SQL Statistics\SQL Compilations/sec" -comp $targetserver).countersamples[0].cookedvalue
$recompilats = (get-counter -counter "SQLServer:SQL Statistics\SQL Re-compilations/sec" -comp $targetserver).countersamples[0].cookedvalue
$procQ = (get-counter -counter "System\Processor Queue Length" -comp $targetserver).countersamples[0].cookedvalue
if ($processor_count -gt 0){
$processor_instance=(0..($processor_count - 1)) 
foreach ( $proc_increments in $processor_instance)
$text_name = "Processor("+$proc_increments+")\% Processor Time"
$processor_utilization= [System.Math]::Round((get-counter -counter $text_name -comp $targetserver).countersamples[0].cookedvalue , 2)
#"Processor Time ("+$proc_increments+"): "+$processor_utilization[$proc_increments]
$processorUtilization_text = "% Utilization - Processor ("+$proc_increments+"): "
else {
$text_name = "Processor(_Total)\% Processor Time"
$processor_utilization= [System.Math]::Round((get-counter -counter $text_name -comp $targetserver).countersamples[0].cookedvalue , 2)
$processorUtilization_text = "% Utilization - Processor (_Total): "

$Counters.Add("Memory - Available MB: ",$availablebytes)
$Counters.Add("Page File - % Usage: ",$pagefileusage)
$Counters.Add("Physical Disk - Seconds/Write: ",$Disksecperwrite)
$Counters.Add("Physical Disk - Seconds/Read: ",$disksecperread)
$Counters.Add("Physical Disk - Reads/Second: ",$diskreadpersec)
$Counters.Add("Physical Disk - Writes/Second: ",$diskwritepersec)
$Counters.Add("SQLServer Buffer Mgr - Buffer Cache Hit Ratio: ",$bchr)
$Counters.Add("SQLServer Buffer Mgr - Page Life Expectancy: ",$pagelife )
$Counters.Add("SQLServer Memory Mgr - Memory Grants Pending: ",$memgrants)

$Counters.Add("SQLServer Statistics - Batch Requests/Second: ",$batchreqst)
$Counters.Add("SQLServer Statistics - Compilations/Second: ",$compilats)
$Counters.Add("SQLServer Statistics - Re-Compilations/Second: ",$recompilats)
$Counters.Add("System - Processor Queue Length: ",$procQ)