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.

001	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.

001	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.

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

From here it is as easy as:

001	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.

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