SQL Server Fragmentation: What it is, what are it’s types (Part 1)

In this post, we’ll talk about what fragmentation is, what are its different types in SQL Server, how it impacts performance (and when it doesn’t).

This is the first post in the multi part series. In the following posts in this series, we’ll see what causes fragmentation part-2, how to check for it(using DMVs) part-3, how to address it(remove it) part-4, and how to avoid it(control it) part-5.

Logical Fragmentation

  • Occurs when the next logical page is not the next physical page
  • Effects on Disk I/O:
    • Prevents optimal read-ahead
    • Reduces range scan performance
  • Effects on Memory usage:
    • Does not affect pages that are already in cache
    • Smaller indexes affected less (e.g 1000 pages or less)
  • Reported as follow link avg_fragmentation_in_percent in the DMV  watch sys.dm_db_index_physical_stats DMV in SQL server 2005+

Physical (Internal) Fragmentation

  • Occurs when page is not filled optimally with rows which results in wasted space
  • Effects on Disk I/O:
    • Increased disk space (more pages required to hold the same number of rows)
    • Increased I/Os to read the same amount of data
  • Effects on Memory usage:
    • Increased memory usage (more pages required to hold the same number of rows in memory also)
  • Reported as source site avg_page_space_used_in_percent in above mentioned DMV