When should you rebuild an index? vs reorganize?
What index fragmentation value is considered healthy?
2
-
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 :)– AndomarCommented Mar 5, 2011 at 19:59
-
To answer your first question: dba.stackexchange.com/q/4283/2660– Nick ChammasCommented Jan 21, 2012 at 20:52
Add a comment
|
3 Answers
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.
-
1+1 Yes enterprise edition can index online (at least that's why we tried to order it for our new server :))– AndomarCommented Mar 5, 2011 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.– bjorsigCommented Mar 5, 2011 at 20:34
-
when sql server says Total Fragmentation is 1.2394323. Is that 1.23% or 123%?– JasonCommented Mar 5, 2011 at 23:59
-
If you are using sys.dm_db_index_physical_stats 1.23 is 1.23%.– bjorsigCommented Mar 6, 2011 at 13:27
-
@bjorsig
REBUILDdoesn'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)– mpagCommented Oct 30, 2015 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.
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.