Fragmentation

What is Fragmentation?

It is a general term used to describe various effects that can occur in indexes because of data modifications. There are two general types of fragmentation Internal and External.

Internal Fragmentation:

Internal Fragmentation often called physical fragmentation or page density.
Internal fragmentation is wasted space on index pages, both at leaf and non leaf levels.
 It occurs because of

•  Page splits leaving empty space on the page that was split and newly allocated page
• Delete operations that leave pages less than full

Internal Fragmentation means the index is taking more space than necessary leading to increased disk space
usage, more pages to read to process the data and more memory used to hold the pages in buffer pool.

External Fragmentation : It is where the pages or extents comprising the leaf level of a clustered or non clustered index are not in an efficient order.
Efficient order means where the logical order of the pages and extents is the same as the physical order of the pages and extents with in the data files.
It occurs:
Due to page splits and reduces the efficiency of ordered scans of part of a clustered or non clustered index