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

Monday Apr 16th 2007 by William Pearson
Share:

BI Architect Bill Pearson continues his overview of the new Performance Dashboard for MSSQL Server. In this article, we examine the individual member reports and discuss their basic uses.

About the Series ...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services (“Reporting Services”), presenting an overview of its features, with tips and techniques for real-world use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools needed to complete the hands-on portion of this article, see BlackBelt Administration: Linked Reports in Report Manager, another article within this series.

About the Black Belt Articles ...

As I state in BlackBelt Components: Manage Nulls in OLAP Reports and other articles of this subseries, the Black Belt articles represent an attempt to minimize the setup required in simply getting to a point within an article where we can actually perform hands-on practice with the component(s) or method(s) under consideration. I typically accomplish this by using existing report samples or other “prefabricated” objects that either come along as part of the installation of the applications involved, or that are otherwise readily accessible to virtually any organization that has installed the related components of the Microsoft business intelligence solution. In the Black Belt Administration articles, I focus upon procedures and tools that specifically relate to the job of the Reporting Services Administrator, versus report authors and other developers, although in real world scenarios, these roles often intersect.

For more information about the BlackBelt articles, see the section entitled “About the BlackBelt Articles” in BlackBelt Components: Manage Nulls in OLAP Reports.

In this article, we extend the meaning of “samples,” as well as the definition of “Administrator,” a bit further. (The Performance Dashboard can serve as an excellent tool for DBAs, as well as other “administrators” within the various layers of the integrated Microsoft BI solution.) In our last article, Performance Dashboard for Microsoft SQL Server, Part I, we ran the installation package provided by Microsoft to install the integrated reports comprising the Performance Dashboard. To gain the most benefit from this article, the installation steps we undertook should have been performed.

Overview

In Performance Dashboard for Microsoft SQL Server, Part I, we introduced the Performance Dashboard for Microsoft SQL Server (“Performance Dashboard Reports”). We noted that this “report pack,” made available on the heels of the release of Service Pack 2 for MSSQL Server 2005 will, perhaps, be the most warmly received Reporting Services report set of its type to date. We noted that the Performance Dashboard Reports are a set of report files designed to be run from within SQL Server Management Studio.  Targeted toward MSSQL Server Database Administrators and other users; the objective of the report set is to act as both a health monitoring and diagnostic tool.

We mentioned that, although the Performance Dashboard Reports set relies upon Reporting Services definition files (.rdl), Reporting Services does not need to be installed to use this custom report set. The Performance Dashboard and its child reports rely upon SQL Server's dynamic management views (DMV’s) as data sources, providing the wealth of data the DMV’s contain, while insulating the viewers of the information from the views and the structures underlying them. No additional sources, data capture or tracing is required to access and use this storehouse of performance information. Other obvious benefits of using these prefabricated views are constant availability of the information they contain and their inexpensive nature (from the tandem perspective of collection and querying) as a source of server monitoring.

As a part of our introduction in Performance Dashboard for Microsoft SQL Server, Part I, we further mentioned that the report set comes with a primary dashboard report file, which we loaded directly as a custom report in SQL Server Management Studio as part of our hands-on installation procedure. At that time, we noted that the linkages between the Dashboard report and the underlying drillthrough reports are pre-constructed, and that, once the dashboard report is loaded as a Custom Report in Management Studio, the rest of the reports work “out of the box” automatically, without any additional setup. The end effect is that the primary dashboard gives a summarized status overview, while serving as a multi-directional launch point from which we can drill into the specific details we need to diagnose problems, inefficiencies, and other subjects of analysis and reporting with which we develop interests or concerns.

In this article, we will overview the individual, underlying members of the Performance Dashboard Reports, and more specific details of the drillthrough operation through which we reach them. Along the way, we will summarize the general purposes for each report as intended by its authors. As a part of our continued examination of the Performance Dashboard Reports, we will:

  • Review select details about the new Performance Dashboard for MSSQL Server, as introduced in Part I of this article;
  • Explore the individual, supporting drillthrough reports that, together with the primary Dashboard, comprise the Performance Dashboard for MSSQL Server;
  • Identify each report by name and file name, exposing the means of access to the report, together with its general purpose and other descriptive details.

Performance Dashboard for Microsoft SQL Server: The Reports

