How to detect a high level of SQL Server database index fragmentation and fix it automatically


When working with databases, availability and performance are of paramount importance. Performance of a database is based on multiple factors, key among them is being able to process queries quickly. That can be facilitated using indexes. Indexes are like the table of contents in a book, providing shortcuts to where the information is located. Otherwise, you would have to start reading the book from start until you find the information neededIt’s the same with databases, if it weren’t for indexes, for every query there would need to be an entire table scan.

Looking at indexes, when inserting new data, pages of indexes get filled up gradually. The Fill factor comes into play here, as it’s value, in percentage terms, determines how much each page will be allowed to get filled. Setting to fill factor to 0 or 100% (effectively the same thing), means that once filled, there will be no empty space left in that page for additional data. When another piece of data needs to get inserted into a page, which is completely filled, an event called ’page split’ is carried to adjust for the new data. What a page split does is to split the existing filled page into two new pages which are half filled. The new piece of data is going to be inserted in it’s logical place. However, the SQL Server now has more pages to read, which has an impact on performance and higher usage of memory, CPU and IO bandwidth.

Getting the right Fill Factor is not easy. It depends on the type of environment. If you have a static system, or read only system, where no changes occur, then the Fill factor of 100 is perfect. However, if there are lots of transactions, inserts, updates, deletes, than a Fill Factor of around 70 to 90 is preferred for best results. But, in the end, it all depends on the individual system, user preferences and the amount of IO.

Index fragmentation in a SQL Server database occurs in two forms, internal and external fragmentation. Internal fragmentation means that the index is bigger than it really needs to be. It’s caused by operations which leave the pages unfilled, and results in records being saved on new pages instead of being saved in the empty spaces on the existing pages, leading to a bigger index and slower performance, as the server needs to read more pages.

External fragmentation in a SQL Server database means that the logical order in an index does not match the physical order on the disk. When the pages are scattered on the disk, it takes time for the heads of the magnetic hard drives to jump from one page to another, which significantly decreases performance and overall longevity of the hardware. In some cases, random reads on a magnetic hard drive can be as much as 100 times slower than sequential reads.

When dealing with fragmented indexes, there are two options available to us for fixing fragmentation. Those are Index Reorganization and Index Rebuild. A common practice is to Reorganize when index fragmentation is between 10-30%, and Rebuild when fragmentation gets above 30%. However, it’s up to the user to ultimately choose the percentage at which to run Reorganization and Rebuild.

There are a couple of ways to automatically fix SQL Server database index fragmentation:

  1. Reorganize/Rebuild SQL Server database indexes using SQL Server Maintenance Plans
  2. Reorganize/Rebuild SQL Server database indexes using ApexSQL Defrag

Reorganize/Rebuild SQL Server database index using SQL Server Maintenance Plans

Microsoft SQL Server Management Studio is a environment for Microsoft SQL Server. For the purpose of this article, we will have an AdventureWorks database loaded into our SQL server.

  1. After connecting to the server, expand it and after that the Management folder. Right click on Maintenance Plans and select Maintenance Plan Wizard

  2. When the new window opens, click on Next

  3. In the next window, enter a descriptive name and optionally enter a description for the maintenance plan. Additionally, select Separate schedules for each task a,s in this scenario we want to reorganize indexes on a daily basis and rebuild them on every third day and after that click on Next

  4. In the following window, select Reorganize Index and Rebuild Index and go to Next

  5. In the following window just go to Next

  6. In the Define Reorganize Index Task, under Databases in the drop-down menu we can select the databases we want to work upon

  7. After selecting the database we want to work on, we can select the Scan type and select the conditions when the index should be reorganized. We can select the fragmentation percentage, page count and if it’s been used in the last set number of days, after that click on Change… to setup a schedule when to run the Reorganize Index Task

  8. In the New Job Schedule we define when to run the Reorganize Index task. After defining the schedule, click on OK and select Next to move on to the next window

  9. In the Define Rebuild Index Task window, we define the Free space options which is the Fill Factor. We can leave it at Default free space per page, which leaves the Fill Factor for every Index the same as it was, or we can Change free space per page to: a set percentage, which changes all indexes to the same Fill Factor, which is not recommended in larger databases with different indexes, some of which more, and some less often updated. Under Advanced options we can choose to Sort results in tempdb, Keep index online if it is supported, define maximum degree of paralellism, and define Index Stats Options much like in the previous window

  10. In the following window we can choose to write a report file and save it in the predefined location or browse for our own or send an email report. After selecting that, click on Next

  11. In the next window, we review the created tasks, and check if everything is correct. If there are additional changes to be made, click on Back and revise the changes. If everything is correct, click on Finish

  12. In this window, the tasks are created, and we are shown if everything goes without errors. After that, we click Close, and our Maintenance Plan is created

    Maintenance Plans are a good option to set and forget. However, unless specifically choosing the indexes to run the Plan on, it will run against all of the indexes in the selected database which are over 10% fragmented and 30% fragmented, reorganizing or rebuilding them, respectively and the interface for choosing them could be more user friendly.

