Query Statistics Reports
The “Query Statistics” report is displayed just like I to display the “Disk Usage” reports, by right click on the “Query Statistics” data collection item in Object Explorer, and then clicking on the “Query Statistics History” report item. When I do this on my machine the following report is displayed (two screenshots are displayed due to this size of the report):
This report is broken up into three different sections. The top section of the first screenshot contains a navigation timeline chart that allows you to select the timeframe you want to display for the reports. You can click on the plus (+) and minus (-) magnifying glass symbols to change the time frame for the display. The plus (+) sign allows you do zoom in, or decreased the interval of the display, or you can use the minus (-) sign to zoom out the display to show are larger interval. In this particular display my time frame is from 6/18/2008 at 2:28 AM to 6/18/2008 at 6:28 AM. The triangular symbols allow you to move back and forth through the timeline. You can also click on one of the light blue bars in the timeline to display query statistics for that time frame. Note that the current timeframe is highlighted by a dark blue color. This smallest duration you can zoom in and look at is 15 minutes, and the largest timeframe you can zoom out to is 24 hours.
The second part of this graph displays a graphical representation for resource usage. Five different types of resource usage measurements can be displayed: CPU, Duration, Total I/O, Physical Reads and Logical Writes. My screenshot above displays the 10 most expensive queries based on CPU usage. The screenshot above didn’t identify any queries with significant CPU usage. If there had been some that used a significant amount of CPU then you would see some bars in the “Most Expensive Queries by Total CPU” bar chart. You can change the display to another category by clicking on the “Rank Queries By” hyperlinks. If there are vertical bar to represent resources usage for any of the queries then you can drill down into detail of that query by clicking on the bar.
The last section of the above screen shots, lists the top 10 queries (found in the second screen shot). You can drill down into the details of these queries by clicking on the hyperlink in the “Query“ column. Below is the screen that is displayed when I drill down into the first query in my report above (note there are two screen shots since the report needs to be scrolled to see entire report):
On this report you get the complete text of the query, and a hyperlink to edit the query. Being able to edit the query allows an easy method to test, and modify the query to resolve the resource issues associated with the query. You can also drill down further into the different Query Plans. In the example there is only one plan identified. For some queries multiple plans might be displayed.