By: Ben Snaidero
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 is it 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
