In the Windows Performance Monitor basics article, we described the most important Windows Performance Monitor features, gave general recommendations on how to use it for monitoring and troubleshooting SQL Server performance issues, and showed some of the counters it can monitor

In this article, we will give detailed steps for using Performance Monitor for SQL Server tuning

How to use Performance Monitor for SQL Server monitoring?

To open Windows Performance Monitor, use one of the following options:

• Open Start (Windows + C for Windows 8), Search, type perfmon, and press Enter
• Open Start, Run (Windows + R for Windows 8), type perfmon, and press Enter
• Open Control Panel, System and Security, Administrative Tools, and click Performance Monitor

In the left pane, select Monitoring Tools and then Performance Monitor

To add a metric to monitor in real time:

1. Right-click the graph pane and select Add Counters in the context menu, or click the Add (plus) icon in the menu
2. In the Add Counters dialog, select the computer you want to monitor. The local machine is selected by default. To add a remote machine, click Browse. To do this, you must have access to the network where the remote machine is. To monitor a remote machine, make sure that Performance Monitor is added to the firewall exception list
3. The list below the computer selection drop-down list shows available counters divided into groups. You can add all of the counters in a group or select just the ones you want to collect. To add a counter to the monitoring process, select the counter group in the list of available counter groups and then the counter. For example, to monitor Page faults/sec, select the Memory group. Then, select Page faults/sec and click Add
4. To add more counters, repeat step 3. In this example, we’ve added Processor – %Processor Time total for each processor, Physical Disk – Current Disk Queue Length, and Network Interface – Packets/sec (for all and for a specific network adapter)

All selected counters are shown in a single graph. The graph shows two minutes of data, and lines are written left to right along the X-axis, overwriting the oldest values

Below the graph, all counters are listed with their basic information and the graph line color

When a counter is selected, its current, average, minimum, and maximum values are shown below the graph

Tweaking the graph

The check box at the beginning of each row specifies whether the counter is presented in the graph or not. It doesn’t stop monitoring of the counter, it just hides its line in the graph

To remove the counter from monitoring, select it in the list and click red X (Delete) in the menu

To distinguish the specific counter among the lines shown, use the highlight option in the menu to make the line bolded (or Ctrl+H)

To change the type of graph from default line to histogram bars, or numeric report, click the Change graph type icon in the menu

In order to read the graph easier, you can also change the default line colors in the Properties dialog in the counter context menu

By default, the graph shows the values up to 100. To change the graph ratio:

1. Right click the graph
2. Select Properties
3. Select the Graph tab
4. Modify the Vertical scale Maximum and Minimal values

Another option that you can use to make the lines easier to read is to change the line ratio. A default line scale is set for each counter, but in case the values are very low or high, you see only a flat line close to the bottom or top of the graph. In that case, modifying a counter line scale is necessary

In the next example, reading the Page Faults/sec values when the default line scale is used is not possible, as its values are presented with a flat line at the top of the graph

The solution is to change the default 0.1 line scale for the counter to 0.01

It means that the value 1,600 will be presented as 1,600*0.01 = 16, which can be clearly read from the graph that shows values 0 to 100

To stop drawing the lines, click Pause (Freeze display) in the menu and Unfreeze to resume

Viewing log files

Besides showing the real time data, Performance Monitor can show the counter values stored in *.blg, *.csv, or *.tsv log files by the operating system utility, such as Data Collector Set, or a third party tool

When started, Performance Monitor shows the current activity by default. To open a log file and show its content in the graph:

1. Click View Log Data in the menu
2. Open the Source tab
3. Select Log files

4. Click Add and navigate to the log files you want to show

   

5. Move the Time Range bar left or right to narrow down the time period shown in the graph

Reports

The Reports option available in the Performance Monitor parent snap-in left pane contains a set of templates for presenting the data collected by Data Collector Sets. A report is automatically created for each Data Collector Set, and it can be shown as a text report, with information grouped by their origin into collapsible sections, or as a graph the same as the one for presenting current data or existing logs

Windows Performance Monitor adds less overhead than SQL Server Data Collector and monitors more SQL Server counters. Besides SQL Server, it monitors the system counters as well. While for Windows Performance Monitor you have to add the counters or counter groups one by one, SQL Server Data Collector has pre-defined collection sets that provide monitoring of the most important parameters, but monitoring additional metrics requires coding. Performance Monitor is available in all Windows Server 2003 and later editions; SQL Server Data Collector is available only in Microsoft SQL Server 2008 and higher, in Standard, Enterprise, Business Intelligence, and Web editions

When it comes to SQL Server Activity Monitor, it provides only a limited set of SQL Server performance metrics and no built-in archiving option for historical data, thus it is useful only for basic performance monitoring

Windows Performance Monitor is a native and easy to use performance monitoring utility. Besides showing the real time performance data, it can be also used to show the performance history stored in log files. Historical data can be saved for long periods of time, as the log files can be archived to avoid hard disk space problems. It monitors hundreds of performance counters on the local and remote computers with low overhead

When it comes to advanced analysis, Performance Monitor cannot provide all required information, nor show a trend line or a threshold in the graph