Session 2: Using Ratio-based Performance Analysis in Performance Analyst for Microsoft

From DBArtisan
Jump to: navigation, search

Go Up to Performance Analyst for Microsoft SQL Server Tutorial

Ratio-based analysis has been around for many years, and used to be the only technique database administrators used when they were called on to diagnose the cause of a database slowdown. Performance ratios are very good roll-up mechanisms for busy DBAs, making possible the analysis-at-a-glance approach, however ratios must be computed correctly for them to be meaningful. Key ratios in Performance Analyst are calculated using delta statistics, which is superior to computing ratios with cumulative values. Delta statistics, which are values derived by using the before and after measures in each statistical refresh done in Performance Analyst, lets you see changes in performance metrics that would otherwise be invisible if cumulative statistics were used. Performance Analyst makes it easy to see the ratios that are computed with delta statistics vs. cumulative. A triangle symbol denotes a delta-based statistic, while square symbols represent a cumulative-based measure.

Ratio-based analysis is available for all performance categories (memory, etc.) where ratios can assist in determining the performance level of a database. For example, if you would like to see memory-specific ratios, on the Performance Analyst toolbar click Memory. Performance Analyst displays the Memory home page that showcases all important memory-related ratios.

Obtaining Detail Information for a Ratio using Performance Analyst for Microsoft

A ratio gives you a global picture for a particular performance measure. However, you often need more information regarding a statistical measure so you can make an informed decision. Performance Analyst has easy-to-use drill down features that make obtaining detailed information for various ratios a breeze.

For example, to view more information for the Buffer Cache Hit Ratio statistic, do the following:

  1. On either the Main Home page or the Memory Home page, locate the Buffer Cache Hit Ratio statistic.
  2. Position the pointer over the statistic.
    The pointer changes to a hand symbol.
  3. Double-click the left mouse button.
Performance Analyst opens the Buffer Cache tab of the memory drill down view.
Right-click the statistic, select Details.
Select Navigate to, Memory, Buffer Cache.
Performance Analyst opens the Buffer Cache tab of the memory drill down view.

Proceed to Session 3: Using Bottleneck/wait-based Performance Analysis in Performance Analyst for Microsoft