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.
Using SSMS
Designing and creating an optimal index that enhances the performance of your queries is not an easy task. But the mission will not complete at that point. You should monitor that index usage and maintain that index in order to keep it useful for the queries. This is due to the fact that all changes that are performed on the underlying table will be replicated to all related indexes to keep the index updated. Over time, and after many changes, the index pages become split and fragmented, and the index becomes less useful and degrade the performance of the query, instead of enhancing its performance.
The fragmentation percentage for all the table indexes for a specific database, can be retrieved by the T-SQL script below, that queries the sys.dm_db_index_physical_stats and sys.indexes system objects, as shown below:
SELECT OBJECT_NAME(IDX.OBJECT_ID) AS Table_Name, IDX.name AS Index_Name, IDXPS.index_type_desc AS Index_Type, IDXPS.avg_fragmentation_in_percent Fragmentation_Percentage FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IDXPS INNER JOIN sys.indexes IDX ON IDX.object_id = IDXPS.object_id AND IDX.index_id = IDXPS.index_id ORDER BY Fragmentation_Percentage DESC
And the result from the AdventureWorks2017 demo database will be like:
In order to mitigate the index fragmentation issue, we need to create an index defragmentation maintenance plan, to reorganize and rebuild the highly fragmented indexes, based on the index fragmentation percentage.
To achieve that, expand the Management node of the SQL Server Management Studio Objects Explorer, right-click on the Maintenance Plans node and choose the Maintenance Plans Wizard option, as shown below:
In the Maintenance Plans wizard, provide a unique and meaningful name for the index defragmentation maintenance plan that indicates the purpose of the plan, define a schedule to specify when and how often the indexes maintenance plan will be executed, the tasks that will be executed within the maintenance plan, which is Rebuild index and reorganize index in our demo here, the execution order for the maintenance plan tasks, the configuration options for each index maintenance task and the location where to keep the job execution logs, as shown below:
After executing the index defragmentation maintenance plan, we can check the status of the maintenance plan by checking the status of the SQL Agent job that is used to execute the maintenance plan tasks. To check the maintenance plan agent job, open the Job Activity Monitor window, and check if the job is enabled, executing or idle, the last run date and time and result. Besides, right-click on the maintenance plan agent job and choose View History option to check the execution history of that SQL Agent job, and fix any issue that stops that job using the provided error message, as shown below:
The previous index defragmentation maintenance job history can help in showing if the job is executed successfully or not, without showing if the indexes are really defragmented. To check if the fragmentation issue of the indexes is fixed or not, we need to run the previous T-SQL scrip again and review the fragmentation percentage of these indexes, without having all the information updated automatically and displayed at the same place.
Also, if we plan to create multiple index maintenance jobs to customize the scheduled time or the fragmentation percentage for each database, we can check the status of the jobs from the Job Activity Monitor, but we need to check the history or each job separately and validate the fragmentation percentage again using the previous T-SQL script multiple times and per each database.
Using ApexSQL Defrag
Overview
ApexSQL Defrag is a SQL Server index monitoring and analysis 3rd 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.
April 7, 2020
