SQL Sentry Indexes

Applies to: SQL Sentry

Indexes Tab

The Indexes tab displays information about your tables and indexes that are collected by the SentryOne Fragmentation Manager. The Indexes tab helps you make intelligent decisions about index management in your environment, such as when and how to perform defragmentation operations, when to adjust fill factors, or when an index definition should be changed.

SentryOne Indexes tab

Indexes Tab Sections

SectionDescriptionImage
Overview ChartsThe Overview Chart section, located at the top of the tab, displays a set of charts that contain aggregate statistics about all the indexes analyzed by Fragmentation Manager.SentryOne Indexes tab Overview Charts
Index Grid ViewThe index Grid view section, located in the middle portion of the tab, allows you to view index related statistics in a tabular format.SentryOne Indexes tab Grid View
Index Tree ViewThe index Tree view section, located in the middle portion of the tab allows you to view index related statistics in a hierarchical tree format.SentryOne Indexes tab Tree view
Detail ChartsThe Detail Chart section, located at the bottom of the tab, displays a set of charts that display statistics unique to the individual index. Selecting an index in the grid view area updates the detail level charts.SentryOne Indexes tab Detail Charts
Filter

The Filter section, located at the top of Indexes tab, is collapsed by default. It's used to filter what's shown in the Grid view section. Filter by SQL Server or database, and control the number of rows shown in the Grid view with the Show Top option.

SentryOne Indexes tab Filter Editor


Note:  Customize the layout of the Indexes tab, by right clicking any section heading, and then selecting the Customize Layout option.

SentryOne Indexes tab Customize Layout

Overview Charts

The Overview Charts contain aggregate statistics about all of the indexes analyzed on the target by Fragmentation Manager

SentryOne Indexes tab Overview Charts

Total Fragmentation

The Total Fragmentation chart displays a once a day aggregation of the fragmentation levels for all indexes that have been analyzed by Fragmentation Manager. Each index's fragmentation level for the day is summarized, and the indexes are next grouped by range; <10%, 10-20% , 20-30%, and >30%.

SentryOne Indexes tab Total Fragmentation Overview Chart

The Y-axis numbers represent the total number of indexes belonging to the target that have been analyzed by Fragmentation Manager. When you select a range on the chart, the indexes that fall into the corresponding range are highlighted in the index Grid view.

SentryOne Indexes tab select metric in Total Fragmentation Chart to see the corresponding Grid View

Total Space Usage (MB)

The Total Space Usage chart summarizes disk usage for each index that's had data collected over the specified time frame. As a reminder, adjust the minimum and maximum index size collection thresholds in the Database Source settings.

The chart has individual representations for Unused space, Used space, and Empty space. Unused space is represented at the bottom of the chart, Used space in the middle, and Empty space at the top. See the following table for explanations of each metric.

MetricDescriptionImage
UnusedUnused space is the amount of disk space that's been allocated for index pages, but doesn't contain any index pages.SentryOne Indexes tab Total Usage Unused space
UsedUsed space is the amount of disk space that's been allocated for index pages, and does contain index pages.SentryOne Indexes tab Total Usage Used space
EmptyThis is the amount of empty or unfilled space, within any index pages. The lack of page fullness indicates that index fill factors need to be adjusted.SentryOne Indexes tab Total Usage Empty space

Total Buffer Usage (MB)

The Total Buffer Usage chart summarizes SQL Server buffer usage, during the specified time range, for each index that's had data collected. There are individual representations of Used space and Empty space.

MetricDescriptionImage
UsedRepresents the size of the Used index pages contained within the SQL Server Buffer pool.SentryOne Indexes tab Total Buffer Usage Used space
Empty

Represents the amount of empty or unfilled space within those index pages that reside in the SQL Server Buffer Pool.

The Empty Buffer metric can be directly mapped to the Empty Disk metric because a page that's only 50 percent full on disk is only 50 percent full when it's contained in the buffer.

SentryOne Indexes tab Total Buffer Usage Empty space

Detail Charts

The Detail Charts contain statistics about individual indexes analyzed on the target.

SentryOne Indexes tab Details Charts

Index Fragmentation

The Index Fragmentation chart shows the fragmentation percentage of the selected index, measured during each scheduled Fragmentation Scan. If a reorganization or rebuild operation is performed on the index, and the Run Post-defrag Analysis setting is enabled, data points that represent the fragmentation percent after the rebuild or reorganization operation is complete display. Use this chart to help you determine what kind of fragmentation management schedule should be applied to the index.

