How to monitor the status of index defragmentation jobs with ApexSQL Defrag (4262113)

SQL Server Index is one of the most important factors in the SQL Server performance tuning field that is used to enhance the queries’ performance by speeding up the data retrieval process. It is a double-edged sword that can enhance the database performance when designing it properly and performing the index defragmentation maintenance periodically, and degrade the database performance when it is not designed or maintained well.

• For more information about the SQL Server index concept and the best practices to follow while designing the SQL index, check the SQL Server Index Series.

In this article, we will show how to create an index defragmentation job to mitigate the index fragmentation and page split issues and monitor the status and result of that job.

ApexSQL Defrag is a SQL Server index monitoring and analysis 3^rd party tool that is used to monitor and manage SQL index defragmentation operations in SQL Server.

ApexSQL Defrag can be easily used to connect to your SQL Server instance and analyze the index fragmentation percentage at the instance, database, or table levels. Based on the fragmentation percentage of the analyzed indexes, ApexSQL Defrag is used to perform the index defragmentation directly rebuild or reorganize the selected index or create a custom policy, that can be scheduled to be executed at a specific time and frequency, with the ability to choose from the built-in policies templates, to rebuild and organize the fragmented indexes based on the specified fragmentation percentage threshold, with the ability to execute a customized script before or after the defragmentation policy and send email notifications to predefined email addresses.

ApexSQL Defrag provides you with the ability to monitor the index fragmentation on multiple servers and provide you with exportable reports for the index fragmentation percentages and the result of the indexes’ defragmentation policies.

Installation and Configuration

ApexSQL Defrag can be downloaded from ApexSQL Download page, and installed to your machine, using a straight-forward installation wizard, in which you will be requested to accept the agreement license, specify where to install the ApexSQL Defrag tool in your machine and the service account that will be used to run the ApexSQL Defrag Agent service and conveys the commands to the SQL Server instances, as shown below:

Once the ApexSQL Defrag tool installed to your machine, it will ask you at the first run to specify the SQL Server instance where the central repository database, where the tool configurations and indexes fragmentation information will be stored, will be hosted, as below:

Where it will ask for your confirmation to create the central repository database on the selected instance, as shown below:

Index Fragmentation Analysis

After creating the ApexSQL Defrag central repository database, we are ready now to add a new SQL Server instance that we will work in analyzing the fragmentation percentage of the existing indexes. To achieve that, click on the Add button, under the Servers options group of the Home page, and provide the name of the SQL Server instance to connect to and the credentials that will be used to connect to that instance, as below:

With the ability to perform multiple operations on the registered servers, such as editing the registered server’s connection information, removing the registered servers, or refreshing the server’s lists. When a new server is added, ApexSQL Defrag will perform a full scan for all server indexes automatically and provide detailed information about the on index type, size, fragmentation percentage, and fill factor with variant options to group and filter the indexes list, and search for the indexes information at the instance, database and table levels, as shown below:

ApexSQL Defrag provides you with the ability to fix the index fragmentation issue for a specific index or group of indexes directly by selecting these indexes and perform the index rebuild or reorganize operation directly on that index and provide you with the defragmentation result and the new fragmentation percentage, as shown below:

If the index fragmentation information is not refreshed automatically, you can Analyze the index information again, using the Fast scan, scan last known index fragmentation state in central repository state, or the Deep scan, Scan for current index fragmentation state, as shown below:

ApexSQL Defrag allows you also to allocate the duplicate indexes, and provide you with the index information and the script that can be used to drop the duplicate indexes, after reviewing the index usage, as shown below:

You can also monitor and set the Fill Factor value and Pad Index option for each index separately, taking into consideration that changing the Fill Factor option will perform an online/offline rebuild for that index, as below:

Under the Reports tab, you can get an overview for the index fragmentation percentage and space usage at the instance, database or table levels, as in the Total report below:

And statistical information about the available indexes, such as the type, size, and fragmentation for the indexes, as shown in the Statistics report below:

In addition to the top 10 clustered and non-clustered indexes based on the fragmentation percentage, with detailed information about these top 10 indexes, as in the Top 10 report below:

With the ability to export these reports to different files types, as below:

