Search Wiki:

Report Overview


The Query Hash Statistics reports are designed to be viewed in SQL Server Management Studio (SSMS) from SQL Server 2008 or later. To open the reports, connect SSMS to the SQL Server instance that hosts your MDW database, right-click on the MDW database in Object Explorer, and select "Custom Report..." from the Reports submenu. Browse to the location where you extracted the Query Hash Statistics files, and open MdwOverviewCustom.rdl. You will not need to manually browse to the .RDL file after this; you'll find the report name in the Reports context menu. The first time you open a custom report, SSMS will ask you to confirm that you trust the report.

The top-level Query Hash Statistics report summarizes the total cost of the queries run on the server in a selected time window. The report defaults to showing query cost over the most recent four hour window; if you have just started the collection set a few minutes ago, you may wish to click the magnifying glass icon under the timeline at the top of the report to zoom in to a 15 minute or 60 minute window. Alternatively, click the calendar icon(calendar_icon.png) to enter a date/time and time window size. Note that if your server is relatively idle there may be very little data to examine; this collection set is most interesting and useful on a server with an active query workload.

rpt_query_hash_statistics.png

The queries are numbered in a way that indicates their relative costs. You can change the ranking metric from total CPU cost to total duration, reads, writes, and so on by clicking on the links immediately below the chart. If you wish to view more than the default top 10 queries, you can expand the view to 100 or even the top 1000 queries by clicking the links immediately above the table.

Within the table at the bottom of the report, the top queries are grouped by database along with a database-level rollup of query cost. The query execution costs shown on this report are for all executions of the query within the time window you have selected. You can click on any query’s text to drill through to a detailed report for that query:

rpt_query_hash_details.png

Clicking on the "Sample Query Text" link at the top of the report will launch a new query window with the full statement query text. The "Query Execution Statistics" section in the middle of the report shows three types of statistics: average cost for individual executions of the query (upper-left), average rate of resource use by all executions of the query (lower-left), and total cost for all executions of the query in the selected time window (upper-right). The chart shows the average cost of the various plans used for this query over time. You can change the type of resource used in the chart by clicking the links immediately below the chart ("CPU", "Duration", etc). Below the chart is a table that lists the plans that have been used for this query, and summarizes the cost of each plan. In this example, two different query plan variants were observed for this query; plan #1 tended to use almost double the CPU and ran about twice as slowly as plan #2.
rpt_query_hash_details_stats.png
You can click on the plan number in this table to launch a query window from which you can view the graphical query plan.

The reports were implemented as fairly standard SQL Server 2005 Reporting Services reports using the SQL Server 2005 Business Intelligence Development Studio.

To grant a non-admin user the permissions needed to view the reports, make the user a member of the mdw_reader role in the MDW database. Note that this will also give the user the ability to see data that was uploaded to that MDW database by other collection sets.

(Continue reading about the query hash statistics collection set implementation, or return to the top.)
Last edited Mar 5 2013 at 10:43 PM  by bartd, version 8
Updating...
Page view tracker