Introduction to Windows Performance Monitor for SQL Server


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


Working Around the Workload

Free MSSQLTips Webinar: Working Around the Workload

How can the production database support everything the business wants to throw at it, and be continuously available, and perform well? Tune in as we discuss how (and why) businesses keep adding on to their existing environments, and how you, as the keeper of the database, can keep it all in check.


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.

Performance Monitor

Adding Perfmon Counters

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

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

Perfmon Objects
  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
  4. Add
  5. OK
Perfmon Counters

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

Added Counter

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. New
  3. Data Collector Set
New Data Collector Set
  1. Name Collector Set
  2. Next
Create New Collector Set
  1. Keep the default root directory or Browse… to another location
  2. Next
Collector Set Root Directory
  1. Choose different account or leave as default
  2. Finish
Finish

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

Newly Created Collector Set

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…
Save Collector Set Template
  1. Choose a location
  2. Give it a name
  3. Save
Template File Location

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. New
  3. Data Collector Set
New Collector Set
  1. Name it
  2. Next
Name New Collector Set
  1. Browse
  1. Browse to saved template and select
  2. Open
  1. Next
  1. Change data file location or leave at default
  2. Next
Collector Set Directory
  1. Change 'Run as' (optional)
  2. Select 'Open properties for this data collector set'
  3. Finish
Open Properties
  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
Create Report Subdirectory

We can add a schedule to run the Collector Set.

  1. Click Schedule tab
  2. Add
Add Schedule

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
  4. OK
Set Start Date and Time
  1. Stop Condition tab
  2. 'Check 'Overall duration' box
  3. Enter duration
  4. Choose Unit of time
  5. OK
Set Stop Condition

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
Manually Start Collector Set

Stop Collector Set when you're ready to view data

  1. Right click on Collector Set
  2. Stop
Stop Collector Set

Display Counter data.

  1. Right click on Collector Set
  2. Latest Report
Display Report

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

Report
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:






get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips


Article Last Updated: 2021-07-30

Comments For This Article





download














get free sql tips
agree to terms