SQL Server - Checking Index Fragmentation - Understanding LIMITED, SAMPLED and DETAILED

Indexes get fragmented based on the operation we perform against records and modification we do against index keys. We use one of the Dynamic Management Function which is called sys.dm_db_index_physical_stats for checking both Internal Fragmentation and External Fragmentation. Today, I had to check one my clients data table for fragmentation which is a very large table. Since this is a quick look, I decided the use SAMPLED mode for checking the fragmentation instead of my usual mode DETAILED. Once the check is done, we had a short-conversation on the mode selected; difference between them and why should use them. This conversation resulted this post.

If you need to know Internal and External Fragmentation with a sample code, please see my post: Understanding Index Fragmentation – SS SLUG Aug 2013 – Demo I.

Here are the differences between three modes;

Mode	Details
LIMITED	Fastest way of scanning the index for fragmentation. For B-Tree indexes, only the Parent Level Pages are scanned. For Heaps, associated PFS and IAM pages are checked and data pages are scanned. This cannot be used for checking Internal Fragmentation. This still shows External Fragmentation because it uses Pointers to the Leaf Level in Parent Pages for calculating the External Fragmentation.
SAMPLED	Slower than LIMITED as it scans leaf pages too. It uses 1% of all pages for scanning, hence the fragmentation values are approximate. If the number of pages are lesser than 10,000, then DETAILED mode is used instead of SAMPLED mode.
DETAILED	Slower than LIMITED as it scans leaf pages too. It uses 1% of all pages for scanning, hence the fragmentation values are approximate. If the number of pages are lesser than 10,000, then DETAILED mode is used instead of SAMPLED mode.

The reason for me to use SAMPLED mode for checking is, the number of records it has. Since it takes long time for scanning all pages, I decided to use SAMPLED mode because it could help to me determine whether the index is fragmented or not.

This shows how the fragmentation is shown with all three modes;