SentryOne Indexes tab Index Fragmentation Details Chart

Each datapoint on the chart represents a Fragmentation Manager operation. Double-click on any datapoint to open the Event Calendar for the selected operation. The background colors of the chart correspond to the same ranges and colors as found in the Total Fragmentation chart.

Index Space Usage

The Index Space Usage chart shows how much of the selected index is located on the disk and in the buffer, over the selected time frame. It can also give you an idea about how much disk and buffer space is being wasted by the index due to non-full pages.

MetricDescriptionImage
Unused DiskThe amount of disk space that's allocated for index pages, but doesn't contain index pages.SentryOne Indexes tab Index Space Usage Unused Disk
Used DiskThe amount of disk space that's allocated for index pages, and does actually contain index pages.SentryOne Indexes tab Index Space Usage Used Disk
Empty DiskThis is the amount of empty or unfilled space, within any of the index pages.SentryOne Indexes tab Index Space Usage Empty Disk
Empty BufferRepresents the amount of disk space taken up by empty or unfilled space, within those index pages, that resides in the SQL Server Buffer Pool.SentryOne Indexes tab Index Space Usage Empty Buffer
Used BufferRepresents the size of the Used index pages contained within the SQL Server Buffer Pool for the specified index.SentryOne Indexes tab Index Space Usage Used Buffer

Index Activity

The Index Activity chart gives you a view of how the selected index is being used over the specified time frame. Use this chart to help identify unused indexes and the cost of your heavily used indexes.

MetricDescriptionImage
User ScansThe number of scan operations on the index caused by user activity.SentryOne Indexes tab Index Activity User Scans
User SeeksThe number of seek operations on the index caused by user activity.SentryOne Indexes tab Index Activity User Seeks
User Lookups

The number of lookups caused by user activity.

  • For a clustered index, this represents the number of key lookups done against the index.
  • N/A for non-clustered index
  • For a heap, this indicates the number of RID lookups.
SentryOne Indexes tab Index Activity User Lookups
User UpdatesThe number of updates caused by user activity. Indicates the level of maintenance on the index caused by insert, update, or delete operations done to the underlying table.SentryOne Indexes tab Index Activity User Updates
System ScansThe number of scan operations on the index due to system queries.SentryOne Indexes tab Index Activity System Scans


Note:  For general information about the Index usage statistics, see the  sys.dm_db_index_usage_stats (Transact-SQL) MSDN article.

Index Grid View

The Index Grid View, located in the center of the Indexes tab, contains various statistics about your individual indexes. When you select an index in the Grid view, the Detail index charts update with information specific to that index.

Apply filters and order the data by various columns. To apply a filter to the Grid view, scroll over a column, and then select the filter symbol located in the upper right hand corner of the column header.

SentryOne Indexes tab apply a filter to Grid view

After you apply a filter, the filter symbol persists in the column's header, allowing you to see which columns have filters applied.

SentryOne Indexes tab filter applied to Grid view

Additionally, the column name and any associated filtered text displays at the bottom of the Grid View. Use the button located beside the filtered text to remove the filter, or use the drop-down box to cycle through filtered columns.

SentryOne Indexes tab close applied filter

Edit the applied filters with the Edit Filter button found in the bottom of the Grid view. Filters can also be applied through the right-click context menu of any column.

SentryOne Indexes tab Grid View Edit Filter button


Note:  The information displayed in the Overview Charts isn't effected by filters applied in the Grid view.

To order the Grid view by a column's value, select the column header. An arrow displays in the column's header indicating that the Grid view is being ordered by that column. Cycle through ascending and descending order by selecting the column's header.

SentryOne Indexes tab Filter by Column header

Index Grid View Columns

ColumnDescription
SQL ServerThe name of the SQL Server where the index resides.
DatabaseThe name of the database where the index resides.
TableThe name of table where the index resides.
IndexThe name of the index.
TypeThe type of index. Heap, Clustered index, Non-clustered index, Primary index, Spatial index, or XML index. For more information about the different types of indexes, see the Types of Indexes MSDN article.
Primary KeySpecifies if the index is part of a primary key constraint.
UniqueSpecifies if the index is part of a unique constraint.
Data Space Type

Specifies the Data Space Type:

  • FG- FileGroup
  • PS- Partition Scheme
  • FD- Filestream data filegroup
