However, because of Page Split, the pages turn into out-of-order pages. Every index page is linked with previous and next page in the logical order of column data.

Index pages also maintain a logical order of pages inside the extent. When the extents of a table are not physically stored contiguously on disk, switching from one extent to another causes higher disk rotations, and this is called Extent Fragmentation. External Fragmentation: When on disk, the physical storage of pages and extents is not contiguous.This unused space causes poor cache utilization and more I/O, which ultimately leads to poor query performance. As these modifications are not equally distributed among the rows of the table and indexes, the fullness of each page can vary over time. This fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and therefore, to the indexes defined on the table. In other words, internal fragmentation is said to occur if there is unused space between records in a page. Internal Fragmentation: When records are stored non-contiguously inside the page, then it is called internal fragmentation.We can classify fragmentation into two types:

Before learning to eliminate fragmentation, you should have a clear understanding of the types of fragmentation.

Storing data non-contiguously on disk is known as fragmentation.

What is Fragmentation? How to detect fragmentation and how to eliminate it?Ī.

Avg clear reports