Overview
Performance Monitor (PerfMon) is a tool that comes built-in with Windows and allows you to look into the performance of your system and the applications that are running on it. It gives you a way to view data points that are associated with these applications and link them with the effect they have on your system. You can monitor data points both in real-time or collect them for analysis at a later time. Collection can also be scheduled for specific times of the day if there are only specific windows of time where you are experiencing an issue that you would like to monitor. Another important thing to note with the tool can also be used to monitor remote servers, not just the local server where the tool is running.
Explanation
From the system perspective, there are performance counters that can help you to identify a wide range of system bottlenecks. There are counters related to disk performance, both physical and logical, network throughput on both the adapter and all interfaces, CPU, memory, and a host of other subsystems that run on top of your operating system. If you are running Hyper-V, there are also counters specifically related to all of its resources.
Many applications also come with their own set of performance counters, and SQL Server is no different in this respect. When installing SQL Server, it also installs counters for both the database instance and its associated SQL Server Agent. Included with the database engine are counters to monitor backup devices, buffer manager, databases, latches/locks, memory, errors, wait statistics, just to name a few. With regards to the SQL Server Agent, there are counters to monitor jobs, job steps, and alerts.
Most importantly, having these system and application counters available in the same tool makes it much easier to correlate system and application performance.
In this tutorial, we will demonstrate how you can use the functionality that is available within Perfmon to monitor your system. Topics will include:
- Using Performance Monitor
- Access to Windows Performance Monitor features
- Launching the application
- Methods for collecting data
- Real-time data collection
- Creating a new collector set
- Data Collection
- On demand
- Scheduled
- Display/Report collected data
- Using Perfmon
- Using a spreadsheet
- Collecting system level statistics
- Monitoring server health
- Collecting database specific statistics
- Monitoring database health
Additional Information
- Overview of Performance Monitor
- Windows Reliability and Performance Monitor to troubleshoot SQL Server
Ben Snaidero has been a Database Administrator for just over 10 years. Starting out working mainly with Oracle he got into SQL Server in 2005 and has worked primarily with SQL Server for the last 3 years. His main focus with both Oracle and SQL Server is in the area of performance tuning.
- MSSQLTips Awards: Achiever (75+ tips) – 2018 | Author of the Year Contender – 2016-2017
