Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II - Page 2

Examine Examples of the Underlying Drillthrough Reports

As we have noted, we access each of the underlying reports as a drillthrough, by clicking an associated navigation point on the Performance Dashboard (or, in some cases, from points in other reports within the set). We will consider each in turn, discussing the point of access from the Dashboard, as well as the purpose and other selected details, within the respective subsections that follow.

Report: Blocking Report

File Name: wait_blocking.rdl

Performance Dashboard Access “Click” Point:

Purpose and Details:

The Blocking Report provides details about each distinct blocking chain occurring on the server.  The summary information shows which session_id is at the head of the blocking chain, applications running, transaction count, and the number of sessions and total time they have been blocked.  The blocking chain with highest cumulative wait time is shown at the top of the report, ordered by any additional blocking chains from highest to lowest duration. 

The head blocker is the first row in the table, appearing once we expand to see the blocking chain. Other sessions are indented based upon their positions within the blocking chain.  In the example depicted in Illustration 6 (from the Performance Dashboard Reports documentation), session 55 is blocked by session 53, while session 54 is blocked by session 55. 

The detail information portion of the report (lower section) presents the amount of time each request has been waiting, what the associated lock mode is, transaction details, and so forth.

Report: Buffer IO Waits Report

File Name: wait_buffer_io.rdl

Performance Dashboard Access “Click” Point:

Purpose and Details:

The Buffer IO Waits Report displays requests that are currently blocked pending the completion of a physical disk IO.  If this is the most common wait type, it may be an indication of a disk IO bottleneck that may be resolved by tuning the queries or adding additional memory.

The output is grouped by the Database Page number for each awaiting request. Expanding the grouping allows us to see each waiting session and the associated query.  The bottom portion of the report (which displays the top twenty sessions that have performed the most physical IO, and thus are most likely contributing to an IO bottleneck), can help us to identify which sessions are performing the most IO. This can lead us to a subsequent examination of the session, and perhaps a determination of more information about the user or application and why it is driving the indicated volume of physical IO.

An example of the Buffer IO Waits Report is shown in Illustration 7.

As is the case with many of the members of the Performance Dashboard Reports set, the events we are scrutinizing often have extremely short lives (in some cases milliseconds), so if we refresh the report we may not see certain attributes or activities (such as, in the present case, a session currently waiting for a buffer IO latch).

Report: Buffer Latch Waits Report

File Name: wait_buflatch.rdl

Performance Dashboard Access “Click” Point:

Purpose and Details:

The Buffer Latch Waits Report displays requests awaiting access to a buffer already resident in memory (versus a disk IO bottleneck). The Buffer Latch Waits Report groups information based upon the page upon which the requests are waiting.  (Possible diagnostic clues can be derived, from this perspective, by seeking the reasons behind why the indicated page is being frequently accessed.)

By clicking on the page, we can navigate to the Page Details Report (examined in its own section within this article) that shows information about the page itself (whether it is a data or index page, etc.).  Understanding the page type is key to resolving the contention. (Because a buffer latch is normally held for a very brief period, contention of this type generally occurs because of very frequent, concurrent access to a page by multiple sessions.)

Design enhancements in MSSQL Server 2005 have eliminated / reduced tempDB-related contention (suggestions for identifying and resolving these issues can be found in MSDN articles, where applicable). Contention issues involving non-tempDB pages can be best resolved by determining how the page is used and why it is being accessed so frequently.  Schema or query changes are commonly required to eliminate these bottlenecks. (More complicated scenarios may be resolved with help from Microsoft support.)

An example Buffer Latch Waits Report display appears is depicted in Illustration 8.

Report: Databases Overview Report

File Name: database_overview.rdl

Performance Dashboard Access “Click” Point:

Purpose and Details:

The Databases Overview Report displays basic status and configuration information for each database to which the information consumer has access. The information we can obtain from the Databases Overview Report may assist our diagnostic and maintenance efforts in several ways.

• The database compatibility level setting can affect the types of plans generated or chosen by the optimizer.  (Two otherwise identical databases with different compatibility levels could result in different query plans, even with all other factors, such as user options, etc., remaining the same.)
• The recovery model may affect performance of certain modification operations.  (For example, using bulk logged recovery can reduce the amount of logging for a bulk operation, or simple recovery can be used if log backups are not necessary, thus reducing the IO required for the backup operation itself.)
• The auto create statistics and auto update statistics database options allow MSSQL Server to create or update statistics needed during query optimization.  Databases for which this option is disabled are noted with a yellow cell background, similar to the circumstances for one of the databases in the report example shown in Illustration 9.

