I am in the process of revamping my index rebuild routine. It stores the Fragmentation (into a history table) before reorganising / rebuilding an index [if it is fragmented]. I find the historical information useful, but I am using
DBCC SHOWCONTIG (MyTableObjectID) WITH ALL_INDEXES, TABLERESULTS, FAST
which doesn't give me the record size stats etc.
So two questions:
Should I switch to using sys.dm_db_index_physical_stats instead of DBCC SHOWCONTIG?
Should I stop using FAST (for DBCC SHOWCONTIG, or if sys.dm_db_index_physical_stats then use DETAILED instead of LIMITED) in order to get the additional information because I suspect that would also be useful to see retrospectively / historically?
Clearly I need to worry about collecting DETAILS stats on large tables, but we run this overnight and I'm not sure I care (within reason!) how long it takes. Perhaps I should set a threshold ROWCOUNT and use FAST / LIMITED on tables that have more rows than that?