Internal and External Fragmentation in SQL Server

A non-contagious storage of data is called fragmentation. Fragmentation adversely affects the performance of the SQL Server. There are mainly two types of fragmentation.

1.       Internal Fragmentation

Internal fragmentation occurs when data is stored non-contiguously inside a page. This means there are unused space between the records in a page. Insert, delete and update operations fill the page and overflow of data causes a “page split”. Splitting causes a full page to divide evenly, inserting half of its data on a newly allocated page. Internal fragmentation decreases page density and as a result it is very difficult for the server to search and find the data.

 

 

2.       External Fragmentation (Logical Fragmentation)
 

When the extents of a table are not physically stored as per the logical order external fragmentation occurs. That means next page pointer does not point to the next physical page. This can be identified at the avg_fragmentation_in_percent in sys.dm_db_index_physical stats. This value is not relevant if you have a small number of pages and if all pages are already in the buffer pool and logical IOs are not necessarily physical IOs.