Extending well beyond earlier “report pack” releases, which focus more upon providing “guided samples” in adapting Reporting Services to various Microsoft applications and databases, Performance Dashboard was designed as a set of custom reports to be hosted within SQL Server Management Studio. The reports were written to deliver information from a consistent and familiar tool already used by database administrators.  According to the documentation, there were several key design goals for this effort:

  • Zero system impact when the dashboard is not being used.  The dashboard uses no background agents or jobs, and the only time there is any system impact is when a user actually runs a report (at which time Management Studio runs the queries specified in the report definition as required to render the report);
  • Read-only access to the server.  The dashboard reports never perform any system modifications;
  • No requirement to install any binaries (extended stored procedures, CLR assemblies) on the server;
  • No requirement to create any special database/tables to store trending or historical information.  The reports do require a one-time installation of some procedures and functions in the msdb database, with which we got hands-on practice in the installation we undertook in Performance Dashboard for Microsoft SQL Server, Part I;
  • The efficient display of information necessary to identify the most frequently observed performance problems;
  • Assisting administrators and other consumers in increasing their familiarity with the wealth of information available in the SQL Server 2005 dynamic management views (“DMVs”).

As we noted in the Preparation section of our installation procedure in Performance Dashboard for Microsoft SQL Server, Part I, minimal (but important) system requirements must be met for installing and using Performance Dashboard Reports. We touched upon these, illuminating some of the unclear points in the rather sketchy documentation that accompanies the report set, in the steps of the installation that we undertook.

NOTE: To receive the most benefit from this article, you should first install the Performance Dashboard Reports that form its focus. An expanded, step-by-step installation is provided in Performance Dashboard for Microsoft SQL Server, Part I.

We will perform our practice session from inside the MSSQL Server Management Studio. For more exposure to the Management Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see other articles in this series, as well as within my Database Journal series Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to keep to the focus of the article more efficiently.

Preparation: Open MSSQL Server Management Studio and Execute the Primary Dashboard Report

For purposes of our exploration of the Performance Dashboard Reports, we will enter the MSSQL Server Management Studio. In Performance Dashboard for Microsoft SQL Server, Part I, this was where we performed the initial inspection of our local MSSQL Server installations to ascertain compliance with the relatively minimal system requirements to support the set of custom reports. We installed the primary Dashboard report as a custom report within Management Studio, once we had determined that Service Pack 2 was in place, and that the other requirements were met (demonstrating how to check compliance from within the Management Studio itself, prior to the installation).

1.  Click the Start button on the PC.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 1.


Illustration 1: Opening SQL Server Management Studio

The Connect to Server dialog appears, after the brief Management Studio splash screen.

4.  Select Database Engine in the Server type selector.

5.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

6.  Supply authentication information, as required in your own environment. (I have selected Windows Authentication in my own environment.)

The Connect to Server dialog appears similar to that depicted in Illustration 2.


Illustration 2: Preparing to Connect to the Server ...

7.  Click the Connect button to connect with the specified SQL Server Database Engine server.

The SQL Server Management Studio opens.

Procedure: Examine and Overview Performance Dashboard Reports

Open and Examine the Primary Dashboard Report with the Custom Reports Functionality

As we have noted in Performance Dashboard for Microsoft SQL Server, Part I, we access the Performance Dashboard Reports from the primary Dashboard report. We drill through to the subordinate reports via respectively associated navigation points on the Dashboard. (Because parameters for the subordinate, target reports are passed via the drillthrough mechanism, and because at least one parameter is required to run each of these reports, the subordinate reports must be opened / executed from the Dashboard report.) Recall that we load the Dashboard report as a custom report within SQL Server Management Studio: we will thus begin our exploration of the individual drillthrough reports by opening the report file named performance_dashboard_main.rdl.

1.  Right-click the appropriate server instance within the Object Browser.

2.  Select Reports ... from the context menu that appears.

The first time we choose the Custom Reports ... option, we are prompted to choose the report to run.  In Performance Dashboard for Microsoft SQL Server, Part I, we browsed to the share into which we had placed the performance_dashboard_main.rdl report file (as a part of our installation procedure), where we selected it for loading as a custom report.  Assuming we have performed the installation in Part I already, we can access the Dashboard report via the recent file list, at this stage, instead of manually browsing to the share, again as we shall see in the next step.

3.  Select performance _dashboard_main from the recent file list section of the cascading menu that appears next, as shown in Illustration 3.


Illustration 3: Select Reports -> Performance _dashboard_main ...

4.  Click Run on the Run Custom Report dialog that appears next, warning us that we are about to execute a Custom Report, as depicted in Illustration 4.


Illustration 4: Click Run to Open / Execute the Dashboard Report

We briefly see indication that data is being retrieved, and then the Performance Dashboard opens within a new tab, appearing similar to that shown in Illustration 5.


Illustration 5: The Performance Dashboard within a New Tab

NOTE: The appearance of the local reports that you examine within this session will differ from those depicted from within my environment (and from the sample report illustrations, as well). Obviously, some details may not appear at all, depending upon the load currently experienced by the server upon which the Performance Dashboard Reports are executed. To “stimulate” the readings a bit, you might consider executing a few long-running operations (such as a large database import, sample cube processing, etc.) in the background, as we proceed through the steps that follow.

