---
title: "Monitor SQL Server Performance with PowerShell"
description: "Set up a SQL Server performance monitor using PowerShell and learn how to query SQL Server performance counters."
canonical: "https://adamtheautomator.com/sql-server-performance-monitor/"
---

# Monitor SQL Server Performance with PowerShell

> Set up a SQL Server performance monitor using PowerShell and learn how to query SQL Server performance counters.

Source: https://adamtheautomator.com/sql-server-performance-monitor/

---

ATA Learning

Tap to hide

[

ATA Learning

](/)

*   [Home](/)
*   [Tutorials](/tutorials/)
*   [Instructors](/author/)
*   [Advertising](/advertising/)
*   [Recommended Resources](/resources/)
*   [About Adam](/about-adam/)

Search for:  

*   [](https://twitter.com/adbertram)
*   [](https://github.com/Adam-the-Automator)
*   [](https://www.linkedin.com/company/adam-the-automator-llc)
*   [](/feed/)

![Monitor SQL Server Performance with PowerShell](https://adamtheautomator.com/wp-content/uploads/2019/07/photo-1504355080015-bba52674577b.jpg)

# Monitor SQL Server Performance with PowerShell

[![](https://secure.gravatar.com/avatar/d0b9d42e21e5622713f8b693aa5c0f9244d5f7dd200ed29b8398f52dee5de337?s=192&d=mm&r=g)Adam Bertram](https://adamtheautomator.com/author/adam-bertram/)22 July 20194 min. read

Categories: [IT Ops](/category/it-ops/)

Tags:[Microsoft SQL Server](/tag/microsoft-sql-server/)[PowerShell](/tag/powershell/)

Table of Contents

*   [Using Get-Counter](#using-get-counter)
*   [Finding SQL Server Performance Counters](#finding-important-performance-counters)
*   [Querying the SQL Performance Counters](#querying-the-sql-performance-counters)
*   [Summary](#summary)

Microsoft SQL Server can be one of the most resource-intensive server applications out there. You should build a SQL Server performance monitor script with PowerShell to help keep tabs on it!

> _SQL Server can also [run on Linux](https://adamtheautomator.com/connect-to-sql-server-from-linux/) but, unfortunately, you don’t have the performance counter system that you do on Windows._

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`](https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.diagnostics/get-counter?view=powershell-7.2). `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](/wp-content/uploads/2019/07/image-49.png)

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.

```powershell
PS> Get-Counter -ComputerName SQLSRV
```

When you run `Get-Counter` by just using the `ComputerName` parameter, the cmdlet returns a default set of counters which is probably not what you want to see.

## Finding SQL Server 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 put together some PowerShell code to create a SQL Server performance monitor.

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

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](https://adamtheautomator.com/powershell-foreach/ "foreach")_ loop.

```powershell
$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.

```powershell
$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
}
```

When you run the above code snippet, you’ll see that `Get-Counter` will query each of the counters defined in the `$counters` array and return the values. Since each iterator (`$counter`) is a hashtable, the script is then adding a property called `Value` and averaging out the values.

What you end up with with a single pscustomobject object with a single property `Value` representing an average of each counter value.

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! Now take that the start of the SQL Server performance monitor script shown here and extend it!

Share this article

[Share on X](https://twitter.com/intent/tweet?url=https%3A%2F%2Fadamtheautomator.com%2Fsql-server-performance-monitor%2F&text=Monitor%20SQL%20Server%20Performance%20with%20PowerShell)[Share on Facebook](https://www.facebook.com/sharer/sharer.php?u=https%3A%2F%2Fadamtheautomator.com%2Fsql-server-performance-monitor%2F)[Share on LinkedIn](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fadamtheautomator.com%2Fsql-server-performance-monitor%2F)

## Related Posts

![](https://adamtheautomator.com/wp-content/uploads/2026/06/26995-troubleshoot-dns-issues-powershell-codex.webp)

### [Troubleshoot DNS Issues with PowerShell](/troubleshoot-dns-issues-powershell/)

Troubleshoot DNS issues with PowerShell by testing name resolution, DNS client settings, cache entries, and network connectivity in a repeatable workflow.

![](https://adamtheautomator.com/wp-content/uploads/2025/10/image_2025-10-24_095322075.png)

### [Migrating from PowerShell 6 to 7.5: Breaking Changes/New Features](/migrating-powershell-6-to-7-5/)

Migrate from PowerShell Core 6 to 7.5: breaking changes, features, and testing tips.

![](https://adamtheautomator.com/wp-content/uploads/2025/06/featured-image-6.png)

### [How to Add Timeouts to Pester Tests with PowerShell Runspaces](/pester-test-timeout-runspaces/)

Prevent Pester tests from hanging indefinitely using PowerShell runspaces. Learn to handle variable scoping, module loading, TestDrive access, and stream capture challenges with timeout protection.

## Categories

*   [IT Ops](/category/it-ops/)
*   [Cloud](/category/cloud/)
*   [DevOps](/category/devops/)
*   [Home Ops](/category/home-ops/)
*   [Information Security](/category/infosec/)
*   [Software Development](/category/software-development/)

## Site

*   [Home](/)
*   [Tutorials](/tutorials/)
*   [Instructors](/author/)
*   [Advertising](/advertising/)
*   [Recommended Resources](/resources/)
*   [About Adam](/about-adam/)

Copyright 2026© ATA Learning | [Privacy Policy](/privacy/)