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;
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.