Performance Analysis Top SQL

Applies to: SQL Sentry, BI Sentry, and DB Sentry

SentryOne 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 SentryOne 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 SentryOne schema on the target database.

SentryOne 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.

SentryOne Top SQL tab Top section Grid view Expand a desired query to see more detailed information and metrics by selecting  by that query.

SentryOne Top SQL tab Top Section expanded queryOpen Plan Explorer for the expanded query by selecting Open.

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.

SentryOne Top SQL tab Statements Grid

Execution Plan Diagram

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

SentryOne 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.

SentryOne Top SQL Query History

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

SentryOne 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.

SentryOne 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

GroupingMetricMode
HourDurationStatement
DayCPUProcedure
WeekIO

Display Tabs

TabDescriptionImage
Filter

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

  • SQL Servers 
  • applications 
  • databases 
  • hosts

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.

SentryOne Top SQL Filter
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.

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.

SentryOne Top SQL tab Running Queries
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. 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:  The Completed Queries tab is not available in DB Sentry for Azure SQL Database.

Note:  SentryOne 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.

SentryOne Top SQL Completed Queries
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.

Note:  SentryOne 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.

SentryOne Top SQL tab Procedure Stats
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.

Note:  SentryOne 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.

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

Changing What's Collected for Top SQL

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

SentryOne 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 SentryOne, see the Alerting and Response System topic.

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).
    SentryOne Open Navigator pane and select All Targets
  2. In the Settings pane (View > Settings), select SQL Server from the top drop-down list.
    SentryOne 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.
    SentryOne 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.
    SentryOne 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).
    SentryOne open the Navigator pane and select desired instance 
  2. In the Settings pane (View > Settings), select SQL Server from the top drop-down list.
    SentryOne 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.
    SentryOne 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.
    SentryOne set Inherit From Parent setting to False in Settings pane
  5. Change the Minimum Duration to the desired value; it saves automatically.
    SentryOne 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 Writes Collection 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 SentryOne, and has a negative impact on the monitored server. 

Note:  SentryOne's Quick Trace 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 >SentryOne Monitoring Service Settings > Advanced tab > Query Collection Maximum Text Length).

SentryOne 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 Within Top SQL and Stopping Alerts 

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.
    SentryOne 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.
    SentryOne 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 SentryOne environment, not just your individual SentryOne 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.

SettingDescriptionImage
Query Stats Sample Interval
Specifies how often to sample query stats.
SentryOne Query Stats Sample Interval settings
Filter Time Span
Specifies the base length of time over which the collection filters are applied to Query Stats.
SentryOne Filter Time Span settings
Minimum Duration
Specifies the minimum duration for a statement to be logged .
SentryOne Minimum Duration settings
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.
SentryOne Filter Factor settings


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

ControlDescriptionImage
Filter buttonAdd 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.
SentryOne Top SQL tab Filter button

Toolbar Options

ControlDescriptionImage
Show Hidden Rows

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

SentryOne Top SQL toolbar Show Hidden Rows
Show TotalsGroups like queries together by normalizing the result set.SentryOne Top SQL tab Show Totals toolbar button

Context Menu Options

OptionDescriptionContext Menu ImageOption Applied
Sort AscendingOrganizes the selected column in Ascending order.SentryOne Top SQL Sort Ascending context menu optionSentryOne Top SQL Sort Ascending
Sort DescendingOrganizes the selected column in Descending order.SentryOne Top SQL Sort Descending context menu optionSentryOne Top SQL Sort Descending
Clear SortingClears the sorting options applied to the column.SentryOne Top SQL Clear Sorting context menu optionSentryOne Top SQL Clear Sorting
Group By This ColumnGroups the queries by the selected column.SentryOne Top SQL Group By This Column context menu optionSentryOne Top SQL Group By This Column
Show Group By Box/ Hide Group By Box
Displays or Hides the Group by box.SentryOne Top SQL Show Group By Box context menu option SentryOne Top SQL Show Group By Box
Hide This ColumnHides the selected column.SentryOne Top SQL Hide This Column context menu option SentryOne Top SQL Hide This Column
Column ChooserOpens the Column Chooser. The Column Chooser provides additional columns to sort information. SentryOne Top SQL Column Chooser context menu option SentryOne Top SQL Column Chooser
Best FitResizes the information for the selected column.SentryOne Top SQL Best Fit context menu option SentryOne Top SQL Best Fit
Best Fit All ColumnsResizes the information for all of the columns.SentryOne Top SQL Best Fit (all columns) context menu optionSentryOne Top SQL Best Fit (all columns)
Filter EditorOpens 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.

SentryOne Top SQL Filter Editor context menu optionSentryOne Top SQL Filter Editor
Show Find PanelDisplays the Find Panel. Use the Find Panel to search for specific strings in the queries.SentryOne Top SQL Show Find Panel context menu optionSentryOne Top SQL Show Find Panel
Show Auto Filter RowDisplays the Auto Filter Row. Use the Auto Filter Row to SentryOne Top SQL Show Auto Filter Row context menu optionSentryOne Top SQL Show Auto Filter Row
Reset GridResets the data on the grid to the default setting.SentryOne Top SQL Reset Grid context menu optionSentryOne Top SQL Reset Grid


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 optionDescriptionTop SQL Jump To optionCorresponding data
Jump to DashboardWhen 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.SentryOne Top SQL Jump to DashboardSentryOne Top SQL corresponding Dashboard
Jump to CalendarGoes to the selected event on the Event Calendar (requires the server to be watched with EM).SentryOne Top SQL Jump to CalendarSentryOne Top SQL corresponding Event Calendar
Jump to Runtime StatsOpens a tab showing the Runtime Stats for the event.SentryOne Top SQL Jump to Runtime StatsSentryOne Top SQL corresponding Runtime Stats

Additional Options

Option DescriptionImage
Kill Process

Kills the running process (Running Queries tab).


SentryOne Top SQL Running Queries Kill Process
Trace ProcedureOpens the Run Quick Trace dialog box for the selected event (Query Stats tab).SentryOne Top SQL Query Stats Trace Procedure

Top SQL Metrics

NameDescription
ServerThe SQL Server instance where the associated event took place.
PlanSelect View to open a new Plan Explorer session for the captured plan.
Text DataAssociated text data captured for the event.
Event ClassThe event class of the associated event. For more information, see the SQL Server Event Class Reference MSDN article.
 HostName of the target where the associated event is running.
LoginThe Windows or SQL Server account associated with the event.
ApplicationName of the client application that created the connection.
DatabaseName of the database for the associated event.
DurationThe amount of time taken by the associated event.
CPUThe amount of CPU time (in milliseconds) used by the event.
CPU %The percentage of CPU time used by this event, in relation to any other Top SQL events that are shown in the grid.
ReadsThe number of logical disk reads performed by the server on behalf of the event.
Reads %The percentage of bytes read by this event, in relation to any other events that are shown in the Top SQL Grid view.
WritesThe number of physical disk writes performed by the server on behalf of the event.
Writes %The percentage of bytes written by this event, in relation to any other events that are shown in the Top SQL Grid view.
Start TimeTime the event started.
End TimeTime the event ended.
InformationAdditional data captured about the event.
ErrorError number of a given event.
SPIDThe server process ID (SPID) that is assigned to the process.
Host Process IDThe ID assigned by the host computer to the process where the client application is running.
Has PlanTrue if the associated event has a captured plan.
Has StatementsTrue if the associated event has captured statements
VisibleSpecifies the default visibility for the select row. Queries may be hidden in Top SQL.

Display Tabs

Filter

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

  • SQL Servers 
  • applications 
  • databases 
  • hosts

SentryOne 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.

SentryOne 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. 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.

SentryOne Top SQL Completed Queries tab

Note:  The Completed Queries tab is not available in DB Sentry for Azure SQL Database.

Note:  SentryOne 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.

SentryOne Top SQL Procedure Stats tab

Note:  SentryOne 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.

SentryOne Top SQL Query Stats tab

Note:  SentryOne 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.

SentryOne Top SQL Command Text

Top SQL Runtime Stats

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

Runtime Graphs

Runtime Stats provides a visualization of collected runtimes for a particular statement. Open Runtime Stats by right-clicking any statement in the Completed Queries tab and then selecting  Jump To > Runtime Stats from the context menu.

SentryOne Top SQL Jump to Runtime Stats
Jump to > Runtime Stats
SentryOne Top SQL corresponding Runtime Stats
Top SQL Runtime Stats

There are two views available for the graph, Runtime History and Runtime Aggregates.  This selection is made from the main toolbar. These graphs make it easy to identify runtime trends, which are an indicator of or eventually lead to performance issues with the event or the server.

Runtime History Graph

This graph shows an entry's actual runtime and status over the specified interval. A red bar indicates that the instance failed.

Runtime Aggregates Graph

This graphs show the distribution of the entry’s actual runtime over the specified interval.

SentryOne Top SQL Runtime Stats History mode
Top SQL Runtime Stats History mode
SentryOne Top SQL Runtime Stats Aggregate mode
Top SQL Runtime Stats Aggregate mode

Graph Controls

Use the Navigation toolbar to change the Start date/time and interval for the graph.

SentryOne Top SQL tab Runtime Stats Navigation toolbar The Days control can go up to 60 days at a time. Whenever the selected interval is less than one day (one minute through four hours), the Days control changes to one day.

Graph ControlDescription

Min/Max Y Val

When a runtime graph is first displayed, SentryOne tries to determine the most appropriate interval and maximum Y-axis value. Change either of these settings at any time.

Auto-scale to Max Value

Checking this box automatically selects the appropriate Max Y Value of the data being shown in the current view.

Interval Type

Changes the interval used for the Y-axis.

Show Value Labels

Deselect this checkbox to hide the bar value labels. This can become necessary for broader ranges with hundreds or thousands of bars to reduce the noise on the graph.

Runtime Grid

The Runtime Grid provides additional information about captured Top SQL events. This includes both events that are captured as part of the standard Top SQL collection, and fast running events that are captured as part of Query Stats collection. The events listed in the Runtime Grid aren't dependent on any specific time range you're viewing, and don't update based on the selected chart view. The count represents the total number of observed occurrences of the event that have history in SentryOne. The respective Min, Max, and Average durations are updated as new occurrences of the events are observed in your environment.

SentryOne Top SQL Runtime Grid

Events are purged from the list according to the SentryOne Monitoring Service Settings > Keep Performance History For setting. See the last time an event occurred by using the Last Execution column. If an event hasn't been observed since the effective Keep Performance History For date, its record is removed.


SentryOne Runtime Grid Last Execution