What do Microsoft mean by Internal vs External fragmentation in SQL Server?

A. Internal fragmentation is having free space on a page. Due to deletes/updates/inserts you might have more free space on a page than you "need".

External fragmentation is when pages are not all nice and neat on contiguous extents. The ideal is to have one extent, fill it up, then get another, fill it up, get another, etc. When you have page splits, you have to link in a new page from another extent. So if you followed the page chain, you would be hopping around from extent to extent far more than you should. Ideally, you have one extent switch every 8 pages; the degree to which you have more than this determines the external fragmentation.

To show the level of fragmentation in a table use the dbcc showcontig command.

Microsoft do not mean filesystem/disk fragmentation when they say external. This sort of fragmentation occurs outside the knowledge of SQL Server. (Though this is still a sensible definition of external and hence the confusion).

(Info from Kalen Delaney)

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.