Index Defragmentation Policies

The index defragmentation jobs are usually performed during the non-peak times, which is most of the time during the midnight of the weekends, where no one is available to perform such operation manually.

In order to perform the index defragmentation job at the proper time, without affecting the database administrator’s personal life, ApexSQL Defrag provides us with the ability to create a new policy and schedule it to be executed based on a predefined schedule.

To create a new policy, click on the Create option under the Policy options group of the Policies tab, and choose to create a custom policy based on your own criteria, or select from one of the eight available policy templates, as shown below:

In the General tab of the Create Policy wizard, provide a unique meaningful name for the defragmentation policy that reflects the purpose behind this policy, Enable that policy, select the target indexes that will be processed within this policy and whether to perform the index rebuild operation online or offline, as shown below:

As the name indicates, you will be requested in the Threshold tab to specify the fragmentation percentage range for the indexes that will be reorganized and rebuilt, the fragmentation scan mode and number of SQL Server resources consumption thresholds during the index defragmentation operation, as shown below:

After that, you will be asked to set the daily or weekly schedules for rebuild and reorganize jobs, or execute them once in real-time, as shown below:

In the Advanced page, you can configure some advanced index defragmentation options, such as the excluded indexes based on the number of index pages, as shown below:

ApexSQL Defrag allows you to optionally run custom scripts before or after executing defragmentation policies, based on your company requirements, as below:

And finally configure the policy to send email notifications for successful or failed index defragmentation jobs, as shown below:

Once the index defragmentation policy is created, you can easily select that policy then click on the Edit option, and change any configuration option, such as the fragmentation percentage or the resources thresholds, as shown below:

In addition to the ability to delete, enable, disable or directly run that policy, as below:

Monitoring Policy Status

ApexSQL Defrag provides you with the ability to monitor all defragmentation jobs and policies from a central location.

If you try to select one of created index defragmentation policies and run that policy, the Policies tab will provide you with useful information about that executed policy, such as an indicative icon for the policy result, if the policy is enabled or disabled, when the job is scheduled to be executed, the current status for that policy, when was the last run for the policy and when the next run is scheduled, with a general message when the policy execution failed.

To get detailed information about the policy failure, the Alerts tab provided a centralized location for all policies that are executed in the ApexSQl Defrag tool.

In our example here, the Policies tab shows that the index defragmentation job failed with an error message without showing any useful error message. But the Alerts tab shows a meaningful error message, that the database that we are trying to process its indexes is read-only databases, as shown below:

As this is an Always-on Availability Group, it seems that a failover performed previously on that instance that made the current instance as readable secondary. To fix that issue, we will perform a manual failover and make the current instance the Primary node again, as shown below:

Let us run the index defragmentation policy again after the failover. You will see that the job completed successfully with no error message, as it is clear from the Policies page, with an indicative icon for the successful execution and no error message displayed, and from the Alerts page where it shows two messages of policy start and successful execution, as shown below:

The previous example showed the detailed useful information that you can get about all index defragmentation policies execution from the centralized Alerts window, with the ability to filter the Alters result to provide the alerts received during a specific period and the type of these alerts, such as errors, warnings or informational, as shown below:

ApexSQL Defrag allows you also to export the alerts generated from the index defragmentation policies to different files types, as shown below:

If you are interested in the general details about the index defragmentation operations, the Activities page provides you with a general message about each defragmentation operation, as shown below:

To validate that the defragmentation policy works fine and performed the configured reorganize and rebuild operations, you can go back to the Fragmentation page and refresh and review the fragmentation percentage of the target indexes, which is reduced to the minimum value, as shown clearly below:

You can see from the previous examples, how you can easily review the fragmentation percentage information for all indexes created under a specific SQL Server instance, at the instance, database, table or index levels, and fix the fragmentation issues manually or automatically by scheduling policies, with the ability to monitor the status of these policies and generate reports for the fragmentation information and policies alerts, all from the same location!

Conclusion

We covered how to create an index defragmentation job to mitigate the index fragmentation issue and monitor the status and result of that job. I hope this article was useful as a resource for your learning and understanding to rebuild or reorganize indexes.