Opening Performance Monitor In Sql Server 2008: A Guide

Database developers and administrators can use the Activity Monitor in SQL Server 2008 to get a quick overview of system performance. The Activity Monitor tool in the previous version of SQL Server used to display information related to Processes, Lock by Objects and Locks by Process. There are several enhancements in Activity Monitor in SQL Server 2008, such as a graphical display of Processor Time, Waiting Tasks, Database I/O's, Batch Requests, and Processes. To open the Activity Monitor in SQL Server 2008, right-click the SQL Server 2008 Instance in Object Explorer and select Activity Monitor from the drop-down list. Alternatively, click Activity Monitor in the SQL Server 2008 Management Studio toolbar.

Launching Performance Monitor

Performance Monitor is a tool that allows you to capture and graph many aspects of a Windows server. It can be used to monitor the performance of SQL Server and Windows operating system simultaneously to determine any correlation between the two.

There are a few different ways to launch Performance Monitor:

• Start > Control Panel > Administrative Tools > Performance Monitor or you can launch PerfMon.exe.
• Open Start, Run (Windows + R for Windows 8), type perfmon, and press Enter.
• Open Start (Windows + C for Windows 8), Search, 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.

Once Performance Monitor is open, you will see an interface with one counter, "% Processor Time", being tracked. You can view the last value, average, minimum, and maximum values for this counter, as well as the duration of the collection time period.

In addition to "% Processor Time", there are many other counters available in Performance Monitor, such as Memory, Physical Disk, and SQL Server-specific counters. You can add additional counters by right-clicking on the big green plus button above the graph and selecting the counter you want to add.

Performance Monitor also allows you to change the duration and frequency of data collection. By default, it is set to sample the data every 1 second for a duration of 100 seconds. You can change these settings by right-clicking on the graph and selecting Properties.

Adding Counters

To add counters, right-click on the graph pane and select "Add Counters" from the context menu, or click the plus icon in the menu. In the "Add Counters" dialog box, select the computer you wish to monitor. The default selection is the local machine. To add a remote machine, click "Browse".

You will need network access to add a remote machine, and to ensure Performance Monitor is added to the firewall exception list.

The list of available counters is divided into groups, and you can choose to add all the counters in a group or select specific ones. To add a counter to the monitoring process, select the counter group from the list of available counter groups, then select the counter. For example, to monitor "Page faults/sec", select the "Memory" group, then select "Page faults/sec" and click "Add".

Repeat this step to add more counters. In the example provided, the author has also added:

• Processor – %Processor Time total for each processor
• Physical Disk – Current Disk Queue Length
• Network Interface – Packets/sec (for all and for a specific network adapter)

All selected counters will be shown on a single graph, with data displayed for two minutes. The graph will show lines written left to right along the X-axis, overwriting the oldest values.

Below the graph, all counters will be listed with their basic information and the graph line colour. When a counter is selected, its current, average, minimum, and maximum values will be shown below the graph.

You can choose to hide a counter from the graph display by unchecking the box at the beginning of each row. This will not stop the counter from being monitored, but simply hide the line from the graph.

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

You can also highlight specific counters to make the line bolded, and change the type of graph from the default line to histogram bars or a numeric report.

Changing Settings

Once you have opened the Performance Monitor, you can start to add metrics to monitor in real time. To do this, right-click on the graph pane and select "Add Counters" from the context menu, or simply click the "Add" (plus) icon in the menu.

In the "Add Counters" dialogue box, select the computer you want to monitor. The local machine is selected by default, but you can add a remote machine by clicking "Browse". To monitor a remote machine, make sure that Performance Monitor is added to the firewall exception list and that you have access to the network where the remote machine is located.

The list of available counter groups will appear below the computer selection drop-down list. You can add all the counters in a group or select specific ones to collect. To add a counter to the monitoring process, select the counter group from the list of available counter groups, then choose the counter you want to add. For example, to monitor "Page faults/sec", select the "Memory" group, then select "Page faults/sec" and click "Add".

Repeat this process to add more counters. For example, you could add Processor – %Processor Time total for each processor, Physical Disk – Current Disk Queue Length, and Network Interface – Packets/sec (for all or for a specific network adapter).

All selected counters will be displayed on a single graph, which shows two minutes of data by default. The graph can be modified to show different time periods and scales, and you can also change the type of graph from the default line to histogram bars or a numeric report.

To remove a counter from the monitoring process, select it from the list and click the red "X" (Delete) button in the menu.

You can also export data from charts, logs, alert logs, and reports to spreadsheet or database applications for further analysis, manipulation, or printing.

Benefits of Performance Monitor

The Performance Monitor in SQL Server 2008 is a tool that provides a quick overview of the system's performance. It offers several benefits for database developers and administrators:

• Graphical Display of System Performance Metrics: The Performance Monitor provides a graphical display of various system performance metrics, including Processor Time, Waiting Tasks, Database I/O, Batch Requests, and Resource Waits. This allows users to easily visualize and interpret the system's performance data.
• Identification of Potential Bottlenecks: The Resource Waits section provides valuable information about threads waiting for key resources such as memory, CPU, or network. This helps administrators identify potential bottlenecks related to memory, CPU, network I/O, and other resources, enabling them to optimize system performance.
• Detection of Poorly Performing Queries: The Recent Expensive Queries section allows administrators to quickly identify poorly performing queries in the SQL Server Instance. This enables them to take necessary actions, such as editing the query or reviewing its execution plan.
• User Activity Evaluation: The Performance Monitor displays information about processes running on the SQL Server, blocked processes, locks, and user activity. This helps evaluate user activity, ensuring security is adequately set up, and facilitating the testing of applications or development systems.
• Real-time Performance Monitoring: The Performance Monitor can be used to monitor system performance in real time. It collects and displays performance data in the form of counters, providing up-to-date information on resource utilization and SQL Server-specific resources like locks and transactions.
• Historical Data Analysis: In addition to real-time data, the Performance Monitor can also display performance history stored in log files. This allows for the long-term retention of historical data, enabling administrators to analyze past performance trends and make informed decisions.
• Remote Monitoring: The Performance Monitor can be used to monitor local and remote computers. This flexibility enables administrators to manage and optimize system performance across multiple machines.

Performance Monitor Tasks

Performance Monitor is a tool that allows you to capture and graph many aspects of a Windows server. It can be used to monitor the performance of SQL Server and Windows operating system simultaneously to determine any correlation between the two.

Launching Performance Monitor

To launch Performance Monitor, you can go through Start > Control Panel > Administrative Tools > Performance Monitor, or you can launch PerfMon.exe.

Adding Counters and Changing Settings

To add a counter, right-click on the big green plus button above the graph and select from the available options. You can also change the duration and frequency of data collection by right-clicking on the graph and selecting Properties.

Benefits of Performance Monitor

Performance Monitor allows you to obtain statistics on current SQL Server activity and performance. It enables you to view data from multiple computers simultaneously, export data to other applications, add system alerts, and create log files.

When using Performance Monitor to monitor SQL Server and the Windows operating system, focus on the following three areas:

• Processor utilization
• Disk counters to determine disk activity and I/O generated by SQL Server components
• Memory usage to ensure it is within typical ranges

Frequently asked questions