SQL Counters with PowerShell

Many times a DBA will want to check specific SQL counters. In this article we will show you exactly how you can easy do that in PowerShell.

#Get a list of all the counters
Get-Counter -ComputerName $env:COMPUTERNAME -ListSet 'SQLServer*' |
ForEach-Object {$_.CounterSetName, $_.Paths} |
Format-Table -Autosize

In the example above we used $env:COMPUTERNAME to specify the local computer where we are running the commands, you could also simply leave off the -ComputerName switch and it would assume local computer by default. You can provide a remote computer name instead like ‘SQLSERVER01’ if you want, or an array of names.

#Counters in the buffer manager
Get-Counter -ComputerName $env:COMPUTERNAME -ListSet 'SQLServer:Buffer Manager' |
ForEach-Object {$_.CounterSetName, $_Paths} |
Format-Table -AutoSize

Now we will actually get the counters in Buffer Manager. This can be your own customized list of all the counters that you want to track, most DBAs have ones that they like to track.

$counterlist = @(
'\SQLServer:Buffer Manager\Buffer cache hit ratio',
'\SQLServer:Buffer Manager\Page reads/sec',
'\SQLServer:Buffer Manager\Page writes/sec',
)

Now lets do something with that performance data

# Here we are going to get 3 samples that are 5 seconds apart
$counterResult = Get-Counter -SampleInterval 5 -MaxSamples 3 -Counter $counterlist
foreach($counter in $counterResult)
{ $counterDataTable += $counter.CounterSamples }
$counterDataTable | Format-Table -AutoSize -Wrap

The sky is the limit when it comes to the counters that you can monitor and what you do with that monitored data, you could send yourself out an email if a threshold limit that you set is reached, or compare data from one day to the next, whatever your need you can do it with Powershell and Counters.