Would it be possible te build an internal databases with an history of the fragmentation of each index?
that way you could easily see if an index gets a lot of rebuilds.
that would help a lot to determine if an index would be beter of with a lesser fillfactor.
or an index that never needs rebuilding could do with a higher fillfactor.
we have stored procedues in place that check each day if indexes need defragmentation/rebuild, and when an index is rebuild 25 days in a month, the fillfactor is decreased by 5% ( 20 days = 4%, 15 = 3%, 10 days is 2%) . when a index is not rebuild in 30 days, the fillfactor gets 1% higher.
this stabilizes in about a year, and gave us a reducement of IO load of about 15% total.
if you dont like the idea, just remove this request :-) its just a thought.
Would it be possible te build an internal databases with an history of the fragmentation of each index?
that way you could easily see if an index gets a lot of rebuilds.
that would help a lot to determine if an index would be beter of with a lesser fillfactor.
or an index that never needs rebuilding could do with a higher fillfactor.
we have stored procedues in place that check each day if indexes need defragmentation/rebuild, and when an index is rebuild 25 days in a month, the fillfactor is decreased by 5% ( 20 days = 4%, 15 = 3%, 10 days is 2%) . when a index is not rebuild in 30 days, the fillfactor gets 1% higher.
this stabilizes in about a year, and gave us a reducement of IO load of about 15% total.
if you dont like the idea, just remove this request :-) its just a thought.