Top SQL Tips and Tricks
One of the most heavily utilized features in SentryOne is Top SQL. This comes as no 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 result sets and search for particular executions. In this KB, I will discuss several of these features. My hope is to help new users navigate Top SQL, and maybe show some existing users something they didn’t know.
While it is not the intent of this KB to discuss Top SQL collection, I do quickly want to highlight some important concepts, as I feel it is important to truly understanding and using Top SQL.
First is the Top SQL’s Minimum Duration threshold setting. This defines the minimum duration queries must run before SentryOne will collect it. By default, this setting is set to 5 seconds. This can be adjusted through Top SQL Source settings (Select desired level in the Navigator pane > Settings pane > Top SQL Source > Minimum Duration).
Note, I would not rush to setting this as low as possible globally. For one it can flood result sets making it more difficult to find the queries you are actually interested in. In addition, depending on volume of activity on a particular environment, this could lead to an exponential increase in the size of the SentryOne database which could potentially lead to performance issues. If you have any concerns, always feel free to communicate with our Support team (firstname.lastname@example.org).
In the case of queries that execute very fast, but often, this is taken care of by the Procedure Stats and Query Stats tabs within Top SQL. I will not go into depth on these tabs here, but some additional information can be found on them below.
Filtering & Searching
There are several tactics you can take to find a particular query or set of queries of interest. First, always be sure that you have set a window of time in which the query or queries have executed during, and that execution duration's were long enough to meet the Minimum Duration threshold.
At the very top of Top SQL, there is Filter drop down. Expanding the Filter window will provide several options for filtering the result set of Completed Queries. Note, 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 are viewing. After selecting a desired value to filter on, make sure to hit the “Refresh” button on the top tool bar. There is also a “Show Top” setting. This is the number of results that will be returned in Completed Queries. Note, if you are looking at a very large window in time, or have an extremely active instance, you may need to increase this value if you can’t find events you would expect to see. The max value for this setting is 20,000.
The column headers in Top SQL can be used to sort and filter based on a particular column. Simply clicking on them will alternate sorting the selected column in ascending and descending order. In addition, you have the ability to filter based on collected column values. To do this, hover over a given column till a “Funnel” icon populates in the top right, and select it. For example, let’s take the Application column. As you can see in the screenshot below, it shows all available applications in the given result set. Selecting one of the available applications will filter the result set for only that application.
There is also a “Custom” option with this functionality. You can use this to search or filter based on a string defined by you. Note, wild cards (%) can be used in this context.
Once you have applied any kind of filter, they will be listed just below the result set. There you can uncheck a particular filter or select the “X” to the far left to remove the filters all together. To the right of the filters is a drop down, which allows you to select from previously made filters. This is great if you have a particular filter set you use regularly.
There will also be the option to the far right of the filters to bring up the Filter Editor. Here you can add and remove filters, but also have the ability to apply Boolean logic for a more complex filter. To adjust the Boolean logic, simply select the red “And” to bring up the context window.
Group by Column Values:
While the column header filter allows you to filter the result set base on a particular value for that column, you also have to ability to group the result set by given columns values. You can do this by dragging a column header to the area between the headers themselves and the Completed Queries tab, as shown in the screenshot below. You can also see an example using the Database column. Queries executed against the same database have been grouped together into expandable rows housing the actual events. This is great for organizing queries, but you can also see in the group headers that several performance counters have been aggregated. This can be useful for viewing overall performance or resource utilization of a result set against columns values such as databases or users. To remove this grouping, simply drag the column header back onto the grid.
You can also group “like” queries together by normalizing the result set. You do this by enabling “Show Totals”. This will be the sigma button on the top tool bar (see screenshot below).
This 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.
One of the most over looked features in this context is the ability to search for a particular string in any column. You can bring up the search functionality for either Running Queries or Completed Queries by selecting anywhere in their respective grids, and pressing “CTRL + F”. Once the search window is up, 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.
A great use case for this, is if you are looking for a particular query execution based on a parameter used. If a particular query executes hundreds or more time in a given period, it could be very difficult to skim through the result set looking for the execution of interest, even after utilizing some of the grouping features mentioned. Also note, that no wild cards are needed for this feature, as the search will automatically look for an entered string in a “%String%” fashion.
Understanding Percent Based Metrics:
One important note to keep in mind when analyzing queries through Top SQL is that the percent based metrics (% 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 below that the top query is listed as using 73% CPU. Again, this is not 73% of available CPU on the Server. Rather, out of the six available queries in the results set, the top query accounts for 73% of CPU utilization out of those six queries. Note, any decimal values are dropped and queries that account for less than 1% of the result set will be blank. So, the percentages may not always add up to exactly 100%, but should be quite close.
As with most columns available through SentryOne features, if you right-click any given column header and select Column Chooser, you will have additional columns that can be dragged onto the grid. One in particular I would highly recommend adding is the “Has Plan” column. This will provide a check box value, that if checked, means we collected a plan for that particular statement. This way, you do not have to blindly select the “View” option to see if a plan was collected.
If you have a regularly occurring Top SQL statement, that you are not particularly interested in ever seeing in Completed Queries or being alerted on, you can Hide it by right-clicking the statement in question > Hide. This will cause any executions of the statement in question to not 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, it 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.
A great feature that is regularly over looked is Runtime Stats. This will provide a visualization of collected runtimes for a particular statement. You can get to Runtime Stats by right-clicking any statement in the Completed Queries section > Jump To > Runtime Stats. For additional information on this feature, please refer to the following blog post (https://blogs.sentryone.com/patrickkelley/using-runtime-statistics/ )
The information provide should help in getting the most out of the Top SQL feature. I hope most of you have learned something from this post to make work just a little easier. As always, please feel free to contact our dedicated Support team with any questions you might have regarding topics discussed here, or any other questions about SentryOne products.