Over time, the indexes applied to tables in a SQL Server database can become fragmented because of row insertions, updates and deletions. This fragmentation can severely impact the performance of the database so should be rectified periodically.
When you add indexes to a table or view you allow some queries to execute faster and improve the performance of your software. An index holds key data for the underlying table in a structure that allows values to be found quickly. Instead of scanning through all of the rows in a table to find the items that match a given WHERE clause, the much faster index is checked instead and used to look up the appropriate rows. This is similar to using the index at the back of a reference book to find a specific topic, rather than reading the entire text.
Indexes consist of a series of fixed-size pages, each holding a number of index entries. For new indexes added to empty tables, the pages start empty. If you add an index to a table that already contains data, most of the pages in the new index will be full of data and arranged in the logical order of the pages matches their physical order. This is the most efficient organisation. NB: The pages will usually not be entirely full. Depending upon the fill factor, SQL Server leaves a certain percentage of each page empty to allow for growth.
Whenever you insert, update or delete table rows in a manner that affects an index’s contents, the index is updated to remain synchronised with the underlying table. When you delete rows, this leaves empty space within the pages of an index. Updating rows can leave empty space too, as changing the indexed data may move entries between pages. Even inserting rows can cause empty space, as SQL Server will sometimes perform page splits. This is where approximately half of the rows from a page are moved into a new page to create room for new rows. Having empty space within index pages is called internal index fragmentation.
When you insert new rows into an indexed table they are added the index. Updating rows may also cause new index entries. This may cause SQL Server to create new index pages. Inserting the new pages into the ideal physical location in the index would necessitate the relocation of existing pages, which would be a very inefficient process, so new pages are added elsewhere. This leads to differences between the physical and logical ordering of the index pages. This is called external index fragmentation.
Fragmentation, be it internal or external, harms the performance of your indexes, primarily due to increased I/O operations. Low levels of fragmentation may slow queries by a few percent. However, when an index is very fragmented it can cause queries to run many times slower that with a freshly generated index. To minimise this impact you should remove reorganise indexes with light fragmentation and rebuild those with heavier fragmentation. We’ll see the difference between the two operations later.
Determining the Fragmentation Level
Before you decide to defragment an index you should determine how badly affected it is. This aids you in selecting the most appropriate action. Fragmentation is measured as a percentage that indicates the number of pages that are not in the ideal order. A value below 5% indicates a very lightly fragmented index that requires no maintenance. A value of between 5% and 30% can be improved by reorganising the index. Greater than 30% fragmentation warrants an index rebuild.
We can find the amount of fragmentation for an index using the dm_db_index_physical_stats function, passing the IDs for a database and a table. The fragmentation percentage is returned in the avg_fragmentation_in_percent column of the results. The function links the percentage to the object ID for the index. You can find the name of the index by looking it up in the sys.indexes view. The following script combines the two data sources with an inner join to list the indexes and associated fragmentation levels for a table. In this case we are reading the Employees table in the Test database.
The results for the above script appear similar to the following. You can see that in my test table the fragmentation of the PK_Employees primary key index is acceptable but the 8.27% value for the IX_Names index warrants reorganisation.
name FragmentationPercent ----------------- -------------------- PK_Employees 0.49 IX_Names 8.27
NB: For small indexes on tables that are updated often, fragmentation can become high very quickly. Although this affects performance, it is likely that the overall execution time of queries for small tables or indexed views is small. You will still improve performance by reorganising or rebuilding these indexes but you may find that they fragment again quickly.