Pages

cross

Tuesday, December 08, 2015

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

A. Storing data non-contiguously on disk is known as
fragmentation. Before learning to eliminate fragmentation, you should have a
clear understanding of the types of fragmentation. We can classify
fragmentation into two types:

Internal
Fragmentation: When records are stored non-contiguously inside the page, then
it is called internal fragmentation. In other words, internal fragmentation is
said to occur if there is unused space between records in a page. 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. 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 unused space causes poor cache utilization and more
I/O, which ultimately leads to poor query performance.

External
Fragmentation: When on disk, the physical storage of pages and extents is not
contiguous. 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.