In BOL, it talks about ignoring index fragmentation below 5%, but out 'in the real world', what sort of levels of index fragmentation should be considered to be significant and hence worth worrying about. Do you find that you need to have things in place to actively re-organise indexes in some of your tables, or is that usually a sign that something else is wrong?
Tony Davis discusses this in depth at http://www.simple-talk.com/community/blogs/tony_davis/archive/2009/10/01/75072.aspx .
But the bottom line is the standard answer of "It Depends". If you really want a rule of thumb that does not take much thought, then I think around 10% is the traditional answer and as close to correct as you are likely to find. If you truly want to optimize though, you need to look at your specific situation. If you have a huge amount of maintenance time when there is low-to-no other server activity, then rebuild/reorganize aggressively to minimize the fragmentation. If maintenance time is at a premium then you may want to allow relatively high (30% or even higher) before even dreaming of taking major action.
And if you have a write-heavy database, ensure you give it an adequate fill factor, otherwise write performance will be affected massively.
The only thing i'd add to the above is that the enterprise editions of 2005 and 2008 support online indexing, which means you can re-index during periods of low activity without blocking access to the table that the index references...
Ken Simmons covered this in his SQL Saturday presentation on automating routine maintenance. In his slide deck he showed that if fragmentation is > 5% and < = 30% then REORGANIZE. If it's > 30% REBUILD
You can see whole slide deck + his scripts here:
*note: when you download the file, rename the file and add .zip to end of it. For some reason download stripped data type. The zip file contains his powerpoint and sql scripts. Any questions about anything in his deck contact Ken at http://cybersql.blogspot.com
