SQLGeordie's Blog

Helping the SQL Server community……where i can!

Poll: – Index Rebuild / Defrag, what page count filter do you set? — March 11, 2011

Poll: – Index Rebuild / Defrag, what page count filter do you set?

After reviewing a number of clients overnight index defrag / rebuild scripts over the years, I’m intrigued to know how many of us actually set the page count filter when capturing the fragmentation levels.

I know there’s a lot of debate around the value to set and personally I set mine to 100, mainly because (in most circumstances) the client has no requirement for a separate update statistics job and therefore its down to the autostats update (if we’ve determined its required to be on) or indeed the overnight index defrag/rebuild job to rebuild them and even though 10000 pages isn’t a great deal on some systems for the stats to be out of date, as a rule of thumb I’d rather play it safe and defrag/update stats on tables with pages > 100.

So, interested to see what everyone sets and if enough interest I’ll blog the results 🙂

I’d appreciate any comments to supplement your selection too….

Advertisements