Reorganize/Rebuild SQL Server database indexes using ApexSQL Defrag

ApexSQL Defrag is a 3rd party tool made specifically to automatically fix SQL Server database index fragmentation. With it, we can analyze for index fragmentation, rebuild and reorganize fragmented indexes, create and manage custom policies and schedules for defragmentation jobs, monitor for index fragmentation across multiple servers, create HTML reports and export all activities to CSV or XML files.

To have ability to detect SQL Server database index fragmentation, it’s first required to set up threshold options to our needs.

We can do that by going to Configuration in the top bar and selecting Options in the ribbon bar. In the Options window, after clicking on Thresholds, we can manually select the fragmentation percentage and appropriate colors from the dropdown color picker

Creating a schedule in ApexSQL Defrag to automatically detect SQL Server index fragmentation and reorganize/rebuild fragmented indexes can be done in a couple of simple steps.

  1. The first step is optional and can be skipped, but is recommended to perform in order to have an overview of the amount of fragmentation in the selected database.

    After selecting the database, click on Analyze. In the right hand side you will have a list of all the indexes with respective details. All of the column names are rearrangable, and we have here the fragmentation percentage listed just after the index name.

  2. After analyzing, in the bottom bar, click on Policies and after that on Create

  3. In the new window, enter an appropriate name and, optionally, a description for the new policy and make sure the policy is enabled by checking the box to the left of Enable policy. After that, press the button to the right of Targets to open a new window to select indexes upon which we want to run the policy against

  4. In Select Policy Targets window, after expanding the tree view, we can select which indexes we want to run the policy against. We can select the whole database, all of the indexes bound to a certain table, or individual indexes. After making the selection, click OK to continue

  5. Under Schedule, for Type we select Daily, specify the Time when to run the policy, and in the dropdown menu for Days we can select on which days will the policy run

  6. After clicking on Thresholds in the navigation tab to the left, we are presented with options to select when to reorganize indexes, when to rebuild, and fragmentation scan density. Under Resources threshold we can choose to check for available resources needed to perform the policy, optional retry and exit as failed options.

  7. In the third panel selected from the Navigation tab to the left, Notification, we can select if we want to be notified by email whether the job is successful or not. We can add email addresses by clicking the Add button. After finishing the setup of our job, click on OK to create it.

    Note: To add an email address, you have to first setup an email account in Configuration tab of the application.

  8. After creating the job, we can view it in the Policies tab of the bottom bar

  9. The policy will now run automatically at the scheduled time. However, if we want to run it on demand, we can do that manually by selecting the policy we want to run, and either right clicking on the policy and selecting run or pressing the Run button in the ribbon above.

  10. After the policy finishes running, ApexSQL Defrag will notify you that it has finished. You can look at the notifications and see if the policy was successfull or not by clicking at the Agent icon.

  11. Alternatively, you can look at all the activites by selecting Activities in the bottom bar.

Related links:


February 8, 2017