Query Plans

Applies to: SQL Sentry and DB Sentry

The Query Plans tab lists all plans collected for Top SQL events for the specified date range, and provides a detailed chronology of all query plan changes. Use Query Plans to look back at any point in time to see when a plan change led to a query performance problem. 

SentryOne Query Plans

The Show Totals toolbar button controls whether the grid lists plans are grouped by stored procedure.SentryOne Show Totals toolbar button
Show Totals toolbar button.
SentryOne Show Totals enabled
Show Totals enabled.
SentryOne Show Totals disabled
Show Totals disabled.

Jump to > Query Plans tab by highlighting a range on the dashboard as usual. For example, if you see a spike in parallelism waits on the SQL Server Waits chart, highlight the spike, and then select Jump To > Query Plans. Sort by the Parallel Operations column to determine which queries caused the spike.

SentryOne Jump To > Query Plans
Highlight spike, then select Jump to > Query Plans.
SentryOne Query Plans sort by Parrallel Operations column
Sort by Parallel Operations in Query Plans.

Note:  The same approach can be used to find queries with key or RID lookups because Lookups are now displayed on the Dashboard, as well as other metrics that correlate, such as I/O and table/index scans.

Display Panes

Display PaneImage 
The Filter pane allows you to filter the Query Plans by SQL Server.SentryOne Query Plans Filter pane
The Bottom pane lists the records matching the current filters. There are two modes for this pane: Default and Totals.SentryOne Query Plans Bottom pane
Default lists presents all events for the active date range in a standard list format, sorted in order by Database name, Object name, and Plan Creation Time (descending).SentryOne Query Plans default columns
Totals lists presents all events for the active date range in a standard list format, sorted in order by Database name, Object name, and Plan Creation Time (descending).SentryOne Query Plans Totals

Controls

Top Pane

After changing the filter, select Refresh , or press F5 to apply the filter.

SentryOne Query Plans enable filter

Bottom Pane

To open the plan in the Query Plan Analysis window, select the Open button under the Plan column. (If the Show Totals button is selected, you need to expand the Database Name to see the Plan column).

SentryOne Query Plans Launch Query Analysis

Note:  The context menu provides an option to Highlight recompiles in the current date range for a statement by enabling the checkbox.

SentryOne Query Plans Highlight recompiles in the current date range context menu