Document Sample
performance-dashboard-for-microsoft-sql-server Powered By Docstoc
					Performance Dashboard for Microsoft SQL Server: The
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”).


Download Link:

Click the Download button on this page to start the download.
Do one of the following:

1. To start the installation immediately, click Open or Run this program from its current

2. By default all the RDL file will install on that link C:\Program Files\Microsoft SQL

3 Run the Setup.sql file on each instance of SQL Server 2005 that you wish to monitor with
the SQL Server 2005 Performance Dashboard Reports.
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

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

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.

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

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

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

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

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

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

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

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

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

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

Shared By: