And you probably know the answer from MSDN: https://msdn.microsoft.com/en-us/library/ms189858.aspx
How many times you've followed that rule?
How many times you've answered that question on a job interview?
And now ask yourself: Were you right then? Have you ever tested that approach?
My answers to these two questions are: "No" and "Yes".
That happened that two years ago I serviced one client with very busy database, which suffered from high Index Fragmentation.
I've tried to use approach from MSDN to address Fragmentation by Rebuild or Reorganize.
Very quickly I've noticed that "Reorganize" runs too long in their environment and Index Rebuild works much quicker for tables with Fragmentation as low as 10%-15%.
At that time I did not have time and desire to deeply research that case, but very heavy hesitation has shaken my believe in MSDNs Re-Indexing rule.
Recently I've hit same issue again and finally decided to do a research.
At first I've created test table with clustered index and fill factor 70% in my test Database:
Then Ive started building cases:
Here is the returned result:
Lets look deeper in what I just did:
At first I've inserted 700000 records into the table. It created 7K pages and filled them with 100% of their capacity and and it looked like this:
The last query just produced the fragmentation report.
Now we are ready to start our defragmentation procedure.
Before running Altering the index I started Query Profiler to capture the results:
After index been reorganized, I've run original script again to reproduce fragmentation back to 5.835% and run Index rebuild:
Here are my results from Query Profiler:
Have you expected that? I have not.
I did not believe my eyes. I started hunt for fragmentation values when Reorganize would out perform Index rebuild.
Case #2I've started that case by filling 10K pages by 70% of data. Then I've up-loaded all pages to almost 100% of their capacity, so they looked like this:
960600 records in 10300 pages with the same fragmentation of 5.835%.
I've used following script to produce that picture:
Please note: The fragmentation is the same as in the first case, but Average used space on each page is completely different. That is what makes the real difference, not a fragmentation.
When I run Reorganize and Rebuild again I've got the following results in Profiler:
Not so impressive as in the first case, but still "Rebuild" outperformed "Reorganize".
Case #3In this case I've just increased fragmentation by splitting 1000 pages:
After Reorganize and Rebuild I've got this:
Case #4 (extreme)In that case I've increased capacity of all pages up to 98% as in Case #2 and then split 1700 pages:
And here is what I've got:
Do I have to test anymore?
Case #5In that case I wanted to see how "Rebuild" or "Reorganize" impact other transactions on busy servers.
In order to do this test I've inserted to my table only 70K records and split only 30 pages (10 times less than in the first case)
Before running ALTER INDEX command I started following script in another window to emulate heavy activity:
Surprisingly, when I run "INDEX REORGANIZE", I almost immediately got an error:
Here is captured in Profiler Deadlock Graph:
I've tried some other scenarios and discovered that even though "Rebuild" causes blocking, but does not produce Deadlocks. "Reorganize" produced Deadlocks only on the very small amount of pages (about 1K).
When I've tried bigger tables I couldn't reproduce Deadlock scenario, but I do not think it is impossible, I've just have not tried hard enough.
As the conclusion:
"INDEX REORGANIZE" is generally slower than "INDEX REBUILD". It uses much more I/O operations and can produce deadlocks.
Do you still want to use "REORGANIZE" option in your code?
That is pretty tough question.
If you take a look at the internal difference how "REBUILD" and "REORGANIZE" work you'll see following:
- "INDEX REBUILD" is creating brand new copy of the index in the database. Would say your table is 2Tb in size. That means you need another 2Tb in your database to finish "INDEX REBUILD" successfully. In addition to that you need 2Tb in your Log file, which means 4Tb of extra space. Do you have it?
- "INDEX REORGANIZE" works much slower, but in small transactions. You can just run it periodically, in your lowest DB activity time and just kill process automatically after an hour or two. It is not a problem if process won't finish completely. Partial results are also beneficial. If process will be a deadlock victim - that is also not a problem other than you get deadlock alert in the middle of the night.
As a result of my research I will be always using "INDEX REBUILD" option from now going forward, unless I'm in a situation when "REBUILD" does not work. Then "REORGANIZE" will take the place.