Session 3: Using Bottleneck/wait-based Performance Analysis in Performance Analyst for Sybase ASE

Go Up to Performance Analyst for Sybase ASE Tutorial

When a Sybase ASE database is up and running, every connected process is either busy doing work or waiting to perform work. A process that is waiting may mean nothing in the overall scheme of things, or it can be an indicator that a database bottleneck exists. Bottleneck analysis is a valid method of measuring performance because it helps you track where a database and user sessions have been spending their time. If latch contention or heavy table-scan activity has been dragging a database’s performance down, you can use bottleneck analysis to confirm the actual root cause.

Performance Analyst uses three broad areas of metrics to form its bottleneck analysis. The first are contentions and conflicts as reported in Sybase's monitoring sp_sysmon procedure, as well as overall lock statistics. The second is the Sybase wait event interface (available only in Sybase 12.5.03 and higher), which displays where the database and user sessions have been bottlenecked. A third area of bottleneck information has to do with space related bottlenecks like databases and transaction logs that are running low on available free space.

Performance Analyst prominently displays bottleneck analysis on every performance home page. The Main home page, displays a summary of all key bottlenecks threatening the database. You can also see specific bottlenecks for memory, I/O, space, objects, and users by navigating to each home page and viewing the Bottleneck Analysis section for that home page. For example, to see bottlenecks that specifically relate to I/O, do the following

1. On the toolbar, click the I/O button.
2. Locate the Bottleneck Analysis section at the lower-left portion of the screen.

   The I/O bottleneck analysis section shows the I/O related headaches, along with the most active devices.

Obtaining Detail on System Bottlenecks Using Performance Analyst for Sybase ASE

You can easily obtain more information regarding global bottlenecks that are plaguing a database by drilling down into a particular bottleneck metric. For example, if you would like to see more data regarding the current top system bottlenecks, do the following:

1. Right-click anywhere in Performance Analyst, point to Users, and then click System Waits.

   Performance Analyst opens the System Waits view.

The System Waits view (available in Sybase ASE 12.5.03 and higher with the MDA monitoring views installed) displays critical details regarding all the wait events that Sybase has recorded. You can easily see what events that occurred the most often as well as the events that have accumulated the most wait time.

Tip: If you would like to see an expanded view of the System waits grid shown on the top of the System Waits view, click the Expand button located at the top right corner of the section. All section views in Performance Analyst can be maximized/minimized using this control.

Viewing Detail for Session Bottlenecks Using Performance Analyst for Sybase ASE

Not only can you obtain detail information for system bottlenecks, but you can also use Performance Analyst to get drill down information regarding session-level bottlenecks. There are several layers to session-level details you can view.

To see more information regarding session-level waits, do the following:

1. Right-click anywhere in Performance Analyst, point to Users, and then click Session Waits.

   Performance Analyst opens the Session Waits drill-down view.

When you are in the Session Waits drill down view, to drill further into a session’s details, do the following:

1. In the Historical Waits or Current Waits grid, select a session that appears to be experiencing critical waits.
2. Double-click the session or right-click, and then select Details.

   Performance Analyst opens a drill-down view that shows key statistics for the selected session, including its current work activities, wait events, and SQL statements.

Proceed to Session 4: Using Workload Performance Analysis in Performance Analyst for Sybase ASE