SQL index fragmentation and maintenance

One of the most critical variables for database performance is SQL index fragmentation. If the fragmentation level is not managed properly, blockages, jams, IO problems, and disk ejection problems can occur. A high level of index fragmentation can also lead to improper execution plans.

DMV

In SQL Server, using the Dynamic Management View (DMV) sys.dm_db_index_physical_stats, information about index fragmentation level, pages counts, and index types can be obtained.

SSMS

One of the in-built SQL Server reports in SSMS that helps monitor database index fragmentation and usage statistics is the Index Physical Statistics report:

This report returns statistics about the index partitions, fragmentation percentage, the number of pages on each index partition, and recommendations to rebuild or reorganize the index depending on the fragmentation percentage of the index:

However, there is a limitation for running this report at the database level, and creating this report for multiple databases located on multiple SQL Server instances becomes a time-consuming and resource-intensive task.

Custom scripts

Gathering all this information in one centralized place helps in making decisions about the index maintenance policy. For small databases, DBA’s do not care about the page counts while rebuilding the index however, for large databases they do need to consider page counts as well. For detection and resolving SQL index fragmentation issues, ALTER INDEX commands to remove the fragmentation level can be used in custom scripts also, but it is another complex procedure for manually establishing and analyzing results.

3^rd party tools

Database administrators can easily monitor, analyze and remove the existing SQL index fragmentation using the ApexSQL Defrag. This way, multiple SQL Server instances, and databases can be managed from one centralized place, and specific benefits from this graphical tool are:

• A graphical user interface to monitor and analyze the index fragmentation
• Index maintenance for all SQL Server instances and databases
• The custom reports based on the specific requirements, that can be configured
• Configure policies to check and perform index maintenance tasks as per the custom metrics
• Index fragmentation analyzing (fast or deep), setting fill factor, online index maintenance
• Reports in desired formats (PDF, IMG, HTML, CSV or XML)

After installing the tool and configuring an SQL Server instance to monitor, ApexSQL Defrag shows the details of SQL index fragmentation status quickly:

• Note: Rebuilding and reorganizing very large indexes should be avoided if not necessary because it takes a lot of time and more system resources to analyze them

ApexSQL Defrag gives the option to check the index fragmentation level with three scan modes:

• Limited – SQL Server collects limited information about the fragmentation. It does not cause any performance issues to the system. It is the default mode in ApexSQL Defrag
• Sampled – it takes a sample portion of the SQL index and collects the fragmentation information
• Detailed – This mode can be used if high-level fragmentation is observed with the above scan modes. It might cause some overhead to the system resources however, after this scan mode, detailed knowledge about all index pages is provided

Similarly, a SQL index fragmentation level can be set in the High, Medium, and Low categories:

There is an option to set Fragmentation threshold to analyze the indexes, as well:

On the application grid, all scanned indexes will appear, and right-click on the particular SQL index provides more options to choose from:

• Analyze (fast)
• Analyze (deep)
• Reorganize/Rebuild/Rebuild online
• Fill factor
• Duplicate index
• Resume/Pause/Cancel – these options can be useful if any performance issues occur or blocking due to a particular index happens

ApexSQL Defrag allows creating the policies from the pre-defined templates or by the custom parameters. The template is also suitable for most of the SQL index defragmentation requirements:

Using these templates, there is a possibility to defrag all indexes or indexes based on threshold level in offline or online mode. In the example below, a policy has been created to Rebuild online top 50% of most fragmented indexes over 30% or reorganize if fragmented over 10% :

There are options in the Thresholds tab for configuring the resources threshold regarding CPU load, memory load, transaction log usage, hard disk usage, and active transactions as well. This level of control over index maintenance is not possible with the maintenance wizard jobs or custom scripts use.

ApexSQL Defrag is suitable to configure and monitor multiple jobs. Index maintenance jobs can be canceled after the set number of hours. If the resource is occupied, retry timing can be set as well. These and other options in the Resource thresholds area can greatly help users to execute the database maintenance based on their specific requirements:

Sometimes an index must be excluded where the index page counts are less than or greater than specified counts. Further customizing SQL index maintenance operation by the fragmentation level, index size, page count, and execution options as well can be seen in the below screenshot on Target filter and Execution areas:

Changes can be made and configured as a template to reuse. There is an excellent overview of the SQL index fragmentation stats before and after the maintenance in the Activities tab, shown after performed index maintenance. It is essential to know whether the index fragmentation level is under control or not after the maintenance and usually needs to capture the fragmentation level again, which is now overcome with this overview:

Overall reports from the server level are shown in the Reports section:

There is a view of the Top 10 clustered indexes and Top 10 non-clustered indexes:

If there is an interest in the CPU and Memory behavior during the index maintenance is running, there is an option that provides this useful information and through it, the system resources can also be monitored:

ApexSQL Defrag allows the export of SQL index fragmentation reports in the following formats: PDF, IMG, HTML, CSV, and XML:

Creating data reports is quite simple and PDF, IMG, and HTML export reports can be easily configured with the export options to choose what information to display in a report:

Conclusion

Maintaining indexes is an ongoing and very responsible job assignment. With ApexSQL Defrag tool the SQL index fragmentation of SQL Server instance can be easily monitored by DBA’s. All necessary information is provided in this graphical tool with custom visuals and policies along with various informative reports.