Monitor Top SQL

Applies to the following products and features: The SentryOne Monitor product and the on-premises SentryOne Portal feature for SQL Sentry.

Introduction

The Top SQL view displays a unified picture of collected SQL statements. It's designed to help you quickly identify queries, applications, logins, and more that are causing the most waits, using the most resources, taking the most time, and putting the most load on your SQL Server.

Note:  Top SQL data is retained for 15 days in SentryOne Monitor. If you're using the SentryOne Portal feature for SQL Sentry, this default value may be changed and is controlled by the Monitoring Service Settings.

Available Charts

The full viewing options for this card's charts are:

Select the button in the upper right for additional options such as reset, show/hide axis labels, and show/hide axes.

Show/hide axes has the following options:

  • Avg Duration (ms)
  • CPU Time (ms)
  • Exec Count
  • Reads Logical
  • Writes Logical
  • Reads Physical

On the options with the (chevron-right), additional choices similar to the above are available by selecting the symbol. For example, By App - Duration (ms) and Queries - Reads (P) are available chart options.

Waits

The first card in Top SQL defaults to a Waits view. Waits displayed here are from the SQL Server instance level. For a better understanding of waits, see the SQL Server Waits Stats section of the Dashboards article and this blog post What to do (or not do) about top wait stats.

SentryOne Monitor Top SQL Waits view

Hover over an area on the chart to view additional details about the waits:

SentryOne Monitor Top SQL Waits view tooltip

Resources

View resource usage from Top SQL, based on query and procedure stats:

SentryOne Monitor Top SQL Resources view

Hover over a point on the chart to view additional details about the resources being used:

SentryOne Monitor Top SQL Resources tooltip

Queries

Select the (chevron-right) to view charts for:

  • CPU
  • Duration
  • Exec Count
  • Reads (L) - The default selection
  • Reads (P)
  • Writes (L)

The information displayed here is from query stats, procedure stats, and trace data.

SentryOne Monitor Top SQL Queries view

Hover over an area in the chart to view more details about the queries:

SentryOne Monitor Top SQL Queries tooltip

By App

Select the (chevron-right) to view charts for:

  • CPU
  • Duration
  • Exec Count
  • Reads (L) - The default selection
  • Writes (L)

The information displayed here is from trace data.

SentryOne Monitor Top SQL By App view

Hover over an area on the chart to view more details about the applications:

SentryOne Monitor Top SQL By App tooltip

By DB

Select the (chevron-right) to view charts for:

  • CPU
  • Duration
  • Exec Count
  • Reads (L) - The default selection
  • Reads (P)

SentryOne Monitor Top SQL By DB

Hover over an area on the chart to view more details about the databases:

SentryOne Monitor Top SQL By DB tooltip

By Host

Select the (chevron-right) to view charts for:

  • CPU
  • Duration
  • Exec Count
  • Reads (L) - The default selection
  • Writes (P)

The information displayed here is from trace data.

SentryOne Monitor Top SQL By Host

Hover over an area on the chart to view more details about hosts:

SentryOne Monitor Top SQL By Host tooltip

By Login

Select the (chevron-right) to view charts for:

  • CPU
  • Duration
  • Exec Count
  • Reads (L) - The default selection
  • Writes (P)

The information displayed here is from trace data.

SentryOne Monitor Top SQL By Login

Hover over an area on the chart to view more details about logins:

SentryOne Monitor Top SQL By Login tooltip

Totals

The Totals table displays the text data and associated information such as database, duration, count, and CPU for the collected SQL statements (including procedure stats, query stats, and completed queries).

SentryOne Monitor Top SQL Totals grid

Note:  By default, the Totals grid displays the top 8 queries by logical reads (descending ). For all grids, the arrow with the circle around it highlights by which column the data is sorted; ascending or descending .

Trace Events

Select the   (chevron-right) under the Events column to display details for any collected Trace Events such as RPC:Completed.

SentryOne Monitor Top SQL Trace Events grid

Note:  In the upper right of the Top SQL page, there is a details switch. The switch is off by default. Select the switch to turn on details (SentryOne Monitor Top SQL details switch), which flips all totals and statements tables to the detailed Trace Events and Trace Events Statements view on the page.

Statements

SentryOne Monitor Top SQL Statments

The Statements card displays additional information about Totals where applicable, including plan diagrams, text data, parameters, and plan XML.

Trace Events Statements

Select the (chevron-right) under the Events column to display statement details to any collected Trace Events such as SP:StmtCompleted.

SentryOne Monitor Top SQL Trace Events grid

Plan Diagram

Use the full screen button in the upper left to expand a larger plan, or use the download button on the lower right to download the entire .sqlplan file.

SentryOne Monitor Top SQL Plan Diagram download plan

Text Data

Use the Text Data tab to view a formatted and syntax color-coded copy of the statement.

SentryOne Monitor Top SQL Text Data

Parameters

Use the Parameters tab to view compiled values for statement parameters.

SentryOne Monitor Top SQL Parameters

Plan XML

Use the Plan XML tab to view or copy the ShowPlanXML output.

SentryOne Monitor Top SQL Plan XML

Note:  The Copy button is only available when you are using HTTPS (requires an SSL certificate for your SentryOne Portal installation).

Query History

Select a query or statement, then view the Query History for it. Query History displays a graphical representation of the selected query over a specified range of time. Query History provides information about the query execution plans, if and when they were changed, and how they impacted different resources. 

Query Event

Each triangle represents a Query Event.

SentryOne Monitor Top SQL Query History chart query events

Query Stats Sample

Each dot represents a Query Stats Sample or a Proc Stats Sample.

SentryOne Monitor Top SQL Query History chart query stats sample

Proc Stats Sample

Each dot represents a Proc Stats Sample or a Query Stats Sample.

SentryOne Monitor Top SQL Query History chart proc stats sample

Additional Options

Use the options below the chart to adjust the Grouping, Show, Metric, Mode, or Dates slider window.

  • Grouping
    • None
    • Hour
    • Day
    • Week
  • Show
    • Actual/Average
    • Totals
  • Metric
    • Duration
    • CPU
    • IO
  • Mode
    • Procedure
    • Statement

Note:  In Procedure mode, the chart reflects changes in the procedure stats (plan_handle), whereas Statement mode displays the changes in query stats (query_plan_hash).

Example with Grouping by Hour, Show Totals, Metric CPU, and Mode Statement:

SentryOne Monitor Top SQL Query History chart grouping

Additional Information: