• Article

Query Statistics History Report

The Query Statistics History report contains query execution statistics. Data used in this report is obtained by using the Query Statistics collection set, which uses the Query Activity Collector Type.

You can access the Query Statistics History report from Object Explorer. To view the report, expand the Management folder, right-click Data Collection, point to Reports, point to Management Data Warehouse, and then click Query Statistics History. For more information, see How to: View a Collection Set Report.

Selecting Data to Include in the Report

The report includes query execution statistics for the entire data collection period. There are two ways you can navigate through the data collection timeline to select a segment of the data to view.

Timeline Control and Navigation Buttons

Use the timeline control and navigation buttons to move through the timeline or to select a date range. The arrow buttons provide left and right scrolling so you can move backward or forward through the timeline. By default, the arrows move through the timeline in 4-hour increments. Using the magnifier buttons, you can expand or contract this time increment to one of the following values: 15 minutes, 1 hour, 4 hours, 12 hours, or 24 hours. The currently selected time range is indicated by the highlighted portion of the timeline and is displayed in the text below the timeline. These values, as well as the data in the report, are updated whenever you click the timeline or use the navigation buttons.

Calendar Button

Use the calendar button to specify the start date, start time, and duration of the data that you want to report on.

Query Statistics History Report

The Top Queries by Total CPU graph shows the relative expense of each query for the selected time range based on total CPU usage. To display a different view of relative query expense, click one of the hyperlinks provided below the graph: Duration, Total I/O, Physical Reads, or Logical Writes.

The table below the graph provides additional query data. It lists the text for each query that is graphed along with detailed statistical information. Note that the graph bars are active links, as are each of the queries shown in the table. Clicking an active link opens the Query Details subreport for the query.

Query Details Subreport

The Query Details subreport provides the entire query text. There is an Edit Query Text hyperlink adjacent to the query. You can click this link to open the query in Query Editor. The table below the query provides query execution statistics, such as the average duration per query execution.

A graph of query plans and the average duration per execution is displayed. To display a different view of relative query plan cost, click any of the hyperlinks that are displayed below the graph: Duration, Physical Reads, or Logical Writes. The graph line is active and you can click any point to open the Query Plan Details subreport.

The table below the graph shows the top 10 query plans, based on CPU use per execution. Each number in the Plan # column is an active link that you can click to open the Query Plan Details subreport.

Query Plan Details Subreport

This report displays the information for a query plan. In addition to enabling you to edit the query and view execution statistics, the report provides detailed information about the query plan. The View graphical query execution plan hyperlink opens a graphical representation of the execution plan for the current query. For more information, see Displaying Graphical Execution Plans (SQL Server Management Studio) and Graphical Execution Plan Icons (SQL Server Management Studio).