Documentation forSQL Sentry

Plan Explorer Index Analysis

Introduction

The Plan Explorer Index Analysis tab provides a sandbox environment for planning indexing strategy relative to a specific operation in a query.

For Index Analysis to populate, the query must execute from within the Plan Explorer session, using the Get Actual Plan menu option.

Additional Information: See the SQL Sentry Index Analysis blog post for more details and examples.

SQL Sentry Plan Explorer Index Analysis tab

The Statement Tree and Plan Diagram

For more information, see the Integrated Plan Explorer topic. 

The Text Data Area

For more information, see the Integrated Plan Explorer topic. 

The Indexes Data Grid

SQL Sentry Plan Explorer Index Analysis Data Grid

The top row is the index Total Score that's based on several factors including covering, value density, sorting effectiveness, and seek-ability. 100 percent is the best possible score; however, there are some situations that prevent the possibility of a 100 percent score. One known cause is when columns are missing statistics. 

Column statistics are provided for each table column used by the query, with table columns used by the selected operation displayed in bold.

Data Grid Columns:

Name Description
Table Column Name of the column in the relevant database table.
Last Statistics Update The date the column statistics were last updated.
Update Statistics Automatically updates the existing statistics. 
Avg. Length Average length of data values.
Estimated Size (MB) The estimated size of the column values (in MB).
Output Checked if the column is an output for the selected operation.
Sort Type Sort order (ASC, DESC).
Sort Order Ordinal position of the column in the sort.
Predicate The search or join predicate(s) applied to the table column by the operation.

Columns in the grid to the right of the Predicate column all represent indexes. These include both special case indexes, as well as the indexes defined on the table.

Indexes can be modified, as well as scripted for create, drop, or drop and create. Manually update statistics using the button at the bottom of the index column.

Index Coloring:

  • Shades of green indicate that the ordinal position of the column is such that a favorable operation may occur, such as a seek over a scan. Darker shades of green indicate that the column is filtered.
  • Yellow indicates that the column is used in the query, and covered, but isn't part of the left subset of the index key. Included columns are also listed in yellow.
  • Red indicates that the column involved isn't covered by the index.

The Parameters Data Grid

The parameters grid lists parameters used by the query. This differs from the Plan Explorer Parameters tab in that it uses tipping point logic to help determine the type of operation (Seek or Scan) that the optimizer might choose for that parameter value. This isn't an exact science, and should be used as a rough guideline.

The parameters grid provides a Test Value column that allows you to enter a new parameter value and asks for a new Estimated Plan. This allows you to test different parameter values against the plan to see if the plan shape changes given that parameter value.

The small arrows to the right of the parameter values allow you to quickly place the value of that parameter into the Test Values column.

SQL Sentry Plan Explorer Index Analysis Parameters data grid

Options for Parameters Data Grid

Option Description
Tipping Point Range Select this box to display  the tipping point range on the histogram chart.

Additional Information: Blog posts from Kimberly Tripp on the tipping point.
Est/Actual Rows Select this box to show estimated and actual rows on the histogram chart.
Range Rows Toggle this switch to Range Rows to display the total range rows on the histogram chart.

Note:  Total range rows can be helpful for visualizing range predicates.
Avg Range Rows Toggle this switch to Avg Range Rows to display the average range rows on the histogram chart.

Note:  Average range rows can be helpful for when working with equality predicates.

The Statistics Histogram Chart

The Histogram chart displays the histogram steps for the index statistics visually. This allows you to quickly spot potential parameter sniffing issues.

This chart also works in conjunction with the parameters grid, and selected parameters that are present in the histogram have their histogram buckets highlighted on the chart. This also works for parameter ranges.

SQL Sentry Plan Explorer Index Analysis histogram selection and chart

Note:  Hover over the histogram chart to view more details.

SQL Sentry Plan Explorer Histogram chart details

Note:  For more information about Index Analysis, see the Sentry v10 : Index Analysis blog post.

Update Statistics

SQL Sentry Plan Explorer Index Analysis Update Statistics

Update the statistics for a selected index on the Index Analysis tab by selecting the Update Statistics buttonSQL Sentry Plan Explorer Update Statistics button . On the Update Statistics window, select  the sample mode, select a sample percentage, and then select Update to refresh the index statistics.

SQL Sentry Plan Explorer Update Statistics window

Note:  The Sample percentage for an index is set to 20 percent by default. 

Update Statistics Window

Option Description
Statistic The selected index statistic that you want to update.
Statistic mode
SAMPLE Scans a sample of the selected index based on the desired sample percentage.
FULLSCAN Scans the entire selected index.
RESAMPLE Resamples the selected index.
Sample Percentage This option is only available if you select the SAMPLE scan mode option. Scans the entered percentage of the index.
Update Updates the statistics based off of your selections.
Cancel Closes the Update Statistics window.

Script Index

SQL Sentry Plan Explorer Index Analysis open Script Index

View the associated script for a selected index by selecting the Script Index buttonSQL Sentry Plan Explorer Script Index button

Script Index Window

SQL Sentry Plan Explorer Script Index window

Option Description
Script Editor View and make edits to the script for the selected index in the script editor view. 
Drop checkbox Displays the Drop index statement when selected. Removes the Drop index statement when deselected.
Create checkbox Displays the Create index statement when selected. Removes the Create index statement when deselected.
Copy to Clipboard Copies the entire index script to the clipboard.
Execute Executes the index script.
Close Close the Script Index window.

Script Index Window Context Menu

SQL Sentry Plan Explorer Script Index window context menu

Context menu option Description
Copy Copies the selected text from the Script editor.
Select All Highlights all of the text in the Script editor.
Save SQL Saves the script as a .sql file.
Word Wrap Displays the script using word wrap to minimize horizontal scrolling.