In an MS-SQL Server database, the data is stored in pages where each page is 8 KB wide and 8 contiguous pages form an extent.
The following image shows how the pages are linked together via a B-tree index structure.
A B-tree index structure (where the letter ""B" stands for balanced) is a multilevel structure that has Root, Intermediate, and Leaf levels. Pages linked between the levels allow index seek operations. At the Leaf level, a "doubly linked list" links the pages. This link is used to scan an index.
We can see that this is a clustered index because at the Leaf level there are data pages. This means that:
- Physical data is stored in the clustered index
- Clustered indexes define the logical order of the table
A non-clustered index, on the other hand, has the same B-tree structure but points to the clustered index for its data.
What is Index Fragmentation?
Fragmentation is a natural phenomenon and is defined as a condition of not having contiguous data. It is due to the continuous operations of inserting, updating, and deleting data in the database.
We can say that a B-tree index is fragmented when there are pages where the logical ordering (within the index, based on the key values of the index) does not match the physical ordering of the index pages.
For example, when we add rows to a table, the index becomes fragmented (also called scattered). The database engine automatically modifies the indexes involved as well. This could cause existing pages to split to make room for new rows.
If we insert a new row in a page but there is not enough space, a new data page or even a new extent is allocated, while part of data from the existing data page is moved to the newly allocated data page. To maintain the logical sorting order in the index, pointer on both pages is updated.
There are two types of index fragmentation:
- Logical fragmentation - when the logical order of the pages does not match the physical order.
- Internal fragmentation - when data pages in the index contain free space.
From a performance standpoint, logical fragmentation leads to an increase in the number of physical reads. Internal fragmentation leads to an increase in logical reads.
Updating the fields in a table could also generate fragmentation. It can happen that when we write more data, one page of data fills up and another is needed.
How can we solve the problem?
To reduce fragmentation, we can perform one of the two Microsoft developed tasks.
1. Reorganize Task
The reorganize task moves the index pages into a more efficient search order. To do this:
- Squishes the index rows together to try to deallocate some index pages.
- Swap the remaining pages in small transactions until all the pages are in logical order. At the end of the process, physical order of the pages is same as the logical order.
The result will produce logically ordered pages and not physically contiguous pages. This approach is very space efficient because it only requires an 8 KB page as temporary storage area for the pages that will be moved.
While the reorganize task is running, the underlying table or index is not locked. However, the task will take longer than a re-index if the indexes are logically highly fragmented. An important aspect of the reorganize task is that it is executed in a small atomic transaction.
When dealing with a huge index, we can stop the task rolling back only a little part of the work and then resume the task later.
2. Rebuild Task
The rebuild task operates differently from the reorganize task. This process builds a new index and drops the old one. This happens regardless of the fragmentation present in the older index. This way of proceeding means that we need to have enough space for the new index.
MS SQL Server has two different types of rebuild index tasks: Online and Offline. The Standard version of MS SQL Server supports only the Offline type, while the Enterprise and Developer versions also support the Online type.
The terms Online and Offline indicate whether the index can be kept accessible during the rebuild operation.
Offline Rebuild
- This operation moves pages to make them physically contiguous.
- This operation is atomic at single index level. This means if we interrupt the process, all the work on the index will be rolled back and lost.
- For the duration of the procedure, the index or table will be locked and unavailable for updating.
Online Rebuild
- The online index rebuild works exactly as the offline procedure.
- The major difference is that online rebuild locks the underlying index or table.
Defragmentation Strategy
Let's discuss how to get the degree of fragmentation and how to reorganize or rebuild a fragmented index by using SQL Server Management Studio (SSMS) or Transact-SQL.
Degree of Fragmentation
We cannot start defragmentation before knowing the degree of fragmentation. We can get this information by using the DMF sys.dm_db_index_physical_stats.
With the following statement, we can get the percentage of fragmentation for each index of each table:
After getting the fragmentation percentage, we can choose the best method to defragment the index.
Microsoft Books Online provides these values:
- If the fragmentation is less than 5%, there is no need for defragmentation.
- If the fragmentation is comprised between 5% and 30%, we should reorganize the index.
- If the fragmentation is greater than 30%, we should rebuild the index.
Defragment using T-SQL Command
Suppose, we have an OrdTes table with the index IDX_OrdTes_DateDoc.
We can reorganize the index using the first T-SQL syntax and rebuild the index using the second syntax (see the below image).
We can reorganize or rebuild all indexes of a table using the following syntax.
Defragment using SQL Server Management Studio (SSMS)
Here are the steps to reorganize indexes using the SSMS:
- In Object Explorer, expand the database that contains the table on which we want to reorganize an index.
- Expand the Tables folder.
- Expand the table on which we want to reorganize the indexes.
- Right-click the Indexes folder and select Reorganize All.
- In the Reorganize Indexes dialog box, verify that the correct indexes are in the Indexes to be reorganized. To remove an index from the Indexes to be reorganized grid, select the index and then press the Delete key.
- Select the Compact large object column data checkbox to specify that all pages that contain large object (LOB) data are also compacted.
- Click OK.
Here are the steps to rebuild indexes:
- In Object Explorer, expand the database that contains the table on which we want to rebuild an index.
- Expand the Tables folder.
- Expand the table on which we want to rebuild an index.
- Expand the Indexes folder.
- Right-click on the index and select Rebuild.
- In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK.
- Select the Compact large object column data checkbox to specify that all pages that contain large object (LOB) data are also compacted.
- Click OK.
Defragment Index in a Maintenance Plan
As mentioned earlier, there must always be a defragmentation strategy in place. It is necessary to schedule a maintenance plan that periodically carries out this activity. The maintenance plan will not only defragment the index but will also perform other important operations, such as statistic maintenance and database backup.
To create a new maintenance plan, from the Management menu, right-click on the maintenance item. Then, select the ""New Maintenance plan" item.
In a maintenance plan, we can add various tasks.
To perform the Reorganize index task, drag and drop the Reorganize index task to the center of the screen. To perform the Rebuild index task drag and drop the Rebuild index task to the center of the screen.
Once we have added one or both tasks, we should set them up.
To set up the Reorganize task:
- Specify on which databases that operation be performed.
- Choose to reorganize only the necessary databases or only the user databases or only the system databases.
- Choose to Reorganize the indexes only under certain conditions, such as above a certain percentage of fragmentation or above a certain number of pages.
To set up the Rebuild task:
- Specify on which databases that operation be performed.
- Choose to reorganize only the necessary databases or only the user databases or only the system databases.
- Choose to rebuild the indexes only under certain conditions, such as above a certain percentage of fragmentation or above a certain number of pages.
At the end of the configuration, save the maintenance plan and schedule it to run at a certain frequency.
To Conclude
It may happen that the database gets corrupted and returns errors during the defragmentation process. If the manual methods of error recovery don't work, I recommend you to try Stellar Repair for MS SQL software. This easy-to-use software allows to fully recover a corrupt database by parsing each table and each object of your SQL database.