Documentation forSQL Sentry

Performance Analysis Top SQL

Applies to: SQL Server, SSAS, Azure SQL Database, and AWS RDS target types.
SQL Sentry Portal: This feature is available in SQL Sentry Portal. To learn more about configuring your environment to use the on-premises, browser-based option with your existing SQL Sentry database, see the SQL Sentry Portal article.
See the SQL Sentry Portal Top SQL article for an example of the Top SQL feature in a browser.

Introduction

Top SQL tab

The Top SQL tab lists all T-SQL batches, stored procedures (RPCs), and statement events collected for the current date range that exceeded the Minimum Duration specified in the Settings pane. The default filter is Duration, but filtering using CPU and IO is also available. The default minimum duration is five seconds, but this can be adjusted up or down as needed through the Top SQL Source settings.

To collect Top SQL metrics for Azure SQL database targets the Allow SQL Sentry Monitoring Objects in Target setting under the Azure SQL Database Connection settings must be set to True. This option creates a few objects within a SQL Sentry schema on the target database. See the Watched Target Objects article for details.

Azure SQL Database settings

Top Section Grid View

The Top section Grid View displays queries in your environment. View Running Queries, Completed Queries, Procedure Stats, or Query Stats by selecting the corresponding tab.

Top SQL tab Top section Grid view

Open Plan Explorer for the expanded query by selecting Open.Top SQL tab Open Query

Statement Grid

The Statements Grid 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.

Top SQL tab Statements Grid

Execution Plan Diagram

The Plan Diagram displays a graphical plan preview of the desired query in your environment.

Top SQL tab Plan Diagram

Select Open with Plan Explorer to open the integrated Plan Explorer view for the selected execution plan. For more information about Plan Explorer, see the Plan Explorer topic.

Query History

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. Each dot represents when the query was executed. Different colors on the dots indicate the different plans, and the plans are numbered. Adjust the time displayed on the graph with the Range slider.

Top SQL Query History

Note:  Hovering over any of the dots on the Query History graph provides you with additional information.

Top SQL tab Query History tooltips

Note:  Right click any dot on the Query History graph, and select Open Plan from the context menu to open the Plan Explorer diagram.

Top SQL tab Query History Open Plan

Additionally, adjust the graph to identify historical procedure and statement executions through CPU, IO, or Duration metrics. 

Query History Graph visualization options

Grouping Metric Mode
Hour Duration Statement
Day CPU Procedure
Week IO

Display Tabs

Filter

The Filter tab provides the following filters for controlling which records display: 

  • SQL Servers 
  • Applications 
  • Databases 
  • Hosts

Top SQL Filter tab

Select any combination of items to set the filters. To select more than one item, use Shift-Click or CTRL-Click. View the filtered results by selecting Refresh or F5.

Show Top Control- Controls how many rows are retrieved.

Note:  The Filter tab isn't displayed on an Azure SQL Database target.

Running Queries

The Running Queries tab shows all running queries matching the current filters and is independent of the time range setting. The View button in the Plan column opens a Plan Explorer session for the associated query. The Text Data column shows the original text the client sent to the server and the Active Statement column shows the currently running statement.

Top SQL Running Queries tab

Note:  Running queries isn't supported for SQL Server 2000 or Azure SQL Database.
Note:  The text data for the active statement is displayed in the Text Data pane when the Active Statement cell is selected.

Completed Queries

The Completed Queries tab lists all records matching the current filters. The View button in the Plan column opens a Plan Explorer session for the associated query.  The default filter displays all records. There are two modes for this pane: Default and Totals.

Default mode lists all events for the active date range in a standard list format, sorted descending by End Time by default.  Default mode is activated whenever auto-refresh is enabled by selecting Play on the toolbar, which shows the events for the last 10 minutes with new events coming in automatically at the top of the list.

Totals mode is enabled by selecting Show Totals (the sigma Σ button). 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.

Top SQL Completed Queries tab

Note:  SQL Sentry 8.2 introduces enhanced SQL text data normalization. If upgrading from a build prior to 8.2, enhanced normalization must be activated in the Monitoring Service Settings > Performance Monitor tab.

Procedure Stats

The Procedure Stats tab provides aggregate information for all procedures matching the current filters. The View Plan button in the Plan column opens a Plan Explorer session for the associated procedure. Additional historical information may be available in the Runtime Stats. Open Runtime Stats by right clicking on a procedure and selecting Jump To > Runtime Stats.

Top SQL Procedure Stats tab

Note:  SQL Sentry installations upgraded from a version older than 8.2 need to enable the Collect Query Stats option in the Top SQL Source setting.
Note:  Procedure Stats isn't available for SQL Server 2000 or SQL Server 2005.

Query Stats

The Query Stats tab provides aggregate information for queries matching the current filter. This area also provides insight into queries that run very quickly and in high volume.

Top SQL Query Stats tab

Note:  SQL Sentry installations upgraded from a version older than 8.2 need to enable the Collect Query Stats option in the Top SQL Source setting.
Note:  Query Stats isn't available for SQL Server 2000 or SQL Server 2005.

Command Text

