Scan for SQL Server instance index fragmentation percentage level

Applies to

ApexSQL Defrag

Summary

The following guide provides an overview of different SQL Server instance index scanning operations with the goal of determining the exact index fragmentation percentage level.

Description

SQL Server index fragmentation is a natural occurrence that is the result of database performance degradation. It comes in two different forms:

  • Internal fragmentation – occurs when there is a lot of empty space on a data page
  • External fragmentation – occurs when there is no match between the logical order of pages in an index and the physical order of pages in a data

The more data is being modified in a database the more its indexes get fragmented.

Therefore, it is vital to have an index fragmentation percentage level fresh and updated before any of the index maintenance operations are being performed against those indexes.

Keep index fragmentation percentage level up to date

To ensure that the index fragmentation percentage level of a SQL Server instance is fresh and updated, head over to the Fragmentation tab and from the Analyze drop-down menu choose one of the two presented analysis. The scan analysis will be performed, and its results are shown in the index grid based on the default selected scan mode and grid filters:

  • Fast – scans last known index fragmentation state in CRD, the Central Repository Database
  • Deep – scans for current index fragmentation state:

Fast and deep index fragmentation analysis

Quick tip icon

Quick tip:

The mentioned default scan mode settings can be easily configured in few simple steps but more on that in following sections.

Different types of index fragmentation scanning

When it comes to different scan types, there are three modes to consider from:

  • Limited – the default mode in SQL index software that collect limited information about the index fragmentation, not causing any performance issues to the system. It is the fastest mode
  • Sampled – this scan mode takes a sample portion of the SQL Server instance index and based on that sample displays approximate fragmentation information of all index pages that the sample was taken from
  • Detailed – this mode scans all index pages and displays its fragmentation statistics. Depending on a size and number of index pages, it may take more resources and, in some extreme cases, may lead to some performance issues down the line. However, it displays the most accurate statistics

Different types of index fragmentation scanning in SQL index tool

Grid index fragmentation filters

SQL Server instance index fragmentation percentage level can be listed in two different grid displays:

  • Fragmentation level – show indexes with high, medium and/or low percentage of index fragmentation
  • Fragmentation threshold – only shows indexes with fragmentation percentage above specified value

Grid index fragmentation filters

Fragmentation level grid filter

Once a SQL instance, its databases or indexes are selected from the server explorer, from the Grid filter category one of three fragmentation level options can be chosen:

  • High – in grid only shows the high fragmented indexes, by default all indexes with fragmentation over 30%
  • Medium – medium fragmented indexes, with fragmentation percentage between 10% and 30% are shown in the grid
  • Low – only SQL Server instance indexes with low percentage level of fragmentation, up to 10%, are shown:

Fragmentation level grid filter in SQL index tool

Quick tip icon

Quick tip:

Fragmentation level threshold can be easily set with desired custom values, by moving to the Configuration tab where from the View tab of the Options window High, Medium and Low values can be configured:

Fragmentation levels configuration

Fragmentation threshold grid filter

When the Fragmentation threshold is set to a specific value, then index grid view will display only indexes with fragmentation level percentage above that value:

Fragmentation threshold grid filter in SQL index tool

Server explorer Treeview filters

When any of Treeview filters are unchecked, SQL Server instance databases with the unchecked filter will be hidden from the server explorer and no longer available for visual display. The following image shows that only databases with Low fragmented indexes are selected in the server explorer Treeview filter to be shown:

Server explorer treeview filters