I read the BOL on this, but not sure what it is.
Here is what I am trying to do. I am trying to implement an index maintenance procedure that uses the output from sys.dm_db_index_physical_stats to determine if an index on a table should be defragmented.
So, I run a statement like this to see what the fragmentation stats look like on an index.
from sys.dm_db_index_physical_stats (@db_id,@table_id,@index_id,null,'LIMITED')
When I do this, on certain indexes, I get back more than 1 row differentiated by something called alloc_unit_type_desc. Now, reading BOL, it sounds like this has something to do with the data types of the keys in your index. The issue is, I want 1 row of
avg_fragmentation_in_percent to determine if I should defragment. But when I have an index that returns multiple avg_fragmentation_in_percent due to this alloc_unit_type_desc, I'm not sure what the approach should be. May be I should just look at the max avg_fragmentation_in_percent
among the various alloc_unit_type_desc?
View Complete Post