When should you rebuild an index? vs reorganize?

What index fragmentation value is considered healthy?

  • That depends on the context, like the size of the table, your version and edition of SQL Server, and how you query the table. If there was a one-size-fits-all solution, SQL Server would do that by default :) – Andomar Mar 5 '11 at 19:59
  • To answer your first question: dba.stackexchange.com/q/4283/2660 – Nick Chammas Jan 21 '12 at 20:52

Rebuilding an index takes more CPU than reorganizing it. It locks the database so that has to taken into account. Indexes should be rebuilt when the fragmentation is more than 40% or so. After that it becomes too slow and cumbersome for the server to reorganize. You should reorganize an index when the fragmentation is roughly 10%-40%. So healthy index is basically less than 10% fragmentation, little bit less than 10% perhaps.

These are my rules of thumb and they have worked for me, but they are guidelines. So many things change the performance so you need to find out what woorks for you in your environment.

I think that you can rebuild an index online nowadays, at least if you use the Enterprise version of SQL Server, the keyword ONLINE if I am not mistaken.

| improve this answer | |
  • 1
    +1 Yes enterprise edition can index online (at least that's why we tried to order it for our new server :)) – Andomar Mar 5 '11 at 20:25
  • Maybe it isn't clear but the functional difference between the two methods is that REBUILD drops the index and re-creates it, but REORGANIZE moves the leaf nodes. – bjorsig Mar 5 '11 at 20:34
  • when sql server says Total Fragmentation is 1.2394323. Is that 1.23% or 123%? – Jason Mar 5 '11 at 23:59
  • If you are using sys.dm_db_index_physical_stats 1.23 is 1.23%. – bjorsig Mar 6 '11 at 13:27
  • @bjorsig REBUILD doesn't appear to rebuild the index from scratch. In the event of index corruption, it still has the invalid data in the "rebuilt" index. See dba.stackexchange.com/questions/36498/… or dba.stackexchange.com/questions/119605/… (comment applies to SQL Server 2008 R2) – mpag Oct 30 '15 at 19:02

There's a few variables: - how much data is being changed (inserted/updated) - how much data you have - whether you have a maintenance window, and how long it is

A general consensus is to perhaps consider doing general reindexing once a week at weekends along with other maintenance (dbcc checkdb for instance), and monitor/evaluate.

| improve this answer | |

The practical impact of index fragmentation will have a lot to do with the size of the data set and how it's queried. My non-specific answer is that you switch from reorganize to rebuild when reorganize doesn't make it fast enough anymore.

The other huge variable is that indexes on highly inserted/updated tables will naturally fragment much more so than for tables that are read-heavy so your performance baselines should take that into account. (e.g., don't run your query performance metrics when the indexes are pristine if you expect them to fragment quickly.)

Although you CAN run a rebuild online on Enterprise Edition be aware that it comes with a fairly severe performance penalty until it finishes. It may not be worthwhile depending on the database workload and impact of increased query execution time.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy