When should you rebuild an index? vs reorganize?
What index fragmentation value is considered healthy?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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.
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.