At this point, we will consider examples of the drillthrough reports underlying the Performance Dashboard, briefly explaining the purpose of each as we encounter it.

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:

A Lock Wait Category data point in the Current Waiting Requests chart.

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


Illustration 6: Example Display: Blocking Report

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:

Buffer IO Wait Category data point in the Current Waiting Requests chart.

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.


Illustration 7: Example Display: Buffer IO Waits Report

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:

Buffer Latch Wait Category data point in the Current Waiting Requests chart of the primary Dashboard.

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.


Illustration 8: Example Display: Buffer Latch Waits Report

Report: Databases Overview Report

File Name: database_overview.rdl

Performance Dashboard Access “Click” Point:

Databases link on the primary Dashboard

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.


Illustration 9: Example Display: Databases Overview Report

  • 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:

Links that appear under the Expensive Queries label on the primary Dashboard.

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.


Illustration 10: Example Display: Expensive Queries Report (Partial View)

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:

Wait Category data point in the Current Waiting Requests chart (for which no specific report is tailored for the associated problem type).

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.


Illustration 11: Example Display: General Waits Report

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.

Report: Historical IO Report

File Name: historical_io.rdl

Performance Dashboard Access “Click” Point:

IO Statistics link on the primary Dashboard (Historical Information section).

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.


Illustration 12: Example Display: Historical IO Report

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:

Waits link in the Historical Information section of the primary Dashboard.

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.


Illustration 13: Example Display: Historical Waits Report

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:

Latch Wait Category data point in the Current Waiting Requests chart

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.


Illustration 14: Example Display: Missing Indexes Report

Report: Missing Indexes - XML Showplan Report

File Name: missing_index_from_showplan.rdl

Performance Dashboard Access “Click” Point:

Link on the Query Plan Report when specific conditions are present (as discussed in the Purpose and Details section below).

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.


Illustration 15: Example Display: Warning Icon Based upon Missing Indexes Recommendation

Report: Page Details Report

File Name: page_details.rdl

Performance Dashboard Access “Click” Point:

Link on the various wait-related reports, when the Wait Resource is a database page.

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.


Illustration 16: Example Display: Page Details Report

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:

Link on Query Plan Report when the SHOWPLAN XML indicates that the query matches a Plan Guide.

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.


Illustration 17: Example Display: Plan Guide Report

Report: Query Plan Report

File Name: query_plan.rdl

Performance Dashboard Reports Access “Click” Points:

The text of the respective SQL query displayed in various other Performance Dashboard reports.

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.


Illustration 18: Example Display: Query Plan Report

Report: Recent CPU Consumption

File Name: recent_cpu.rdl

Performance Dashboard Access “Click” Point:

The CPU utilization data point for blue legend item “SQL” on the main dashboard report. 

Purpose and Details:

The Recent CPU Consumption Report helps us to determine what queries or sessions are the largest contributors to current MSSQL Server CPU utilization. Recent CPU Consumption presents an estimate of the amount of CPU consumption that has occurred since the "Activity Since" time displayed at the top of the report.  The Activity Since time is determined by a parameter passed to the report, and is the earliest time value displayed in the CPU utilization history chart on the primary Dashboard.

By its nature, the CPU consumed value is approximated (as we have noted in Performance Dashboard for Microsoft SQL Server, Part I, as well as earlier in this article, MSSQL Server stores limited historical detail in the DMVs, while the primary Dashboard does not store any historical information). Assumptions behind the estimate include counting an entire session's CPU if the session logged in after the Activity Since, as well as reflecting all CPU consumed by a given request if the request started after the Activity Since time. Above and beyond these considerations, potential recent CPU utilization can be approximated by taking the average rate of CPU consumption per millisecond over the lifetime of the session/request and calculating how many milliseconds the session/request could have been active within this time window.  This method is used to estimate CPU consumption and show any sessions that have at least one second of CPU time.

As the documentation states, the temptation to concentrate upon the Expensive Queries Report (which we discuss elsewhere within this article) for high CPU should be balanced with the understanding that the Expensive Queries Report shows the queries with the highest cumulative CPU consumption over the lifetime of the cached plan, even if the query didn't run within the time frame of interest. 

The format of the data presented within the bottom portion of the Recent CPU Consumption Report appears similar to that presented in the same area of the Expensive Queries Report, but the Recent CPU Consumption Report filters the data so that it only shows queries which had one successful completed execution within the affected time window.  (It is important to keep in mind that all of the CPU consumption reported in this portion of the report may not have occurred during the time window, but may have been accumulated over the time since the plan was first cached.)

