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

Report: Historical IO Report

File Name: historical_io.rdl

Performance Dashboard Access “Click” Point:

Purpose and Details:

The Historical IO Report is composed of two complementary views of IO activity. The top table presents how much IO is occurring within each database, along with the corresponding average IO response time.  Expanding the table at a database level allows us to see this same perspective on a per-file level within the database.  This also allows us to ascertain at a glance which database accounts for the most IO on the server as a whole (either reads, writes, or combined reads and writes, as applicable).  The data in this table comes from the sys.dm_os_virtual_file_stats DMV, and the IO statistics for each database are cumulative values (since server startup or creating/bringing a particular database online). 

The second table in the report shows the top 20 objects within each database responsible for the most IO wait time.  This table is populated with data from the sys.dm_db_index_operational_stats function, which retrieves information from internal, in-memory data structures associated with each index (subject to be freed to release memory for other needs).  It is not possible to determine when this data structure for the object was loaded in memory (and thus started aggregating IO statistics). 

An example Historical IO Report display is shown in Illustration 12.

The number of seeks or scans against the table is presented as a general indicator of how long the statistics have been aggregating. (A high rate of physical IO is commonly due to lack of appropriate indexing to support the queries.)  The second table cross-references each object with the missing index, and provides a link whereby we can jump to the Missing Index Report (discussed in its own section within this article) to see the resulting specific index recommendations.

Report: Historical Waits Report

File Name: historical_waits.rdl

Performance Dashboard Access “Click” Point:

Purpose and Details:

The Historical Waits Report displays a chart showing the total Wait Time and number of Waits that have occurred for each wait category.  A table at the bottom of the report shows the same data, but also allows one to see the details for each Wait Type within each Wait Category.

Knowing the type of resource upon which most commonly occur (or for which waits are the longest) can help in determining the primary bottleneck for a particular instance. When an MSSQL Server worker thread has to wait for access to a resource, it sets a Wait Type.  Wait information in the sys.dm_exec_requests and sys.dm_os_waiting_tasks DMVs is available for our review while the thread is waiting. Moreover, MSSQL Server maintains an aggregate count of the number of times each type of wait has occurred and the cumulative time spent for all waits on a given type of resource.  This aggregate wait information is maintained per instance since SQL startup (or since last reset via DBCC SQLPERF), and is exposed through the sys.dm_os_wait_stats DMV.  Because there are over 200 distinct wait types, the Performance Dashboard uses the concept of a Wait Category to provide a high level reason for a given wait. 

An example Historical Waits Report display is depicted in Illustration 13.

The Historical Waits Report often reveals that the Sleep and Other Wait Categories are among those with the highest Wait Time.  A high Wait Time for the Sleep Wait Category is rarely an indication of an actual performance problem:  The Sleep Wait Category consists of wait types set by background tasks (such as checkpoint, lazywriter, etc) awaiting more work or T-SQL queries via the WAITFOR command, and thus these are typically normal and expected delays .

The Other Wait Category embodies miscellaneous wait types that don't conveniently map to an existing group.  Within that group, the BROKER_TASK_STOP Wait Type is a common wait that may occur while a Service Broker Task is terminating, and is unlikely to be an indication of a performance problem.  The SOS_SCHEDULER_YIELD Wait Type (Scheduler Yield Wait Category) records the time a thread spends waiting to be run after yielding to other runnable threads. A high value for this Wait Type is an indication that there are periods of time where the server is CPU bound.

More information about the meaning of each Wait Type may be found within the sys.dm_os_wait_stats help topic in the MSSQL Server Books Online.  A good understanding of the meaning of Wait Types is key to tracking down why and where waits are occurring, together with the changes we can make to avoid or reduce the waits.

Report: Latch Waits Report

File Name: wait_latch.rdl

Performance Dashboard Access “Click” Point:

Purpose and Details:

The Latch Waits Report displays all requests currently waiting for a non-buffer Latch, the wait resource and wait time, the query each request is running, and so forth.  The wait_resource contains a Latch Class that describes the type of resource/structure that the Latch protects.

(Latches are a lightweight multiple reader/single writer locking mechanisms, widely used for synchronizing access to various internal data structures or objects, that do not participate in deadlock detection.)

The bottom portion of the Latch Waits Report also displays a table showing aggregate information about how often each type of Latch Wait has occurred, and total time and maximum time spent waiting on each Latch Type.  This can be used to determine whether a given type of Latch Wait is significant enough to become a bottleneck that requires further investigation.

The Microsoft Knowledge Base should be referenced for any known issues related to latch contention scenarios that we encounter in our local environments (we can use the Latch Class from the wait_resource column to search the Knowledge Base). More elusive or undocumented problems can be explored with Microsoft support.

Report: Missing Indexes Report

File Name: missing_indexes.rdl

Performance Dashboard Access “Click” Point:

• Missing Indexes link on the primary Dashboard (Miscellaneous Information section), or
• Link on the Historical IO Report for a table with significant IO, which is determined to also have a missing index recommendation.

