Microsoft SQL Server can be one of the most resource-intensive server applications out there. Depending on the number of databases and the load on them, resource utilization could be intensive. As a result, if you don't routinely monitor important metrics around this utilization,this can lead to slow performance and service downtime. Here's a few pointers to create a PowerShell script to monitor SQL services.

Windows has had built into it a great resource for getting insights into various performance metrics for the operating system itself as well as many other applications known as performance counters.

The Windows Performance Monitor is a staple of many IT professionals out there. Although the Windows Performance Monitor is a great tool to visually see performance statistics, it's not necessarily great for automation. This is where PowerShell comes in.

By using PowerShell to query performance statistics you can use these numbers as triggers for other functions like making decisions to automatically move databases to faster storage, automatically add an additional CPU, memory, etc.

In this article, let's go over some of the most common performance metrics for SQL and how to query them with PowerShell.

Using Get-Counter

The PowerShell cmdlet you're going to get the most comfortable with is Get-Counter. Get-Counter is a cmdlet that allows you to query any number of performance metrics from Windows. Run it by itself and you'll get a list of various common performance counters and their current values.

Get-Counter output

Get-Counter, used without parameters queries the local computer. We're going to be querying a SQL server so use the ComputerName parameter to specify your remote SQL server.

PS> Get-Counter -ComputerName SQLSRV

Finding important performance counters

Get-Counter will return the same counter values. However, we're going to look for some counters that are important to SQL server. We'll be querying:

  • Avg. CPU Queue Length
  • Avg. Disk Queue Length
  • Memory Pages/Sec
  • Latch Wait Time
  • Buffer Page Life Expectancy
  • Average Lock Wait Time.

To get the current values for each of these counters, we'll first need to determine which set they are in and their proper name. In any metric where multiple instances can be used, I'm using an asterisk to get an average across all available instances or using _total to get a sum of all instances.

  • Avg. CPU Queue Length = \System\Processor Queue Length
  • Avg. Disk Queue Length = \PhysicalDisk()\Avg. Disk Queue Length
  • Memory Pages/Sec = \Memory\Pages/sec
  • Latch Wait Time = \SQLServer:Latches\Average Latch Wait Time (ms)
  • Buffer Page Life Expectancy = \SQLServer:Buffer Manager\Page lifeexpectancy
  • Average Lock Wait Time = \SQLServer:Locks(_total)\Average WaitTime (ms)

Now that we have the correct counter names defined and the counter sets the counters are a part of, we can now begin to build some PowerShell code to query them.

Querying the SQL performance counters

To get an overall picture of performance and remove the possibility of a one-time spike in any of the counters, I'm going to query each of the counters ten times. I'll use the default sample interval of one second which means I'm going to query each counter ten times over ten seconds.This should give me a more realistic number than simply querying eachcounter a single time.

To do this, I'll use the MaxSamples parameter on Get-Counter. This will allow me to specify the maximum number of times each counter will be queried. I'll gather up all of these figures and then take an average of each when I'm finished. Also, because I just want the values and don't necessarily care about the formatting, I'll specify the CounterSamples property directly and the CookedValue property as part of that. This gives me only the actual integer values.

Because I'll be querying multiple performance counters, it's good practice to group them up into array. I've done so below and assigning it the variable $counters. �I've chosen to use hashtables with the"friendly" name of the counter and the actual counter name. I then have something I can read with a foreach loop.

$counters = @(
    @{
        'Name' = 'Avg. CPU Queue Length'
        'CounterName' ='\System\Processor Queue Length'
    }
    @{
        'Name' = 'Avg. Disk Queue Length'
        'CounterName' ='\PhysicalDisk()\Avg. Disk Queue Length' }
    @{
        'Name' = 'Memory Pages/Sec'
        'CounterName' = '\Memory\Pages/sec'
    }
    @{
        'Name' = 'Latch Wait Time'
        'CounterName' ='\SQLServer:Latches\Average Latch Wait Time (ms)'     }
    @{
        'Name' = 'Buffer Page Life Expectancy'
        'CounterName' ='\SQLServer:Buffer Manager\Page life expectancy'     }
    @{
        'Name' = 'Average Lock Wait Time'
        'CounterName' ='\SQLServer:Locks(_total)\Average Wait Time (ms)'
    }
)

Once I have the array of hashtables defined, I'll then begin to read each counter, gather up all ten samples and add the average value to the counter instance itself.

Finally, I'm creating a PowerShell custom object so the output can be easily used for other purposes, if necessary.

$sqlServerName = 'LABSQL'
foreach ($counter in $counters) {
    $values = (Get-Counter -ComputerName $sqlServerName -Counter $counter.CounterName -MaxSamples 10).CounterSamples.CookedValue
    $counter.Add('Value', ($values | Measure-Object -Average).Average)
    [pscustomobject]$counter
}

This ends up getting us a nice report showing performance counter statistics for our SQL server!

Summary

You can see that working with performance counters in PowerShell is pretty straightforward. The hardest part for me was just trying to find the counter names themselves!

Join the Jar Tippers on Patreon

It takes a lot of time to write detailed blog posts like this one. In a single-income family, this blog is one way I depend on to keep the lights on. I'd be eternally grateful if you could become a Patreon patron today!

Become a Patron!