Documentation forSQL Sentry

SQL Sentry Portal Top SQL

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 by default. If you're using the SQL SentryPortal feature for SQL Sentry, this default value may be changed and is controlled by the Monitoring Service Settings.

Completed Queries

The Completed Queries tab lists all recorded completed queries matching the current filters across the selected time period. The Completed Queries tab is displayed on the Top SQL tab by default. There are two modes for this pane: Totals and Trace Events.

The Totals table lists all events for the active date range in a standard list format, sorted descending by Reads by default. The Totals table is activated whenever auto-refresh is enabled by selecting Play on the toolbar, which shows the events for the last hour with new events coming in automatically at the top of the list.

The Completed Queries tab also displays any recorded Statements, Query History, or Plan Diagram information for recorded Completed Queries.

he Completed Queries table displays the Average Duration for all events, and the percentage of change for all events compared to the last recorded query execution. In the % Change column, a red arrow indicates that the query took longer to execute for the duration compared to the last execution, and a green arrow indicates that the query executed quicker compared to the last execution.

Proc/Query stats

Click Proc/Query Stats to display the Proc/Query Stats tab. The Proc/Query Stats tab provides aggregate information for all procedures and queries matching the current filters. This area also provides insight into queries that run very quickly and in high volume.

The Proc/Query stats tab displays a Totals table and Statements table for all recorded Proc/Query stats. The Proc/Query Stats tab also displays any Query History, or Plan Diagram information for the Proc/Query Stats.

Available Charts

Click the Proc/Query Stats tab to view detailed charts for the selected time period. The Queries chart displays by default. You can select any of the following chart options from the filter menu.

  • Queries ›
  • App ›
  • DB ›
  • Host ›
  • Login ›
  • Resources

Further filter your charts by data type. The Data Type drop-down menu has the following options.

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

Queries charts

Click Queries from the filters menu to view Queries charts. Click the Data Type drop-down to select any of the available chart types.

  • CPU
  • Duration
  • Exec Count
  • Reads (L)
  • Reads (P)
  • Writes (L)

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

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

App charts

Click App from the filters menu to view App charts. Click the Data Type drop-down to select any of the available chart types.

  • CPU
  • Duration
  • Exec Count
  • Reads (L)
  • Writes (L)
  • Waits 

The information displayed here is from trace data.

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

DB charts

Click DB from the filters menu to view Database charts. Click the Data Type drop-down to select any of the available chart types.

  • CPU
  • Duration
  • Exec Count
  • Reads (L)
  • Reads (P)
  • Waits

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

Host charts

Click Host from the filters menu to view Host charts. Click the Data Type drop-down to select any of the available chart types.

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

The information displayed here is from trace data.

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

Login charts

Click Login from the filters menu to view Login charts. Click the Data Type drop-down to select any of the available chart types.

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

The information displayed here is from trace data.

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

Resources charts

Click Resources from the filters menu to view Resources charts. View resource usage from Top SQL, based on query and procedure stats:

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

Totals

The Totals table displays by default in both the Completed Queries and Proc/Query Stats tabs. The Totalstable 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). This groups all like events together using a normalized version of the T-SQL with all variable parameters replaced, and it shows aggregates for each group. This enables you to quickly determine which events are responsible for the most CPU or IO activity for the active date range.

Note:  By default, the Totals table displays the top 15 queries by logical reads (descending ▼. For all tables, the arrow with the circle around it highlights by which column the data is sorted; ascending ▲ or descending ▼.

Click a recorded event to display all of the Trace Events for your selection.

Totals Filtering

Click the filter button to filter by a specific database. Enter the database name in the Value field, and then click + Add filter to apply your filter.

Statements

Portal Top SQL tab Statements chart displaying additional information about Totals including plan diagrams, text data, parameters, and plan XML.

The Statements table displays additional information about Completed Queries or Proc/Query Stats where applicable, including plan diagrams, text data, parameters, and plan XML. The Statements table displays the statements that were collected with the associated completed query, procedure stats or query stats. It provides details on the key performance metrics that are associated with each component part of the larger query. This allows you to see which resources were consumed by which part of the batch.

Trace Events

Click Traces on the Completed Queries tab to flip all totals and statements tables to the detailed Trace Events and Trace Events Statements tables and display details for any collected Trace Events such as RPC:Completed.

Trace Event Waits

Select a Wait (ms) link from the Totals, or Traces tab to open the Trace Event Waits chart for your selection.

The Trace Event Waits chart displays detailed information about the recorded Trace Event Wait including the text data, duration, waits (ms), reads, and writes.

Trace Event Filtering

Click the filter button to apply a filter. Select the desired column, operator, and enter a filter value. Click + Add Filter to apply the filter.

Note:  You can filter by the Event Class, Host, Application Name, Database Name, Login, CPU, Reads, Writes, Error, and SPID categories.

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.

Note:  The triangle colors represent execution plans. Triangles of the same color are using the same plan. If there is excessive plan drift (beyond 25 plans), then the 25 colors will start to be reused in the same order. Select a specific event to the Plan # represented.

Query Stats Sample

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

Proc Stats Sample

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

Note:  The Mode for the Proc Stats Sample is set to Procedure.

Additional Options

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

  • Grouping

    • Ungrouped
    • 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).