Purpose and Details:

When the first method of access is available and used, the Missing Indexes Report presents all missing index recommendations.  When the Missing Indexes Report is reached as a drill through from the Historical IO report it presents recommendations for the specific table involved. When the Missing Index DMVs contain entries, a Missing Indexes link will appear in the Miscellaneous Information section of the primary Dashboard page providing access to this report. 

The Overall Impact column in the Missing Indexes Report shows the estimated percentage improvement that could be achieved by implementing the recommendation. It is important to realize that this impact must be multiplied by the overall cost of the query plan itself.  For example, an estimated overall impact of 50% improvement for a plan with cost of 100 should provide greater benefit than one with estimated improvement of 95% and a plan cost of 10 (50% reduction for a plan with cost of 100 is a larger improvement than a 95% improvement for a “cheaper” plan with cost of 10).

The Report Parameters toggle box at the bottom of the report shows whether the results are filtered to a specific database or object. An example Missing Indexes Report display is shown in Illustration 14.

Report: Missing Indexes - XML Showplan Report

File Name: missing_index_from_showplan.rdl

Performance Dashboard Access “Click” Point:

Purpose and Details:

Each query plan records details about any missing index recommendations associated with that plan.  The missing index recommendation is also added to the missing index DMVs, but the recommendation can be aged out of cache even though the query plan is still valid.  When we retrieve the SHOWPLAN XML or STATISTICS XML output we will still see the original missing index details (without details about the number of compiles/recompiles, seeks, scans, etc).

The Query Plan Report (examined in its own section within this article) displays a warning icon and a link to this report any time the SHOWPLAN XML contains one or more missing index recommendations, as depicted in Illustration 15.

Report: Page Details Report

File Name: page_details.rdl

Performance Dashboard Access “Click” Point:

Purpose and Details:

The Page Details Report displays details about a particular database Page, including the database that it is in, the table to which the Page belongs, and the Page Type (e.g., data, index, allocation).  If the Page is an index Page, the Level indicates the level within the b-tree. 

An example Page Details Report display is shown in Illustration 16.

Understanding the Page Type can be useful in determining whether the performance issue is associated with a particular index, text/image data, or other consideration, and may be instrumental in determining ways to avoid the contention on that page.

Report: Plan Guide Report

File Name: plan_guide.rdl

Performance Dashboard Access “Click” Point:

Purpose and Details:

The Plan Guide Report displays details about the Plan Guide and the associated Query Hint that was applied during query compilation.  (Plan Guides are typically created by a database administrator or independent software vendor in order to control query behavior in some way.  A common use of Plan Guides is to force a query plan with the USE PLAN hint.)

The optimal Plan is dictated by data distribution, available indexes, and other factors.  If these factors have changed since the database administrator or software vendor first created it, the Plan Guide could actually hurt performance.  (If we believe the Plan Guide is having a negative impact, we can temporarily disable it using the sp_control_plan_guide system stored procedure while we test the behavior.)  Depending upon whether the Plan Guide actually helps or hurts performance, we can then re-enable or drop it using the same stored procedure.

An example presentation of the Plan Guide Report is depicted in Illustration 17.

Report: Query Plan Report

File Name: query_plan.rdl

Performance Dashboard Reports Access “Click” Points:

Purpose and Details:

The Query Plan Report presents the query text and its associated Query Plan in a format that is similar to the legacy SHOWPLAN_ALL output.  The output is actually produced from the SHOWPLAN XML output obtained for the statement.  The information available in SHOWPLAN XML is a superset of what is available in the legacy formats, and at times there is additional information in the SHOWPLAN XML, which isn't displayed in the legacy showplan format.  (We can view the original raw SHOWPLAN XML output for the statement by expanding the View Showplan XML item.)

If the query is a part of a SQL module, such as a function or stored procedure, the report will also show the associated module name, together with identification of the database where the module is located. 

If the query matches a Plan Guide, the Plan Guide name will be shown, together with a link to allow us to access and view a report showing details about the Plan Guide (this report is discussed in its own section of this article).

If there are any index recommendations generated by the optimizer, a warning will be displayed allowing us to access and view an associated Missing Index report (also discussed in its own section of this article), where recommendations are detailed.

The Query Plan Report will present the parameter value used by the optimizer when compiling the Plan, for queries containing parameters. The Understanding Plan Guides topic in the MSSQL Server Books Online provides more information about this topic, as well as information surrounding how the OPTIMIZE FOR hint can be used to provide consistent optimization behavior independent of the parameter values supplied in the user query.

The portion of the Query Plan Report showing the query plan table will prominently present any warning in the first column of the output, highlighted in yellow.  Missing statistics (a common cause of poorly performing queries) can thus be identified, and we can remedy these scenarios easily by enabling the auto create statistics database option, or by manually running a CREATE STATISTICS statement for the specified column.  If the Plan contains a missing join predicate warning, the query may be missing an intended join predicate and should be reviewed for overall proper construction.

An example presentation of the Query Plan Report is shown in Illustration 18.