Performance Monitor

Performance Monitor is a graphical tool supplied as part of the installation of any NT/2000 Server or Workstation that lets you monitor various performance indicators. Hundreds of counters are organized within performance objects. These counters can be monitored on the local machine or over the network and can be set up to monitor any object and counter on multiple systems at once from one session. A small subset of performance information is also available via the Windows Task Manager Performance tab. However, all of this information and more is available using the Performance Monitor facility.

Performance Monitor can be launched from many different points. Figure 37.2 shows how to launch Performance Monitor from SQL Profiler. You can also launch it from the Administrative Tools window in both Windows NT and Windows 2000. In Windows 2000, Performance Monitor has been renamed as System Monitor. However, it looks and feels just like Windows NT Performance Monitor.

Figure 37.2. Launching Performance Monitor from SQL Profiler.

Performance Monitor Views

You can switch the Performance Monitor between any of four views on Windows NT or three views on Windows 2000:

• Chart ”(Windows NT and Windows 2000) This is the default view that shows the selected counters as colored lines with the y-axis representing the value and the x-axis representing time.

• Alert ” (Windows NT only) In this view, you can set thresholds for counters, and the Performance Monitor will maintain a visual log of when they are reached.

• Log ” (Windows NT and Windows 2000) This option allows you to capture counter values with times to a file. This gives you a set of values that you can load back into the Performance Monitor facility at a later time. That way, you can monitor the system after hours and capture statistical data.

• Report ” (Windows NT and Windows 2000) In this mode, you see the current values for counters collected under their parent object.

You need to be aware of what counters you will examine, whether you should monitor from another machine, and how the counter values are to be tracked. Figure 37.3 shows the basic graphical user interface for Performance Monitor on Windows 2000 and the Chart view of one of the system counters.

Figure 37.3. Performance Monitor Chart view.

Monitoring Values

When you open the Performance Monitor/System Monitor from the Administrative Tools group , you will have an empty workspace that defaults to the Chart view. You will want to add all counters that you want to appear in the chart (or other views) to reflect what you want to measure. This chapter will explain the right ones for SQL Server in the "SQL Server Performance Counters" section later in this chapter.

You add a counter by clicking the large plus sign toolbar button. The Add Counters dialog box (see Figure 37.4) allows you to select a computer (for monitoring remotely), a performance object, any or selected counters, and an instance of the counter if applicable .

Figure 37.4. Adding a counter in the Performance Monitor/System Monitor.

You can customize the look of the line in the chart view by specifying color , width, and style from the System Monitor Properties, Data tab. The default counter that you will be presented with is from the %Processor Time counter from the Processor performance object. This counter indicates the percentage of time that the processor (CPU instance 0) is executing a nonidle thread and is the primary indicator of processor activity.

By clicking the Explain button, you can get a simple explanation of the counter. You can change the scale of a counter's value as well. In this case, you don't need to change the scale because it is a percentage, and the chart y-axis is numbered to 100.

To remove a counter, simply highlight the line in the bottom area of the Chart View window and press the Delete key or click on the X button in the toolbar.

You follow the same process whether you're adding the counter to Chart view, Histogram, or Report view. The next section will discuss some of the counters and when you want to use them.