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.

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 WaitCategory 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_STOPWait 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_YIELDWait 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 ServerBooks 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.

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.

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 ShowplanXML 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 ServerBooks 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.