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

Go Up to Performance Analyst for Microsoft SQL Server Tutorial

When a Microsoft SQL Server 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 two broad areas of metrics to form its bottleneck analysis. The first is the Microsoft SQL Server wait event interface, which displays where the database and user sessions have been bottlenecked. But, when using bottleneck analysis you can not rely only on the information contained in the wait event views that Microsoft SQL Server provides. For example, an object may attempt to extend into another extent of space in a tablespace and yet, be denied if no such free space exists. Such a failure will not be reflected in any wait event, but still represents a very real bottleneck to the database. For this reason, Performance Analyst also shows non-wait event based bottlenecks that represent a very real threat to a database’s availability and performance.

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 displays the Top I/O Wait Events by wait count and wait time, along with the Hottest Database and Log Files.

Obtaining Detail on System Bottlenecks Using Performance Analyst for Microsoft

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. Open the Main home page.
2. Double-click the Top System Bottlenecks area.

   Performance Analyst opens the System Waits view.

3. Or 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 displays critical details regarding all the wait events that Microsoft SQL Server 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 Microsoft

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. Open the Main home page.
2. Double-click the Top Session Bottlenecks area.

   OR

3. 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 Microsoft (DBArtisan)