Query Statistics

  • Updated on Mar 25, 2025
  • Published on Nov 10, 2023
  • 6 minute(s) read
Prev Next

Query Statistics

Query Statistics provides a deep dive into historical data on query execution with a focus on resource cost, dollar cost, and efficiency. This data is derived from hourly deltas from the Query Statistics DMV or Query Store (if enabled) on the selected instance(s).

Unlike Activity, Query Statistics provides a largely unfiltered view of the workload, with a focus on proactive optimization to reduce cost and increase efficiency. This screen will generally be used to look data over longer time ranges; often weeks or even months.

  • Use the calendar widget to change the date range (default is last seven (7) days). The selected range is displayed on the card tool bar.
  • Use the Instance, Database, and Tag drop-down Filters to change the visualization.

Workload Analytics

The Workload Analytics card displays summary data to surface the areas in the environment or selected subset of the environment that drive costs, IO, CPU, or are the most or least efficient.

  • Top Overall by Resources will display the portion of the dollar, CPU, or IO cost attributed to the Top 10 SQL Statements when sorted by that metric versus the rest of the workload.
  • Top Instances by Resources will display the portions of the dollar, CPU, or IO cost attributed to each instance in descending order when sorted by that metric.
    • The top 5 instances plus the sum of all others meeting the filter criteria will be displayed.
  • Top Databases by Resources will display the portions of the dollar, CPU, or IO cost attributed to each database in descending order when sorted by that metric.
    • The top 5 databases plus the sum of all others meeting the filter criteria will be displayed.
  • Top Instances by Efficiency will display the five instances with the highest average efficiency rating in ascending order.
  • The Operational Analytics card displays summary information as well as change over the prior 7 days.

Slicers

To filter the data in the dashboard displays, click the Slicer icon to the right of the SQL Statistics card. Slicers both summarize data and allow filtering.

  • Top Instances shows total CPU and IO for each instance and allows filtering.
    • Where the overview displays only the top three (3) instances, this slicer displays a sortable list of all Instances by CPU and IO counts.
  • Top Databases displays a list of Databases by CPU and IO counts and allows filtering.
    • Like instances, the entire list of database meeting the filter criteria in effect for the screen will be displayed here.

Top Statements

This card groups result-sets for SQL Statement (default), Cost, Executions, CPU, IO, and Efficiency in a searchable table format with sortable columns that can be grouped by Instance, Database, Object, and SQL Statement.

  • Click on the column headings to sort the list in ascending or descending order.
  • The SQL Statement column shows the normalized SQL statement.
    • Note that this is inclusive of all identical SQL Statements across all instances and databases where that code has been executed.
    • A click on the > button to the left of the statement will expand it to display the metrics by execution context (Instance/Database).
      • The expanded view adds columns for Database and Object, as well as a vertical ellipsis View Details interactive widget to display the Query Details flyout for that query.
  • The Cost column displays the cost in dollars attributed to the query during the selected time period.
    • Cost modeling starts out as the query's share of an average of Azure and AWS IaaS cost plus SQL Server licensing but can be customized via the Admin Console.
  • The Executions column displays the count of executions of that statement during the selected time period.
  • The CPU column displays the cumulative CPU time attributed to that query over the time period.
  • The IO column displays the cumulative IO attributed to that query over the time period.
  • The Efficiency column displays the Efficiency rating for that query over the time period.
    • This is determined by the ratio of rows returned, inserted, updated, or deleted to the number of IO operations the query consumed.

Database performance on the code level can be further examined by using the Group By drop-down menu, as follows:

  • By Instance will group the workload by instance and display columns for Database, Object, SQL Statement, Cost, Executions, CPU, IO, and Efficiency.
  • By Database will group the workload by database and display columns for Instance, Object, SQL Statement, Cost, Executions, CPU, IO, and Efficiency.
  • By Object will group the workload by object and display columns for Cost, Executions, CPU, IO, and Efficiency.

Query Details Flyout

When View Details is selected the Query Details flyout is displayed. The flyout is composed of three tabs: Details, Query History, and SQL Statement.

Details

The Details tab is made up of five cards: Properties, SQL Statement, Query Statistics, Projected Total Cost of Code, and Query Information - CPU & Reads.

The Properties card displays the Instance, Database, Object (if applicable), Object Type (if applicable), First Execution, and Last Execution for the selected query.

The SQL Statement card displays a preview of the SQL Statement and can be expanded to display the full statement and/or copy to clipboard with the widgets in the top-right.

The Query Statistics card displays the totals and change vs the prior period for Executions, CPU, and IO and also displays a qualitative rating for the Efficiency of the query.

The Projected Total Cost of Code card displays 30 day, 1 year, 2 year, and 3 year projections for Executions, CPU, IO, Cost of Code, and Loaded Cost of Code.

  • Cost of Code is the query's share of the cost of the server and licensing based on its share of the full resource capacity of the server.
  • Loaded Cost of Code is what that cost would be if the server were able to run at 90% of it's full resource capacity 100% of the time for the projected time range.
    • This metric is often a better way of looking at the cost of any code that contributes to peak utilization on a server, as servers are sized based on peak utilization, and many have quite a bit of off-hours time where the workload is nowhere near capacity.

The Query Information - CPU & Reads card displays a chart of the count of Executions (default), CPU, or IO for the time range selected. This is broken down into sample intervals that will vary based on the time range selected, and can be customized using the Time widget in the top-right corner of the card.

  • Hovering the mouse over a time interval will display the metrics for that time interval at the top of the chart.

Query History

Query History breaks down the data for the selected query into the hourly deltas collected by WISdom allowing the user to view changes over time.

This card groups result-sets for Sample Time, Executions, Duration, CPU AVG, CPU MAX, IO AVG, and IO MAX in a searchable table format with sortable columns that can be grouped by Instance, Database, Object, and SQL Statement.

  • The Sample Time column displays the date and time the sample was collected, that sample represents the delta from that hour to the prior hour.
  • The Executions column displays the total number of times the query was executed that hour.
  • The Duration column shows the average execution duration for those executions.
  • The CPU AVG column displays the average CPU time for those executions.
  • The CPU MAX column displays the CPU time for the query that had the highest time among those executions.
  • The IO AVG column displays the average number of IO operations for those executions.
  • The IO MAX column displays the number of IO operations for the query that had the highest IO among those executions.

SQL Statement

This card displays the full SQL Statement with formatting and line numbers and has a Copy to Clipboard widget in the top-right corner.