Size (MB)The amount of disk space represented in megabytes taken up by the index.
Used (MB)The amount of disk space that's allocated for index pages, and does contain index pages.
Used (MB) in BufferRepresents the size of the Used Index pages contained within the SQL Server Buffer Pool.
Empty (MB) in BufferRepresents the amount of empty or unfilled space, within those index pages that reside in the SQL Server Buffer Pool.
Row CountThe number of rows of data belonging to the index.

Note:  Not available for Limited Scan mode.
Avg % FragmentedThe average fragmentation percentage of the index as recorded during the last fragmentation scan.

Note:  Not available with a Sampled Scan for heaps.
Avg % Page Space Used

The average percentage of available data storage space used in all the pages of the selected index. This indicates page fullness.

Note:  Not available for Limited Scan mode.

Fill FactorThe Fill Factor set for the selected index. For more information, see the Specify Fill Factor for an Index MSDN topic.
User Scans DeltaThe number of scan operations on the index caused by user activity since the last sample time (every 15 minutes).
StatusThe current status of any defragmentation operations being performed on the selected index.
Start TimeThe start time of the last defragmentation operation that was performed on the selected index.
End TimeThe end time of the last defragmentation operation that was performed on the selected index.
DurationThe duration of the last defragmentation operation that was performed on the selected index.
Last MessageIndicates the last defragmentation operation related to this index, including the last defragmentation method used on the selected index.
Avg Record Size (bytes)Indicates the average record size in bytes for the selected index.

Note:   Not available for Limited Scan mode.
Compressed PagesThe number of compressed pages for the selected index.
EnabledIndicates if the index has been made available. Set in the index properties of the native SQL Server tools.
Forwarded RecordsRepresents the number of records in a heap that have forward pointers to another data location. Forward pointers can be added during updates, when there isn't enough room to store the new row in the original location.

Note:  Only available for Index Type: Heap. Not available with a Limited Scan.
Ghost Records

Number of ghost records ready for removal by the ghost cleanup task in the allocation unit. Ghost records are records that have been logically deleted from a page but not physically deleted.

For more information about Ghost Records, see the following two SQL Skills articles:

Note:  Not available with a Limited Scan.

PagesThe total number of index pages for the selected index.
Unused Disk (MB)The amount of disk space that's allocated for index pages, but doesn't contain index pages.
Used %The percent of disk space that's allocated for index pages and contains index pages.
Version Ghost RecordsNumber of ghost records retained by an outstanding snapshot isolation transaction in an allocation unit.

Note:  Not available with a Limited Scan.
LOB DataSpecifies if the index contains one or more LOB data type.

 

Index Grid View Context Menu Commands

The following commands are available through the right-click context menu of the Index Grid and Tree View:

CommandDescription
Index PropertiesViews the Index Properties of the selected index in the native SQL Server tools.
Analyze Fragmentation Now

Selected your desired mode to perform a fragmentation analysis on the selected item.

  • Limited mode is the fastest mode and scans the smallest number of pages. For an index, only the parent-level pages of the B-tree are scanned. For a heap, only the associated PFS and IAM pages are examined; the data pages of the heap are not scanned.
  • Sample mode returns statistics based on a one percent sample of all the pages in the index or heap. If the index or heap has fewer than 10,000 pages, use Detail mode instead of Sample mode.
  • Detail mode scans all pages and returns all statistics.

For more information about Scan Modes, see the sys.dm_db_index_physical_stats (Transact-SQL) MSDN article.

Defragment Now

Use the Rebuild (offline), Rebuild (online), or Reorganize command to immediately perform the selected operation.

For more information about reorganizing and rebuilding indexes, see the Reorganizing and Rebuilding Indexes MSDN article.

Indexes that contain LOB columns cannot be Rebuilt online. When the Defragment Now > Rebuild (online) command is used on indexes that contain LOB columns an offline rebuild is performed.

Exclude Index from Automated Defrag or AnalysisThe Exclude Index from Automated Defrag or Analysis excludes the selected index from future Fragmentation Manager operations. After an index has been excluded it displays with a gray background in the data grid.
Reset Fragmentation StatsThe Reset Fragmentation Stats command resets statistics for the selected item, including the Avg percentage Fragmented statistic.
Jump To

Jump To Navigator opens the selected item in the Navigator pane.

Jump To Calendar opens the Calendar for the last analysis or defragmentation event start time.

CopyCopies the selected row or cell.