Search code examples
sql-serversql-server-performance

Why should I have to rebuild index which is having less than Avg. fragmentation is less than 1% to get performance?

Why should I have to rebuild index which is having less than Avg. fragmentation is less than 1% to get performance?

I have a table which used to update frequently,but when I see in depth and check for avg. fragmentation,which is used to less than 1 % even. When I rebuild that index, automatically performance has been improved.

I have joined this table with more than 2 tables in single query.

I want to know as per MSDN we should not do anything to less than 1% index containing table, but without rebuilding my query is not giving me result faster. I have also set FillFactor= 75.

Solution

  • Fragmentation rarely affects performance much. Rebuilding an index also updates the statistics associated with the index and invalidates the cached plans associated with the object.