One of the most heavily utilized features in the SentryOne Monitoring Platform is Top SQL, a user interface that displays long-running and high-impact queries. The popularity of Top SQL shouldn’t be a surprise given the importance of monitoring and optimizing queries executing against a given SQL Server environment.
The Top SQL tab has a lot of different ways to view collected queries and search for executions—let’s look at how to navigate Top SQL and dive into several of its features.
Although it’s not the intent of this post to discuss Top SQL collection, I do need to highlight some important collection concepts, as I feel it’s crucial to truly understanding and using Top SQL.
First is Top SQL’s Minimum Duration threshold setting. This setting defines the minimum duration queries must run before SentryOne will collect them. By default, Minimum Duration is set to 5 seconds. This setting can be adjusted through Top SQL Source settings. (Select the desired level in Navigator Topology > Settings Pane > Top SQL Source > Minimum Duration.)
There are several approaches you can take to find a query or set of queries of interest. First, always be sure that you have identified a window of time in which the query or queries have executed and that execution durations were long enough to meet the Minimum Duration threshold.
At the very top of the Top SQL tab, there’s a Filter drop-down menu. Expanding the Filter menu will provide several options for filtering the result set of Completed Queries. Note that unlike other filtering options that will be discussed, the available values to filter on will be based on all collected queries, rather than just values available in the current result set. So, if you select a specific value to filter on and no results are visible, you likely need to adjust the window of time you’re viewing.
After selecting a desired value to filter on, make sure to hit the Refresh button located on the top toolbar. There’s also a Show Top setting, which enables you to set the number of results that will be returned in Completed Queries. Note that if you’re looking at a very large window of time, or have an extremely active instance, you might need to increase this value if you can’t find events you would expect to see. That said, keep in mind that when sorting by a specific column, it will show the top results based on that column and sort order. So, if you sort by CPU descending, you would not miss high CPU offenders. The max value for this setting is 20,000.
The Top SQL Filter Grid
The column headers in Top SQL can be used to sort and filter based on a specific column. Simply clicking the header will alternate sorting the selected column in ascending and descending order. In addition, you can filter based on collected column values. To do so, hover over a given column until the funnel icon displays in the top right corner of the column header, and then select it.
You can also group the result set by given column values. Drag a column header to the area between the headers themselves and the Completed Queries tab, as shown in the screenshot.
This capability isn’t just great for organizing queries but you can also see in the group headers that several performance metrics have been aggregated. This allows you to see the big picture impact by or against certain objects. One of my favorite use cases for this feature are environments that are hit by a large volume of ad hoc user queries. Whether it’s that hour where you had issues or more macro such as entire days/weeks, this view can easily bubble up if a specific user is the culprit in hammering the instance.
Note that you can also sort this aggregated view based on the available metrics. To do so, right-click the column you’re grouping by and select Sort by Summary.
You can group “like” queries together by normalizing the result set. To do so, enable Show Totals, which is the sigma icon on the top toolbar (shown in the screenshot below).
The Show Totals Icon in Top SQL
This view will group queries together based on multiple column values, particularly Text Data and Database. Parameters are striped so that queries can be group together by Text Data, regardless of the parameters used in each execution. This view will provide aggregations of the performance-based columns, as well as additional duration metrics such as Min, Max, and Avg duration.
Show Totals View
Often, resource utilization issues aren’t caused by a single heavy query, but the accumulated impact of the same or multiple queries executing at a high volume. When this is the case, normalizing the result set will allow you to see the accumulated impact of queries based on the multiple executions that might have occurred.
Additionally, the Show Totals view can be a very good view for identifying what your heaviest queries are across days/weeks. This can help you focus on the portion of your workload that could have the greatest impact if optimized. This ties back to the 80/20 principle. If you focus on optimizing the heaviest 20% of your workload, you’ll see an 80% improvement, versus if you focus on the bottom 80% of your workload, which would only provide a 20% improvement.
For both the Show Totals and column grouping views, if you’re looking at larger time ranges, don’t forget about the Show Top setting on the Filter tab.
You can bring up the search functionality by selecting anywhere in the Top SQL grids and pressing CTRL + F. Once the search window is open, simply type a string of interest. The result set will be filtered by only events that contain the entered string in at least one column, and the string of interest will be highlighted. A simple example is shown below.
The Result Set Filtered by Events that Contain the Entered String in at Least One Column
The string search can be very helpful if you have queries that have very similar Text Data with small differences. The beginning of a query might be the same but have different clauses or parameters. The string search can help you find the version of interest. No wild cards are needed for this feature, as the search will automatically look for an entered string in a “%String%” fashion.
Using the Query History view, you can easily look at the historical performance of a query to assist in identifying trends, patterns, or anomalies. There are a few things to be aware of to fully leverage this feature.
Query History Result Set Grouping
Multiple Plan Usage for the Selected Query
A very common workflow when using the SentryOne Monitoring Platform is identifying some activity in question on the Dashboard, highlighting it, and jumping to Top SQL to start digging into what was executing during that time. What can sometimes be helpful is once you believe to have found that query in question, right-click it, select Jump to, and then select Dashboard. Doing so will actually overlay the event duration on the Dashboard, which can be used to visually compare the activity you’re seeing. As you can see in the screenshot below, in combination with presented data in Top SQL, I feel very confident that the query in question played a major role in the spike in CPU utilization.
Overlaying Event Duration on the Dashboard
It could also be helpful in verifying certain activity isn’t related to an event. Take visible Write latency during the same event. You can see Write latency increasing before the query execution in question. Write latency also starts a downward trend as the query in question is running. This visual behavior would indicate to me that this query had little to do with the Write latency that occurred at that time.
Write Latency Compared to Event Duration
If you find other highlights getting in the way at the bottom of the chart due to Advisory Conditions triggering, you can hide them by toggling the Show Advisory Conditions Marker (i.e., the Lightning Bolt icon) in the top toolbar.
The Show Advisory Conditions Icons in the Top SQL Toolbar
One important thing to keep in mind when analyzing queries through Top SQL is that the percent-based metrics (i.e., % CPU, % Reads, and % Writes) are based on the result set, not percent of resource utilization on the server itself. For example, you can see in the screenshot that the top query is listed as using 73% CPU.
As with most columns available through SentryOne features, if you right-click a column header and select Column Chooser from the menu, you’ll access additional columns that can be dragged onto the grid. One column I would highly recommend adding is the Has Plan column. This will provide a checkbox value that, if checked, means a plan is collected for that particular query. This way, you don’t have to blindly select the View option to see if a plan was collected.
If a query in Completed Queries is highlighted in red, it means the query failed. Something to examine in this case is the Error column. In this column, an error will usually display as 1=Error or 2=Abort. If you see Error, then it’s likely due to an internal SQL Server error or deadlock. As for Abort, this is usually tied to timeouts or manually getting killed. So, whenever you see Abort, the next thing you should check is duration, to see if the query got killed right at an obvious timeout threshold such as 30 seconds.
Errors Showing for Failed Queries
In the Running Queries tab, you might also see queries highlighted in orange, which means the queries are actively causing blocking. You can then right-click the query and select Show Block if you want to view the blocking event in question in the Blocking SQL tab, or Kill Process if necessary. You won’t be able to kill the SPID unless the account you’re running the client under has the permissions to do so.
If you have a regularly occurring Top SQL statement that you’re not interested in ever seeing in Completed Queries or being alerted on, you can hide it by right-clicking the statement in question and selecting Hide. Doing so will cause any executions of the statement in question not to show up in the result set and from being alerted on for conditions such as Top SQL: Error and Top SQL: Duration Threshold Max. This being the case, the Hide option should not generally be used as a filter. You can view hidden queries by selecting the Show Hidden Rows button in the top toolbar. Once visible, you can again use the right-click context menu to unhide particular statements.
Show Hidden Rows Button in Toolbar
Although we now have a newer Runtime Stats chart right in Top SQL, the older view could still be helpful, especially since it tracks all-time Min, Max, and Avg for each query. It also provides another view that could be helpful, as it clearly shows which occurrences were failures. You can get to Runtime Stats by right-clicking any statement in the Completed Queries section, choosing Jump To, and selecting Runtime Stats. For additional information about this feature, please refer to the blog post, “Have You Been Using Runtime Statistics?”
This information should help you get the most out of the Top SQL feature. As always, please feel free to contact the SentryOne Support team with any questions you might have regarding the functionality discussed here, or any other questions about SentryOne products.