Additional Information:

Plan Diagram

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

Note: 

  • Sometimes the plan XML may be populated, but there's not a statement that can be matched for the plan diagram. This can be caused by things like nested procedures or individual statements falling outside of the collection thresholds on their own. In this case, the plan diagram will display a message such as "The selected statement was not found in the plan XML. Download the full plan to view in Plan Explorer" and provide a Download Plan XML button.

  • If the Query History chart shows a disabled point (i.e. a gray triangle), then the plan diagram will display a message to indicate that there are no plans available (e.g. "There is no data to display").

Text Data

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

Parameters

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

Plan XML

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

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

AI Query Assist

AI Query Assist leverages SolarWinds AI to provide query rewrite suggestions with the goal of optimizing query performance. Before submitting an optimization request, the SQL text and plan are sanitized to mask potential personally identifiable information (PII). Artificial intelligence (AI) is an emerging technology, so treat the results as suggestions and review them carefully before implementing.

SolarWinds AI does not use customer data to train AI Query Assist.

You can use AI Query Assist to optimize queries with a recorded explain plan. For information about activating AI Query Assist, see Platform Connect AI Query Assist.

Optimization is not supported for DDL, exec statements, and SQL with no detected plan.

AI Query assist limitations

The AI Query assist tech preview is available for queries with a recorded explain plan. You can optimize up to four queries per monitored instance within a 24 hour period. After an applicable query has been optimized, you cannot get another AI Query assist optimization for that query for 15 days.

Request an AI query optimization

To get a query optimization for an applicable query, complete the following.

  1. Select a query that can be optimized from the completed queries tab.

    The AI Query Assist column will display a sparkle symbol for queries that can be optimized.

    Queries that have received an optimization are designated with a number noting the number of times the query was optimized.

  2. Select the AI Query Assist tab, and then click Optimize SQL.

  3. Once the AI Query Assist finishes its optimization, the results are displayed in the AI Query Assist tab. The Side by Side comparison of the original SQL and Optimized SQL displays by default.

Review your AI optimized query

After AI Query Assist completes its optimization, the pane will update with the query suggestion and accompanying explanations. The AI Query Assist optimization is separated into four sections, Side by Side comparison, Thinking, Explanation, and Summary. See below for more information about each section.

Side by Side comparison

The Side by Side comparison is displayed first after requesting to optimize your query. The left side contains the original SQL, and the right side displays the AI Query Assist Optimized SQL. Click the copy button to copy the Original SQL or the Optimized SQL.

Thinking

Click Thinking to display the rationale behind the changes with the optimized query. The rationale may display as a paragraph, or can display as key points in a numbered list.

Explanation

Click Explanation to display more in depth details about why the changes to your query where necessary for the optimization. The explanation displays each change to your query in a sequential list.

Summary

Click Summary to display a comprehensive summary of the suggested changes from the query optimization.

Optimize a query that has already been optimized

You can submit another optimization request for a query that has already received after 15 days from the last optimization date. Queries that have received an optimization are designated with a number noting the number of times the query was optimized in the AI Query Assist column for Completed Queries.

  1. Click the query you want to re-optimize.

  2. Select the AI Query Assist tab, and then click New Optimization.

  3. Once the AI Query Assist finishes its optimization, the results are displayed in the AI Query Assist tab.

  4. Click the Optimization History drop-down and select the desired date to review your previous optimization.