Though I am comfortable with external fragmentation, There was always element of confusion with internal fragmentation. I just learned from Pinal's blog about the difference between these two types of fragmentation.
Internal Fragmentation: When records are stored inside a page in non-sequential order, then it is called internal fragmentation. If there is a lot of space between records in a page then it will be referred as internal fragmentation. Few reasons for this is,
a) Because of delete statements. Assume a page has records, If 2nd record is deleted, there will be gap between 1st record and 3rd record.
b) Because of page splits caused by insert/update/delete statements.
This results in poor utilization of space, thus poor cache.
External Fragmentation: If pages allocated to table are non-sequential, it is called external fragmentation. Because of this, while reading pages, query engine needs to switch between extents multiple times, which causes more delay. Some of reasons for this are below
a) While allocating pages for new tables, If pages are less than 8, query engine will allocate a page from mixed extent(already allocated to other extents), If table has more than 8 pages, then it will allocate a entirely new extent.
b) When all records are deleted from pages; entire page will be de-allocated. Because of that, there will be gap between the pages, which increases number of fragments count.
c) While allocating a uniform extent, next sequential extent might be already allocated to other table.
SQL Server Tips · · · ·