Session 7: Identifying and Getting Help With Performance Problems Using Performance Analyst for Microsoft

Go Up to Performance Analyst for Microsoft SQL Server Tutorial

You do not have to be an advanced expert in performance tuning to spot trouble in a database if you use Performance Analyst. There are a number of mechanisms in the tool that help you quickly locate areas of concern within a database.

Using the Alarm Log in Performance Analyst for Microsoft

Performance Analyst has a central and flexible alarm log that lists every performance violation found in all monitored databases. Unlike some products that only show you alarms for a particular database, Performance Analyst lists all alarms for each database you are monitoring in one central log so you can see every problem in one place. To view the Performance Analyst Alarm log, do the following:

1. On the toolbar, click the Alarms button.

Once in the Alarm log, you can perform a number of actions including:

• Selecting an alarm and viewing the application page that shows details regarding the metric in violation.
• Filtering the types of alarms shown through the Options dialog.
• Clearing all current alarms.
• Copying or saving the Alarm log into a different format.

To select any of these options, do the following:

1. Position the pointer over any area of the Alarm Log.
2. Right-click and In the shortcut menu, select an option.

Recognizing Performance Violations Using Performance Analyst for Microsoft

Performance Analyst makes it easy to visually pinpoint performance violations in a database. Any statistic that does not conform to your predefined performance standards is colorized in any view that it appears so you can easily see potential troublespots.

The colors used to highlight a particular performance violation depend on the severity of the problem. Performance Analyst uses several layers of performance standards/threshold violations with each layer being represented by a different color. You can control everything regarding threshold violations, including how thresholds are determined, their color representation in Performance Analyst’s display, and more by using the Threshold Template feature of Performance Analyst.

How can Performance Analyst inform you of performance violations in your database if you minimize the product to your desktop and are working on other things? Even if Performance Analyst is not right in front of you, it can still let you know that a problem has been found in your database through the systray alarm icon. When Performance Analyst detects a problem in one of your databases, one of the things the product does is enable an alarm icon in your workstation’s systray area that communicates the presence of alarms in your database. So even if the Performance Analyst interface is not in sight, the product can let you know that something in your database requires attention.

Creating Threshold Templates for Performance Analyst for Microsoft

How does Performance Analyst know what to signal as a problem in a database? It uses predefined performance standards that you define and enforce. All of this is accomplished by using Performance Analyst’s Threshold templates. A template is a set of performance metrics and their thresholds that can be reused to enforce performance standards across any number of databases. IDERA provides a default performance template that contains nearly all of the most-watched performance statistics so you can begin monitoring with confidence right out of the box. However, you can create your own Threshold templates so you only are alerted to performance violations that are important to you and your particular environment.

To view or create Threshold templates, do the following:

1. On the toolbar, click the Thresholds button.

   Performance Analyst opens the Threshold Template Properties dialog.

The Threshold Template Properties dialog lets you select the options to add or edit templates, as well as other maintenance options. For example, you can copy the original IDERA Threshold template and modify it to fit your own needs. You can control what statistics are sampled for performance violations as well as the levels of severity you would like reported. Once you are satisfied with your template, you can save it and then apply it to one or more datasources in one action to immediately begin enforcing your performance standards.

Getting Help With Performance Problems Using Performance Analyst for Microsoft

As databases become more complex, so do the problems that plague them. Knowing what to do for a particular performance problem can be hard in some cases, but Performance Analyst helps by providing a complete set of expert help that you can use as a guide when performance problems occur. To obtain expert help for any statistic shown in Performance Analyst, do the following:

1. Position the pointer over a statistic that you need help.
2. Right-click and select Help.

The Expert Help gives you a general description of the statistic, the range of values that are considered optimal, and some suggestions on how to fix a problem situation. Expert help can also be invoked for any of the Ratio, Bottleneck, SQL, or Workload Analysis sections shown on every home page by clicking on the help icon for that section, which is located at the top right corner of the section.

Proceed to Session 8: Fixing Identified Problems Using Performance Analyst for Microsoft