• The database parameterization level setting may either be SIMPLE or FORCED.  This setting can make significant impact upon query performance - its best configuration obviously depends upon the application involved, and should be determined after a thorough understanding of its operation (from the Books Online and other documentation) and careful consideration of all contributing factors.

The Databases Overview Report table also shows data and log file size as well as log space used, to support proactive monitoring for the availability of adequate data and log space.  (Even if the file is allowed to grow automatically, the time taken to extend and zero the file at high load times, etc., can be considerable – hence the best practice is to extend the database proactively, using autogrow only as a “safety net.”)

Report: The Expensive Queries Report

File Name: query_stats.rdl

Performance Dashboard Access “Click” Point:

Purpose and Details:

The Expensive Queries Report displays the top 20 most expensive queries. The report sorts by different attributes, such as the number of logical reads, physical reads, or CPU time consumed by the query.  The data source for the Expensive Queries Report is the sys.dm_exec_query_stats DMV, which collects aggregate performance information for the time that a query plan resides in cache.

It is important to keep in mind, when using this report, that MSSQL Server only caches query plans for the SELECT, INSERT, UPDATE and DELETE (DML) statements, and that many other statements that can consume significant system resources (for example, CREATE INDEX, BACKUP DATABASE, EXEC <CLR function>, etc.) will not be reflected in this view.  Moreover, there is no guarantee, even, that all DML statements will be cached, especially if the cost to compile the plan is extremely low.  Finally, the sys.dm_exec_query_stats DMV doesn't lend itself to diagnosing issues among ad-hoc, non-parameterized queries that are essentially the same query except for the different literal values specified within the query text. (There will be a different query plan for each statement in cases where these statements are not parameterized by MSSQL Server. The end result is that resources consumed by seemingly "identical" queries from the application perspective aren't aggregated on the same query plan. ). For these and other reasons, the sys.dm_exec_query_stats DMV and the Expensive Queries Report cannot be viewed as a comprehensive accounting of all resource utilization by MSSQL Server.

It is also important to remember that resources leveraged by a query are not recorded in the sys.dm_exec_query_stats DMV until the query successfully completes execution.  Therefore, if the query is aborted due to a timeout or other error there will be no record of the resources consumed prior to the failure. Moreover, maintenance operations like dropping or taking a database offline, changing certain options via sp_configure, and so forth may cause the procedure cache to be cleared, and workloads requiring memory intensive operations like scans, sorts or hashes (which may put pressure on the procedure cache) can result in freeing query plans and releasing memory.

The top section of the Expensive Queries Report charts the top 20 queries with the highest values for the specified attribute (reads, writes, CPU, duration, etc), which is useful in visually interpreting the magnitude of difference in resource consumption for various queries.  It is quite common to see that the overall system performance is negatively influenced by a few queries that run poorly.  These situations are very noticeable on the chart, as seen in the example partially depicted in Illustration 10.

Details about a given query appear in the table underneath the chart (how many times it has executed, when the plan was first cached, and so forth).  Clicking on the blue data point in the chart or on the query text allows us to navigate to the respective Query Plan Report (which we discuss in its own section), which reveals details about the query plan for the statement.  Much information to support diagnostics can be obtained via these related reports, as is obvious to those of us performing administrative functions on a regular basis. 

Report: General Waits Report

File Name: wait_generic.rdl

Performance Dashboard Access “Click” Point:

Purpose and Details:

The General Waits Report displays a chart showing the total Wait Time and number of Waiting Requests grouped by the Wait Category.  It displays information for all currently occurring wait types.  In the example presented in Illustration 11, both Sleep and Buffer Latch Waits are shown in the chart.

Had the consumer clicked on the Buffer Latch Category on the primary Dashboard, page they would have been taken to the Buffer Latch Report (discussed within its own section of this article) designed specifically for that scenario.  In the example illustrated here, we can assume that the user clicked on the Sleep Wait Category - since there is not a specific report tailored for that type of problem, they were navigated to the General Waits Report.  (The General Waits Report also presents information about other Waits, for which the consumer might have obtained more specific information by clicking appropriately on the primary Dashboard.)

The table under the chart breaks down the percentage of time associated with each Wait Category.  It lists each waiting Session / Request and allows us to see the query executing, as well as to drill through to obtain more detailed information about the Session.