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.
Available Charts
The full viewing options for this card's charts are:
- Waits
- Resources
- Queries ›
- By App ›
- By DB ›
- By Host ›
- By Login ›
Select the ellipsis 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 SQL Sentry Portal Performance article and this blog post What to do (or not do) about top wait stats.
Hover over an area on the chart to view additional details about the waits:
Waits Categories
Starting with Version 2023.3, view detailed Wait information by the following categories below.
Waits By App
View Waits By App to see the applications that were contributing to the wait(s), and the wait durations in milliseconds (ms).
Waits By DB
View Waits By DB to identify the database where the wait(s) occurred and see the overall wait durations in milliseconds (ms).
Waits By Host
View Waits By Host to identify the host where the wait(s) occurred and see the overall wait durations in milliseconds.
Waits By Login
View Waits By Login to determine the login contributing to the wait(s) and see the overall wait durations in milliseconds (ms).
Resources
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:
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.
Hover over an area in the chart to view more details about the queries:
By App
Select the > (chevron-right) to view charts for:
- CPU
- Duration
- Exec Count
- Reads (L) - The default selection
- 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:
By DB
Select the > (chevron-right) to view charts for:
- CPU
- Duration
- Exec Count
- Reads (L) - The default selection
- Reads (P)
- Waits
Hover over an area on the chart to view more details about the databases:
By Host
Select the > (chevron-right) to view charts for:
- 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:
By Login
Select the > (chevron-right) to view charts for:
- 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:
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).
Starting with Version 2023.2, the Totals table contains the Waits (ms) metric for recorded Trace events. Select a statement in the Totals table to open the Trace Events table.
Totals Filtering
Use the search bar to further filter your Top SQL totals. Type your search parameters into the search bar and select enter to filter the Totals grid.
Select the filter button on the Database category to filter by individual databases.
Select the databases that you want to filter by, and then select Apply to filter the Totals chart by your selection.
Trace Events
Select Details ( ) above the Totals chart to flip all totals and statements tables to the detailed Trace Events and Trace Events Statements tables on the Top SQL page and display details for any collected Trace Events such as RPC:Completed.
Trace Event Waits
Select a Trace Event Wait (ms) link 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 Wait Type(s) Wait %, and duration in milliseconds (ms).
Trace Event Filtering
Select the filter button on the desired category to filter by that category. For example, select the filter button for the hosts category, select the desired hosts, then select Apply to apply the filter to the Trace Events grid.
Statements
The Statements card displays additional information about Totals where applicable, including plan diagrams, text data, parameters, and plan XML.
Trace Events Statements
Select Details ( ) above the Totals chart to flip all totals and statements tables to the detailed Trace Events and Trace Events Statements tables and display statement details to any collected Trace Events such as SP:StmtCompleted.
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.
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.
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.
Additional Options
Use the options below the chart to adjust the Grouping, Show, Metric, Mode, or Dates slider window.
- Grouping
- None
- Hour
- Day
- Week
- None
- Show
- Actual/Average
- Totals
- Actual/Average
- Metric
- Duration
- CPU
- IO
- Duration
- Mode
- Procedure
- Statement
- Procedure
Example with Grouping by Hour, ShowTotals, MetricCPU, and ModeStatement:
An additional example with multiple plans represented:
Additional Information:
- Multiple Plans for an "Identical" Query blog post by Aaron Bertrand on SQLPerformance
- Different Plans for "Identical" Servers blog post by Aaron Bertrand on SQL Performance
- Analyzing "death by a thousand cuts" workloads blog post by Erin Stellato on SQLPerformance
- How useful are query_hash and query_plan_hash for troubleshooting? blog post by Jonathan Kehayias on SQLskills