The Command Text tab shows the T-SQL text data for the currently highlighted Top SQL record with syntax highlighting.

Top SQL Command Text

Top SQL Runtime Stats

The Top SQLRuntime Stats option provides insight into the frequency and duration of the queries and procedures that are captured by Top SQL

Additional Information: See the Top SQL Runtime Stats article.

Top SQL Metrics

Default Metrics

Name Description
Plan Select Open to open a new Plan Explorer session for the captured query plan.
Host Name of the target where the associated captured query originated.
Event Class The event class of the associated captured query.

Additional Information: For more information, see the SQL Server Event Class Reference MSDN article.
Text Data Associated text data captured for the query.
Login The Windows or SQL Server account associated with the captured query.
Duration The amount of time taken by the captured query.
CPU The amount of CPU time (in milliseconds) used by the captured query.
CPU % The percentage of CPU time used by the captured query, in relation to any other Top SQL events that are shown in the grid.
Reads The number of logical reads from the cache performed by the server on behalf of the captured query.
Reads % The percentage of bytes read by the captured query, in relation to any other events that are shown in the Top SQLGrid view.
Writes The number of physical disk writes performed by the server on behalf of the captured query.
Writes % The percentage of bytes written by the captured query, in relation to any other events that are shown in the Top SQLGrid view.
Start Time Time the captured query started.
End Time Time the captured query ended.
Information Additional data captured about the query.
Error Error number of a given captured query.
SPID The server process ID (SPID) that is assigned to the process.
Host Process ID The ID assigned by the host computer to the process where the client application is running.
Server The SQL Server instance where the associated captured query took place.

Additional Metrics

The following metrics do not appear by default, but can be added through the column chooser. See the Data Grids article for additional information on choosing columns.

Name Description
Application Name of the client application that created the connection.
Database Name of the database for the associated captured query.
Granted Memory (KB) The amount of memory in KB granted to the captured query.
Granted Query Memory (KB) The amount of memory in KB granted to the query.
Has Plan True if the associated query has a captured plan.
Has Statements True if the associated query has captured statements.
Ideal Memory (KB)
Is incomplete True if the associated query is incomplete.
Parent ID The ID assigned to the Parent query.
Requested Memory (KB) The amount of memory in KB requested by the captured query.
Session Memory (KB) The amount of memory in KB used during the session.
Tempdb Internal (KB) The amount of memory in KB used by tempdb internal objects.
Tempdb Internal (KB) Dealloc The amount of memory in KB that is deallocated for tempdb internal objects.
Tempdb User (KB) The amount of memory in KB that is used by User objects.
Tempdb User (KB) Dealloc The amount of memory in KB that is deallocated for User objects.
Visible Specifies the default visibility for the select row. Queries may be hidden in Top SQL.

Changing What's Collected for Top SQL

Change the Top SQL that's collected by SQL Sentry through the Top SQL Source settings. Change the Top SQL events collected by SQL Sentry through the Top SQL Source settings at these levels:All Targets (Global), site, target group, target, and instance.

Top SQL Source settings

Note:  As a reminder, all settings have a hierarchical configuration, and work through the principle of inheritance. Any changes you make at one level is automatically inherited by all objects below it. For more information about inheritance within SQL Sentry, see the Alerting and Response System topic.
Note:  Amazon RDS supports extended events on standard and enterprise editions. Starting with Version 2023.3, you can update your collection preference using the Trace Engine option in Top SQL Source settings to choose between the default Legacy (rowset) collection and Extended Events (XE) collection.

Example One

To configure the Top SQL Minimum Duration Collection setting globally complete the following steps:

  1. Select the All Targets node in the Navigator pane (View > Navigator).
    Open Navigator pane and select All Targets
  2. In the Settings pane (View > Settings), select SQL Server from the top drop-down list.
    Select SQL Server from the top drop-down list in the Settings pane
  3. Select Top SQL Source from the second drop down list to open the Top SQL Source settings that are being applied globally.
    Select Top SQL Source from the second drop-down list in the Settings pane
  4. Change the Minimum Duration to the desired value; it saves automatically.
    Set Minimum Duration settings globally in the Settings pane

Example Two

To configure the  Top SQL Minimum Duration Collection setting for an individual instance complete the following steps:

  1. Select the desired instance node in the Navigator pane (View > Navigator).
    Open the Navigator pane and select desired instance 
  2. In the Settings pane (View > Settings), select SQL Server from the top drop-down list.
    Select SQL Server from the top drop-down list in the Settings pane
  3. Select Top SQL Source from the bottom drop-down list to open the Top SQL Source settings that are configured for the instance.
    Select Top SQL Source from the second drop-down list in the Settings pane
  4. Within the Collection Settings section change the Inherit From Parent setting to False.
    Set Inherit From Parent setting to False in Settings pane
  5. Change the Minimum Duration to the desired value; it saves automatically.
    Set Minimum Duration setting in Settings pane

There are a few additional things you should know regarding the Top SQL Source settings.