An example presentation of the Recent CPU Consumption Report is partially depicted in Illustration 19.


Illustration 19: Example Display: Recent CPU Consumption Report (Partial View)

Report: Requests Overview Report

File Name: requests_overview.rdl

Performance Dashboard Access “Click” Point:

The User Requests column heading (which forms a link), within the Current Activity table in the lower half of the primary Dashboard.

Purpose and Details:

The Requests Overview Report displays a list of all currently executing requests on user sessions.  The report presents the reads, writes, cumulative CPU consumed, current wait information, and so forth for each executing query.

The source for the Requests Overview Report is the sys.dm_exec_requests DMV. An example presentation of the Recent CPU Consumption Report is partially shown in Illustration 20.


Illustration 20: Example Display: Requests Overview Report

Report: Session Details Report

File Name: session_details.rdl

Performance Dashboard Access “Click” Point:

Session ID link in various member reports of the Performance Dashboard set.

Purpose and Details:

The Session Details Report presents detailed information about the session whose ID appears in the parenthesis on the right side of the report title. The report displays basic details, including the application and login name, the number of reads / writes that have been performed by the session, the login time, last batch start / completion time, and the current SET options in affect for the session. 

The data source for the Session Details Report is the sys.dm_exec_requests DMV. Pending requests for the session are also shown, and the consumer is allowed to see currently executing queries, as well as to drill through, if desired, to see the associated query plan. If a scenario exists where there are no pending requests for the session, but where there is a valid sql_handle describing the last request by the session, then the last batch information will be displayed.  (This is very useful in blocking scenarios where the server is awaiting the client application’s commit or rollback of a transaction, etc.)

An example Session Details Report display is partially depicted in Illustration 21.


Illustration 21: Example Display: Session Details Report (Partial View)

Session Overview Report

File Name: session_overview.rdl

Performance Dashboard Access “Click” Point:

The User Sessions column heading (which forms a link), within the Current Activity table in the lower half of the primary Dashboard.

Purpose and Details:

The Sessions Overview Report, whose underlying data source is the sys.dm_exec_sessions DMV, presents a list of all current user sessions. The report can be used as a launch point for investigating activities, statistics, and so forth for users connected to the server. Drillthrough to the Session Details Report is accomplished from the Sessions Overview Report by simply clicking the link for the desired ID in the Session ID column on the left of the table that forms the report.

An example Session Overview Report display is partially shown in Illustration 22.


Illustration 22: Example Display: Session Overview Report (Partial View)

Report: Traces Report

File Name: traces.rdl

Performance Dashboard Access “Click” Point:

Link (visible when one or more traces is running) on the primary Dashboard in the Miscellaneous Information section. 

Purpose and Details:

The Traces Report presents a list of all currently enabled traces, and allows the user to see which events/columns are captured in those traces. 

In addition to the lightweight, default trace that ships with MSSQL Server, other traces can be enabled (user-defined, the C2 audit trace, etc.). Too many traces, traces capturing high-volume events or columns with significant data density, etc., can impair the overall system with bottlenecks, as can rowset-based traces using Profiler (versus tracing directly to a file) and other suboptimal arrangements.

The Traces Report displays a warning for traces configured to capture events which are frequently occurring or that typically produce a large amount of trace data.  This helps administrators to identify traces for which it is advisable to avoid capturing events, at least in a routine production environment, unless strictly required, to prevent potential performance problems on the system.

An example Traces Report display is depicted in Illustration 23.


Illustration 23: Example Display: Traces Report

As we have noted, we can drill between various reports as well as launching individual drill paths from the primary Dashboard. If you wish to test the drillthroughs and other features, this is a great time to continuing building familiarity with all the parts and pieces.

5.  Perform additional drillthroughs from the primary Dashboard to underlying support reports, as well as from underlying reports to other reports, as desired, by clicking the respective hyperlinks in the Performance Dashboard Reports involved.

6.  Select File -> Exit, when ready, to leave SQL Server Management Studio, as shown in Illustration 24.


Illustration 24: Select the Primary Dashboard Report File ...

Conclusion ...

In this article, we overviewed the individual, underlying members of the Performance Dashboard Reports, and discussed specific details of the drillthrough operation through which we reach them. We reviewed select details about the new Performance Dashboard for MSSQL Server, as introduced in Part I of this article, before beginning a brief exploration of each of the individual, supporting drillthrough reports that, together with the primary Dashboard, comprise the Performance Dashboard for MSSQL Server.

We examined each report within the Performance Dashboard set, identifying each by name and file name. We exposed the means of access to each report (some solely from the primary Dashboard and others from multiple access points). Finally, we discussed the general purpose of each report, together with other descriptive details.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved