Introduction to Windows Performance Monitor for SQL Server

By: Joe Gavin   |   Updated: 2021-07-30   |   Comments   |   Related: More > Monitoring

Problem

How can I use Windows Performance Monitor for troubleshooting Microsoft SQL Server performance issues?

Solution

We'll look at what Performance Monitor is and some examples of adding counters, saving reusable templates, and reporting on performance data.

What is Windows Performance Monitor?

Performance Monitor, commonly referred to as simply Perfmon, is a Windows program used by System Administrators to monitor a number of system areas and is a very useful tool for Database Administrators as well. It's been around since Microsoft Windows 3.51. Perfmon can show you information in real time or from log files for later viewing.

How can Performance Monitor Help Me?

Perfmon is able to monitor the standard performance Counters (CPU, processor time, memory, network, physical disk, disk read, etc.) that are installed with Windows operating system as well as specific counters installed with SQL Server.

Start Performance Monitor

Note: All examples were run on Windows Server 2019 Standard with SQL Server 2019 Standard.

There are a few ways to start Performance Monitor including:

• Start > Search enter Performance Monitor in the search box
• Start > Windows Administrative Tools > Performance Monitor
• Right click Start > Run > type perfmon in run box
• Windows key R > type Perfmon in run box (my favorite)

Whichever way you chose you'll be presented with Performance Monitor. Click Performance Monitor on the left side.

Adding Perfmon Counters

The % Processor Time Counter is running by default. Click on the green plus sign to start adding Counters.

You'll see a large number of Perfmon Objects, or categories, in the list. And under each Object there is an almost overwhelming number of Counters. That's OK, we're just going to focus on a few of them.

1. Scroll up to the click the dropdown of the Object you want
2. Select Counter
3. Optionally select the 'Show description' box to get a description of the Counter

Clicking on the Counter will show us a graph of the added Counter and its metrics.

I've gone back and added some standard SQL Server monitoring counters the same way we added Memory \ AvailableMBytes and we now have the following Objects \ Counters running:

Perfmon Object Perfmon Counter Memory Available MBytes Processor % Processor Time SQLServer:Access Methods Forwarded Records/sec SQLServer:Access Methods Full scans/sec SQLServer:Access Methods Page Splits / Sec SQLServer:Buffer Manager Buffer Cache hit ratio SQLServer:Buffer Manager Checkpoint Pages / Sec SQLServer:Buffer Manager Page life expectancy SQLServer:General Statistics User Connections SQLServer:Locks Average Wait Time (ms) SQLServer:Locks Lock Waits / Sec SQLServer:Memory Manager Memory Grants Pending SQLServer:Memory Manager Target Server Memory (KB) SQLServer:Memory Manager Total Server Memory (KB) SQLServer:SQL Statistics Batch Requests/Sec SQLServer:SQL Statistics SQL Compilations/Sec SQLServer:SQL Statistics SQL Re-Compilations/Sec

Create a Collector Set

Adding Counters is a bit tedious and it's very easy to accidentally close Perfmon and lose what you've added. So, it's a good idea at this point to save the Counters you're running to a Collector Set. This will let you pull them back up again easily after you're closed Perfmon.

1. Right click Performance Monitor
2. Data Collector Set

1. Name Collector Set

1. Keep the default root directory or Browse… to another location

1. Choose different account or leave as default
2. Finish

And here it is under Data Collector Sets > User Defined.

Saving Collector Set to a Template

Now that we have the Collector Set saved, let's export it to a template. The template is an editable .xml file that can be used on another Windows machine and is handy to have in your toolbox.

1. Right click on the Collector Set
2. Save Template…

1. Choose a location
2. Give it a name

Opening Saved Template

To demonstrate opening a saved template to a Collector Set on another machine I've deleted the existing Collector Set. Now we'll import the Template.

1. Right click User Defined
2. Data Collector Set

1. Name it

1. Browse

1. Browse to saved template and select

1. Change data file location or leave at default

1. Change 'Run as' (optional)
2. Select 'Open properties for this data collector set'
3. Finish

1. Directory tab
2. Change log directory (optional)
3. Enter subdirectory name
4. Enter date and timestamp format in 'subdirectory name format' box (I chose MMddyyHHmmss so I could start and stop it at will to be sure of a new report file name every time it ran. Otherwise, the Collector would fail to start with a 'When attempting to Start the Data Collector Set the following system error occurred: Cannot create a file when that file already existed.'. (Click right arrow for all supported formats.)
5. Apply

We can add a schedule to run the Collector Set.

1. Click Schedule tab

This schedule will run every day from 2:00PM to 3:00PM.

1. Change ' Beginning date' (optional)
2. Start time
3. Select days of week to run

1. Stop Condition tab
2. 'Check 'Overall duration' box
3. Enter duration
4. Choose Unit of time

There is a known issue with Windows Server 2019 and some versions of Windows 10 where the Windows Task Scheduler Task created when you schedule a Collector Set does not run. Here is the workaround for that: User-defined data collector set doesn't run as scheduled.

Running Collector Set

Let's start gathering performance data.

1. Right click on Collector Set
2. Start

Stop Collector Set when you're ready to view data

1. Right click on Collector Set

Display Counter data.

1. Right click on Collector Set
2. Latest Report

Here we can see our Counters on the screen and select individual Counters for more detail.

Next Steps

So far, we've seen how to:

• Open Perfmon
• Add Counters
• Save Counters to a Collector Set
• Export Collector Set to a template file
• Schedule a Collector Set
• Report on the Perfmon data collected

Stay tuned for a tip on what to look for in the performance monitoring report.

Here are some links with further information about monitoring tools:

Last Updated: 2021-07-30

About the author