I have a scheduled proc for re-indexing indexes. I'm trying to modify the proc to only re-index what is necessary. To do this, I capture DBCC SHOWCONTIG output to a table every day. The aim is to modify the index maintenance proc to check if an indexes 'scan density' (or other measures of fragmentation) is below a certain threshold. eg. below 70% before a re-index is done. That's all fine but I've since been looking at questions surrounding clustered vs. non-clustered indexes and how they might differ with regard to measuring fragmentation.
I've read somewhere that 'logical fragmentation' can be ignored for clustered indexes (or was it for non-clustered indexes?). Basically, I'm trying to establish a small set of rules like 'if it is a clustered index, evaluate scan density only', 'if it is a non-clustered index, evaluate both scan-density and logical fragementation' and so on.
What I've noticed with regard to index fragmentation is that sometimes I can get very high figures for 'logical fragmentation' when BOL would advise 0 to 10%. However, even when logical fragmentation looks bad, scan density can look good. Therefore, might it not be true that scan density as a measure isn't good enough on its own. Although I realise that there isn't a perfect formula, is there a good 'general rule' I can use?