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.
» 
Comment and Contribute
