Monday, December 29, 2008

How sys.dm_db_index_physical_stats - LIMITED shows the leaf level fragmentation?

This question of raised by one of my students in my new batch, excellent question. We all know that when we use LIMITED mode, it scans only the pages above the leaf level. If that is the case, how does it report the fragmentation of leaf pages (index_level = 0)? I could not answer immediately, so, started digging it with many articles, many blog posts. Most say what BOL says, some had some additional, valuable points too. Finally I found the answer from Kalen's blog. The reason is the pointers in the upper level. It uses pointers to calculate the fragmentation of leaf level without scanning the leaf level. LIMITED mode does not scan leaf level at all. Great. One more question to be answered. Will blog once I solved that too.

No comments: