My Dear Friend Sean recently wrote a post about how people mismanage indexes . I'm going to go a little further and talk about the fact that the way your index serving script evaluates index fragmentation is wrong.
If you look at how your script decides whether or not to rebuild the indexes, and the same goes for the maintenance plans (I ran PROFILER YES HELLO PROFILER FORWARD PROFILER to check), you can see that they query sys.dm_db_index_physical_stats .
All of these queries use the avg_fragmentation_in_percent column to determine if the index needs to be rebuilt. The documentation (at the link above) about this column has the following to say:
It is a measure of logical fragmentation. Logical fragmentation is when pages are "shuffled" on disk.
, SAN, , , - , . , .
, , , - . , , , .
avg_page_space_used_in_percent.
...
, , dm_db_index_physical_stats LIMITED. , , , , LIMITED .
, avg_fragmentation_in_percent avg_page_space_used_in_percent, .
Stack Overflow 2013:
, , , , Posts, , .
Comments, avg_page_space_used_in_percent , Posts 10%.
Comments , 500 Posts.
, . 500 .
, - , .
OLTP, , , , , .
?
5% 30% . , , , . 84% "" , 75%.
, .
, fill factor , .
, !
. Erik Darling Brent Ozar , , , , . Paul S. Randal Paul White, , .
/, , . , dba - .