A couple of questions arrived regarding my earlier post about fragmentation on the system tables, and since the questions really don't have anything to do with system tables, I felt they deserved a new post.

1) Using the DETAILED option includes the non-zero index-levels as well (non-leaf pages).  According to many source, this should also be actioned, seeing that this indicates "logical" fragmentation (meaning that the leaf pages are not in the order of the non-leaf keys any more).  I cannot find anything conclusive on this.  Where as the index level=0 occurrences, indicates extent fragmentation.  We are using this dmv, to check fragmentation-levels first, and based on that, do the necessary re-index/reorg.  Currently we are using the LIMITED option (which only shows leaf-level), but we seem to be missing indexes that needs to be re-indexed/re-orged.

There are two  misunderstandings reflected in this question. Although there are several types of fragmentation, the value in the avg_fragmentation_in_percent represents only logical fragmentation, in any level of any index. Yes, there can be logical fragmentation in the upper levels, which may need to be addressed in some very large tables. Extent fragmentation is only reported for heaps, and once you have a clustered index, the table itself is just the leaf level of the index.  So basically, there is no difference between the meaning of the avg_fragmentation_in_percent value no matter what level you are at. However, fragmentation is really only an issue if there are LOTS of pages. I usually say that if there are less than 100 pages, you should never worry about fragmentation. However, this this whitepaper suggests that a better cutoff would actually be 1000 pages:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

It would need to be a very big table to have upper levels of indexes with more than 1000 pages, and the sys.dm_db_index_physical_stats view does show you the number of pages at each level.

The second misunderstanding involves what LIMITED means. LIMITED does not ONLY show leaf level, in fact, just the opposite. LIMITED does not evaluate pages at the leaf level at all. It can still determine the logical fragmentation because of the pointers in the upper levels, but LIMITED will return no values that require actually examining the leaf level rows. This includes values like min, max and avg record size, ghost and forwarded records counts, and the value for avg_page_space_used_in_percent. If you just want to see logical fragmentation to know if defrag or reorg might help, LIMITED should be fine.

2)   Secondly, the stats that this function return, does not seem to be reliable.  I ran the function prior to an ALTER…. REBUILD, and then again thereafter.  There were hardly any differences in the stats.  Is it now a case of the stats being unreliable, or the REBUILD not being effective?

REBUILD doesn't seem to make a difference in the avg_fragmentation_in_percent value is when there are too few pages in the table.