Performance Analysis Top SQL
Introduction
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.
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.
Open 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.
Execution Plan Diagram
The Plan Diagram displays a graphical plan preview of the desired query in your environment.
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.
Note: Hovering over any of the dots on the Query History graph provides you with additional information.
Note: Right click any dot on the Query History graph, and select Open Plan from the context menu to open the Plan Explorer diagram.
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
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.
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.
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.
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.
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.
Command Text
The Command Text tab shows the T-SQL text data for the currently highlighted Top SQL record with syntax highlighting.
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.
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.
Example One
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.
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).
Hiding Queries 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.
- 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.
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.
|
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. |
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). |