Power Up The Grid(s)
My last post kicked off this series with a look at the Jump To feature in SQL Sentry Performance Advisor and SQL Sentry Event Manager. I chose that as the first topic because Jump To is fundamental to troubleshooting performance problems using our software and, let’s face it, it’s just plain cool.
One final point about Jump To: In my estimation, the most important aspect of the Jump To command is that it helps you identify and evaluate potential correlations between metrics and events in various contexts. And while finding correlations is often the key to determining root cause, sometimes discovering NO correlation is just as valuable. The absence of correlations suggests you should look elsewhere and can keep you from running down the wrong rabbit hole, wasting time and effort looking for a root cause that doesn’t exist!
So, enough about Jump To. Let’s move on.
Control the Grid
Today, we are taking a slightly different tack. Rather than focusing on a single feature, we are going to explore the many properties of a single control type found throughout the SQL Sentry Client – the grid. How ubiquitous are grids? Rather than tell you, I’d like you to find out for yourself. Open the web version of our User Guide, click on Search in the left panel (did you know about THAT feature? – be honest!), and type grid in the search box. How many results did you get?
I got thirteen, and hopefully so did you. Ten are links to Performance Advisor tabs that include a grid, two are not relevant to our current discussion (different kind of grid) and the last is an introduction to data grids. Take a moment to read that last one now. It covers basic grid “organizational methods” that I am not going to discuss further (Group By, Sorting, Filters, Exporting grid data) but are important to know about. Go ahead, I’ll wait. One of the office Keurig machines is calling me.
Done? Excellent. Let’s get started.
Note Unless stated otherwise, the examples we will look at today are from the Top SQL tab in Performance Advisor.
Most of the “advanced” grid features are accessed by right-clicking on any grid column header row, which brings up this context menu:
The first few items related to Sorting and Group By are explained in the User Guide section you just read, so I won’t elaborate further.
Choose Your Columns
The first grid option of interest to us is “Column Chooser”. When that option is selected, you are presented with a list of additional columns you can display in the grid. Each item in the list represents additional data collected by the SQL Sentry Monitoring Service and stored in our database. Here is the Column Chooser list for the Top SQL tab Running Queries pane:
You add or remove columns from the grid by dragging and dropping them between the grid header and the Column Chooser, creating a customized version of the grid that suits your needs. Two of my personal favorites are the Has Plan and Has Statements columns for the Top SQL Completed Queries grid.
The combination of these two values being True (checked, in the below example) means Performance Advisor captured the estimated query plan, along with individual T-SQL statements at the time the query executed. Good information to have before you click on the View Plan button!
(Our KB article: Why Would A Query Plan Not Get Collected? answers the question at least some of you are now asking.)
Bottom Line: when you think we should collect and display more data than you see in a grid – we probably do. Open the Column Chooser to find out!
Find Text Strings
Monitoring a busy server, or looking at data covering an extended time period (days, weeks, or months) can result in a grid with hundreds or thousands of rows. In such a situation you may find yourself asking, “How do I find the golden nugget(s) in the midst of all this, well… stuff?” Not surprisingly, we asked the same question, so our grids offer several options for searching and filtering results sets.
If you are looking for specific text anywhere in the grid, use the Find Panel. From the context menu noted above click on “Show Find Panel” and a text box is placed above the grid header columns. Type in the text you are looking for and it will be highlighted anywhere it appears in the grid. In my example below I typed ‘sql’ in the box (notice that wild cards aren’t necessary):
Bonus feature: searched text is saved so you can retrieve it by clicking on the down arrow to the right of the search box.
Filter Grid Results
You can filter grid results using the Filter Editor, in several ways. The simplest method is to use the Auto Filter Row. Starting from our now familiar context menu, select “Show Auto Filter Row,” which places an editable, empty row immediately below the grid header row. Now enter values in the Auto Filter Row cells to filter the rows under each column. In my example below I entered ‘%exec’ in the Text Data column, ‘b’ in the Login column, and 15 in the CPU column.
Notice the Filter description at the bottom of the grid. The SQL Sentry Client built the filter as you entered values in the Auto Filter Row.
A second way to create a filter is by clicking on the funnel icon available in every column header. For columns that contain a limited set of known values, e.g. Host, Login, Application, Database, etc., you can select a particular value, (All), or (Custom) from the drop-down menu. For example if you want to see all captured queries against a particular database in the Top SQL grid, click on the funnel in the Database column header and select the appropriate database. In the screen shot below, I’m filtering for queries run against the Catawba database.
If you want to look at queries from multiple databases, click on Custom and fill out the Custom AutoFilter panel.
This filter will return all rows where database name equals SQLSentry or database name starts with the letter ‘m.’ The drop-down lists are context sensitive, ensuring you only choose conditions appropriate for the data type of the filtered column.
Conditions for text data (left) and numeric data (right)
Filtering on multiple columns in this fashion allows you to quickly build very complex filters.
Again, any filters you build appear directly below the grid. You can enable/disable the current filter by clicking on the checkbox to the left of the filter description. As you add or refine your filters during a session the down arrow to the right of the filter description allows you to select or delete any of the filters you have defined.
Clicking on the Edit Filter button brings up the full Filter Editor. A complete tutorial on the Filter Editor is beyond the scope of this post, so I encourage you to read the documentation.
Totals Mode vs. Default Mode
The Top SQL Completed Queries grid and the Query Plans grid have two display modes – Default and Totals. Switch between the two modes by toggling the Show Totals (∑) button in the toolbar. Default mode is a straightforward list of the queries returned for the selected time range. Totals mode groups the queries together.
Top SQL Completed Queries grid in Default mode. Sorted by End Time, descending
In Top SQL, Totals mode groups the same queries using a normalized version, replacing any parameters with a question mark. Columns for metrics show aggregate values for each group. An example from Top SQL should help clarify how this works.
Sample Top SQL Completed Queries grid in Totals mode. Sorted by
This grid shows four queries captured during a ten minute period on our demo server. You can see that the parameter value for the first query has been replaced with a question mark. The Count column on the right tells us how many times each query was executed during our selected time range. The other columns provide aggregate or total values for all executions of a particular query during this time range. So for example, exec spReportInternetSalesbyColor executed 8 times. The total duration of all 8 executions was 2 minutes, 44.885 seconds, total CPU was 124,311 milliseconds, representing 43% of total CPU usage compared to all other captured queries during our time range, etc., etc.
Totals mode allows us to quickly see which queries are running longest, using the most CPU, executing the most Read operations, etc. cumulatively over any selected period of time. Note that these columns are also sortable and filterable so we can focus on what is most important to us as we work through our troubleshooting process.
Cumulative totals are an important tool in finding queries that are placing a load on SQL Server, but they aren’t the whole story. If we could drill in deeper we might be able to correlate a specific query execution to a spike in resource utilization, or perhaps a blocking event. We could also compare the per-execution metrics of a query looking for the existence (or lack of) significant differences in duration, reads, writes, or other metrics. Thankfully, all of that is possible. (Was there really any doubt?)
Let’s take a quick look at two result sets. In the first, we see 7 executions of stored procedure spReportInternetSalesbyProduct:
Notice that although the procedure was executed by three different logins from three different hosts the metrics are all roughly the same, with the greatest variation in Duration. The per-execution Duration, CPU, and Reads sum to the same values as the Normalized row. The percentage based column values (CPU %, Reads %, Writes %) are relative to the other executions of the same query during this time period and should add up to 100% (with some rounding error possible).
The second example captures 7 executions of a parameterized query. Again, most of the per-execution metrics are fairly similar with the exception of the first and last executions. The first execution (bottom row) performed 8 write operations (100% of the total writes for all 7 executions), and the last execution required about triple the number of read operations. These differences may be due to different parameter values, which you can check out by highlighting each execution, then looking in the Text Data pane.
Hide Queries
The last grid feature I’ll cover is specific to the Top SQL Completed Queries grid. The context menu Hide command gives you the ability to hide individual queries or groups of queries from the Top SQL result set. Options for hiding/unhiding queries include the Show Hidden button on the toolbar and the Visible column, available from the (Buehler…, Buehler…) Column Chooser!
Hiding queries also stops alerts from being generated for them for any of the Top SQL related conditions, such as Top SQL: Error and Top SQL: Runtime Threshold Max. This is a great way to fine tune your Alerting and Response System in SQL Sentry Performance Advisor.
Thanks for reading! I’ll be back soon with more things you didn’t know our software could do!
