Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file.
There are three types of fragmentation:
File fragmentation at the operating system level
When deletes and inserts are performed over time, pages become fragmented as the physical sequence of data pages no longer matches their logical order. This fragmentation happens at the file allocation level and can be addressed with system tools. On larger systems, such as a storage area network (SAN), the disk subsystem automatically maintains low fragmentation levels. If you have a small to medium size system and you do not have a SAN, you should run a system defragmentation tool before addressing logical order and page density fragmentation within SQL Server.
Logical order fragmentation
This issue, also known as external fragmentation within SQL Server, is similar to file fragmentation at the operating system level. When data is deleted, inserted, and modified over time, an index can cause pages to be out of order, where the next logical page is not the same as the next physical page.
Page density fragmentation
This issue, also known as internal fragmentation, occurs as pages split to make room for information added to a page, there may be excessive free space left on the pages. This extra space can cause SQL Server to read more pages than necessary to perform certain tasks. SQL Defrag Manager defragments the leaf level of an index so the physical order of the pages matches the left-to-right logical order of the leaf pages. The leaf pages of a clustered index contain the table data. This process improves index scanning performance and all data retrieval activities.
REFERENCES
