Thursday, December 4, 2014

PowerShell Counters

 Last night we received an alert for the following "SQLServer:Locks: Average Wait Time (ms) is above it's currently configured threshold"... What an obscure alert, that I see far too often. Here’s the PowerShell way to check if this has been resolved.

First we need to open a PowerShell console on the machine in question as an Administrator to get the correct SQL counter set name.

Get-Counter -ListSet * | select CounterSetName

Okay so that does give us all the information we are requesting but in order to get a nice output that only contains SQL counters we could filter out all counters that do NOT have *SQL* in the name.

Get-Counter -ListSet * | select CounterSetName | Where-Object {$_.CounterSetName -like "*SQL*"}

Now that we know the counter set name is "SQLServer:Locks" we can run the following command to see a list of the counters within SQLServer:Locks.

Get-Counter -ListSet SQLServer:Locks | Select-Object Counter -ExpandProperty Counter

From here it is as easy as:

Get-Counter -Counter "\SQLServer:Locks(*)\Lock Wait Time (ms)"

There are a few gotchas however, you will need quotes around the counter name because it contains spaces and this must be ran in an administrative shell.

As shown above the average lock wait time has been reduced to 0 which tells me this alert has now cleared out. Below I've added some more popular cmdlets to this command to get a little more data at an interval that you can specify.

Get-Counter -Counter "\SQLServer:Locks(*)\Lock Wait Time (ms)" -SampleInterval 10 -MaxSamples 3