What is Index Fragmentation in SQL Server?

• SQL Server index fragmentation definition

  SQL Server index fragmentation definition

  SQL Server index fragmentation occurs when the data pages in indexes are logically disordered, loosely filled, or overfilled. Locating and accessing physically non-contiguous data pages in the disk drive requires more server resources and time, resulting in increased query processing time and poor application and server performance.
• What causes index fragmentation in SQL Server?

  What causes index fragmentation in SQL Server?

  Indexes in SQL Server enhance query execution time and application performance. An SQL index is a small and organized lookup table, allowing the SQL Server engine to quickly locate the requested rows of a database table without scanning the entire table. It has its own disk space and is arranged in a B-tree structure. SQL Server allows the creation of multiple indexes for rapid data search and access operations in the database, such as clustered and non-clustered indexes.

  The B-tree structure of these indexes enables the SQL Server engine to search the requested data quickly. Every node in the tree is essentially a data page of eight kilobytes (KB), with the leaf node containing the actual data row or a pointer to the row in the indexed table. When you create a new index for a table, its fragmentation stays at zero percent. However, with frequent data alterations in this table over time, the issue of SQL Server index fragmentation arises. It consists of two types:

  • Internal fragmentation: happens when data pages in SQL Server have unnecessary data storage space or capacity. The extra space emerges due to various reasons. When you insert a new record with a data size less than a SQL Server standard data page, a space is created on the page.
    Conversely, when the size of the inputted information is more than eight KB, the remaining data is adjusted in one or more new pages, which again have blank spaces as the possibility of remaining information fitting perfectly into them is generally diminutive. Further, deleting records from the database also results in empty spaces on the data pages. Internal or page density fragmentation degrades the SQL Server performance as it has to scan more data pages to locate and display the expected information.
    
  • External fragmentation: occurs when the logical order of index pages varies from their physical organization in the data file. When you insert or update a record or data row in an already full table or index, SQL Server performs a page split and creates a new data page for storing the additional data. It moves some part of the information from the existing page to the new page. To keep the logical page order of the index intact, it also updates the pointers on the old and new pages.
    However, the physical page order becomes distorted as the newly added page is generally a part of some other extent, a group of eight physically adjacent pages of 64 KB. This creates logical fragmentation and negatively affects server performance. SQL Server must navigate multiple extents to read the index data, resulting in higher query response time.
• How to check index fragmentation in SQL Server?

  How to check index fragmentation in SQL Server?

  When you frequently insert and update data over a long period, the database size grows and data pages split, which leads to SQL Server index fragmentation. Before fixing the fragmentation problem with the help of a suitable defragmentation technique, it’s necessary to analyze the current level of fragmentation in your database. Outlined below are some of the ways to assess the fragmentation level:

  • Sys.dm_db_index_physical_stats: this dynamic management view (DMV), available in SQL Server 2005 and later, allows you to assess the physical health of indexes by offering statistics regarding their current fragmentation level. You can analyze the logical fragmentation percentage, the total number of records in an index page, and other vital metrics by executing this DMV.
  • SQL Server Management Studio (SSMS): is a popular tool to check and resolve index fragmentation problems. Leveraging the Object Explorer module of the SSMS, you can quickly check the number of levels and the degree of page fullness and fragmentation in index pages. It also helps defragment them through the rebuild or reorganize option.
  • SQL Server Database Maintenance plans: Microsoft offers these plans to simplify index maintenance operations. However, the problem with this solution is it rebuilds or reorganizes every index in the database, even when they have acceptable levels of fragmentation. The option to apply specific business logic like policy-driven defragmentation is not available. Some new features have been introduced in the SQL Server 2016 edition to manage the index fragmentation, but the customization options are still limited.
  • Database performance monitoring tool: is ideally the best option to quickly identify and resolve index fragmentation problems with minimum manual effort. It can automatically detect the index fragmentation across multiple databases and defragment them effectively based on specific policies or rules. For instance, you can set up a policy to automatically rebuild a particular index if it’s more than 30% fragmented. Advanced database performance monitoring tools also help schedule index maintenance activities and reduce the overall maintenance timeframe by executing concurrent defragmentation operations. You can also set up an optimal fill factor to reduce unnecessary page splits using such tools.

  After determining the index fragmentation percentage in the database environment, you can select a suitable defragmentation strategy to reduce it. When the fragmentation level surpasses 30%, you can choose the index rebuild strategy. Contrarily, for 11% to 30% fragmentation, reorganizing them is preferable. Nevertheless, these are general recommendations; choose defragmentation criteria based on your unique application requirements. Note rebuilding involves creating indexes from scratch by discarding their older versions, while reorganizing is all about reshuffling the leaf-level pages of indexes to create a harmony between their logical and physical order.