There is an AND relationship that exists between the Minimum Duration, Minimum CPU, Minimum Reads, and the Minimum WritesCollection Settings, meaning that to be collected as Top SQL, the event needs to satisfy each individual Collection Setting. For example, if you set the Minimum Duration at 10 seconds and the Minimum Reads at 25, an event needs to meet both a Minimum Duration of 10 seconds AND a Minimum Reads of 25 to be captured in Top SQL.

Minimum Duration can't be set below 100ms unless Minimum CPU, Minimum Reads, or Minimum Writes is greater than zero. This lower limit is enforced because setting this threshold below 100ms for an extended period of time dramatically increases the volume of data collected and stored by SQL Sentry, and has a negative impact on the monitored server. 

Note:  SQL Sentry's QuickTrace functionality is better suited to analyze extremely short duration events.

Adjusting Captured TextData Length

By default textdata for captured Top SQL and Top Command events is truncated at 10,000 characters. Change this maximum length in Global Settings (Navigator pane > Configuration > Global Settings > Advanced tab > Query Collection Maximum Text Length).

Set Query Collection Maximum Text Length

Note:  You may adjust the Query Collection Maximum Text Length setting up to 50,000 characters. The default setting of 10,000 characters is adequate for most SQL Server environments. 

Hiding Queries and Stopping Alerts

Note:  Hiding a query in Top SQL also hides it from the calendar view.

Hide individual queries or groups of queries with the right-click context menu Hide command.

  • To hide a single query, select the desired row, then right-click and select Hide. Top SQL tab Hide a query
  • To hide a group of queries, drag a column header to the top of the Grid View to group like items. Select the group header in the Grid View and use the right-click context menu Hide command. Top SQL tab Hide This Column
Important:  Once a query is hidden in Top SQL, you're no longer alerted about it for any of the Top SQL related conditions. Unhide all hidden queries by selecting Show Hidden on the toolbar. To unhide an individual query permanently, select the Visible checkbox from the Column Chooser that toggles the effect of Hide/Hide Group.

It's important to note that this will impact everyone using this particular SQL Sentry environment, not just your individual SQL Sentry client view.

Top SQL Source Settings

There are three settings that can be adjusted to change how long a query runs to be collected. These settings are in the Top SQL Source area in the Settings pane.

Setting Description
Query Stats Sample Interval Specifies how often to sample query stats.
Filter Time Span Specifies the base length of time over which the collection filters are applied to Query Stats.
Minimum Duration Specifies the minimum duration for a statement to be logged.
Filter Factor The Filter Factor is calculated by dividing the Query Stats Sample Interval by the Filter Time Span. The collection filters such as Minimum Duration are multiplied by this value when applied to Query Stats collection.

Note:  Each of these settings play a part in what's captured for Query Stats in your environment. As an example, using the following default settings:

  • Query Stats Sample Interval = 60 seconds
  • Filter Time Span = 30 seconds
  • Minimum Duration = five seconds

The total cumulative duration of all executions of a query over the 60 second sample interval must be greater than 10 seconds.

Controls

Top Pane

Control Description
Filter button Add a filter to any column by selecting the filter button. After changing any of the filters, it's necessary to select Refresh, or press F5 to apply the filter.
  • Select on a combination of items in any of the lists to set the filters. The default filter displays all records.
  • To select more than one item in a list, use Shift-Click or CTRL-Click.

Toolbar Options

Control Description
Show Hidden Rows

Hide individual lines or groups of rows. This toggles the display of those lines.

Show Totals Groups like queries together by normalizing the result set.

Context Menu Options

Option Description
Sort Ascending Organizes the selected column in Ascending order.
Sort Descending Organizes the selected column in Descending order.
Clear Sorting Clears the sorting options applied to the column.
Group By This Column Groups the queries by the selected column.
Show Group By Box/ Hide Group By Box Displays or Hides the Group by box.
Hide This Column Hides the selected column.
Column Chooser Opens the Column Chooser. The Column Chooser provides additional columns to sort information. 
Best Fit Resizes the information for the selected column.
Best Fit All Columns Resizes the information for all of the columns.
Filter Editor Opens the Filter Editor

Use the Filter Editor to add and remove filters, and apply Boolean logic for a more complex filter. To adjust the Boolean logic, simply select the red And to bring up the context window.

Show Find Panel Displays the Find Panel. Use the Find Panel to search for specific strings in the queries.
Show Auto Filter Row Displays the Auto Filter Row. Use the Auto Filter Row to 
Reset Grid Resets the data on the grid to the default setting.
Important:  Hide/Hide Group disables any alerts for the events, and prevents them from being displayed on the Event Calendar.

Jump to

Jump To context menu option Description
Jump to Dashboard When Show Totals mode is enabled all events belonging to the selected group are overlaid on the Dashboard. When the Default mode is enabled only the selected event is overlaid on the Dashboard.
Jump to Calendar Goes to the selected event on the Event Calendar (requires the server to be watched with EM).
Jump to Runtime Stats Opens a tab showing the Runtime Stats for the event.

Additional Options

Option  Description
Kill Process

Kills the running process (Running Queries tab).

Trace Procedure Opens the Run QuickTrace dialog box for the selected event (Query Stats tab).