Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

SQL Server - Difference between Internal Fragmentation and External Fragmentation

Jul 20 2011 1:50AM by Ramireddy   

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.

Read More..   [178 clicks]

Published under: SQL Server Tips ·  ·  ·  · 

3 · 40% · 12972


Your Comment

Sign Up or Login to post a comment.

"SQL Server - Difference between Internal Fragmentation and External Fragmentation" rated 5 out of 5 by 5 readers
SQL Server - Difference between Internal Fragmentation and External Fragmentation , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]