Index Rebuild vs Reorganize

Have you ever asked that question which ALTER INDEX operation to use Rebuild or Reorganize?

And you probably know the answer from MSDN: https://msdn.microsoft.com/en-us/library/ms189858.aspx

It states:

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:

USE TestDB;
GO
IF EXISTS ( SELECT * FROM sys.tables WHERE name = 'tbl_Test_Index_Rebuild')
    DROP TABLE tbl_Test_Index_Rebuild;
GO
CREATE TABLE tbl_Test_Index_Rebuild(
    ID INT,
    A CHAR(68)
);
GO
CREATE CLUSTERED INDEX CIX_tbl_Test_Index_Rebuild ON tbl_Test_Index_Rebuild(ID) WITH (FILLFACTOR=70);
GO

Then Ive started building cases:

Case #1.

USE TestDB;
GO
TRUNCATE TABLE tbl_Test_Index_Rebuild
GO
;WITH
Pass0 AS (SELECT 1 AS C UNION all SELECT 1), --2 rows
Pass1 AS (SELECT 1 AS C FROM Pass0 AS A, Pass0 AS B),--4 rows
Pass2 AS (SELECT 1 AS C FROM Pass1 AS A, Pass1 AS B),--16 rows
Pass3 AS (SELECT 1 AS C FROM Pass2 AS A, Pass2 AS B),--256 rows
Pass4 AS (SELECT 1 AS C FROM Pass3 AS A, Pass3 AS B),--65536 rows
Pass5 AS (SELECT 1 AS C FROM Pass4 AS A, Pass4 AS B) --4294967296 rows
INSERT INTO tbl_Test_Index_Rebuild(ID, A)
SELECT TOP 700000 ROW_NUMBER() OVER( ORDER BY C), 'A' FROM Pass5;
GO
ALTER INDEX CIX_tbl_Test_Index_Rebuild ON tbl_Test_Index_Rebuild REBUILD WITH (MAXDOP=1);
GO
INSERT INTO tbl_Test_Index_Rebuild(ID, A) SELECT Fragment+1, 'A' FROM (SELECT TOP 30 0 AS MsgId FROM sys.messages) AS Msg,
(SELECT TOP 300 (ROW_NUMBER() OVER\( ORDER BY message_id)-1)*70 AS Fragment FROM sys.messages) AS Fragments
OPTION (MAXDOP 1);
GO
SELECT CASE WHEN i.fill_factor = 0 OR (ps.index_level > 0 and i.is_padded = 0)
        THEN 100 ELSE i.fill_factor END AS fill_factor,
        ROUND(ps.avg_fragmentation_in_percent,3) AS [AVG Frgmnt %],
        ROUND(ps.avg_page_space_used_in_percent,3) AS [AVG Space USE %],
        ps.fragment_count,
        ROUND(ps.avg_fragment_size_in_pages,3) AS [AVG Frgmnt Size],
        ps.page_count, ps.record_count,
        (ps.record_count / ps.page_count) AS AVG_Records_per_Page
    FROM sys.indexes i
    CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), i.object_id, i.index_id, NULL, 'DETAILED') ps
    WHERE i.name = 'CIX_tbl_Test_Index_Rebuild' and ps.index_level = 0;
GO

Here is the returned result:

You can see that table has 709000 rows on 10300 pages and has only 5.835% fragmentation.

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 next step was "Index Rebuild" to enforce 70% Fill Factor and number of pages has increased to 10K with 70% of their capacity filled. It looked like this:

On the next step I inserted 30 records on the first 300 pages. That action caused Page splits for those 300 pages, which produced 600 fragments. Now if you divide 600 by total number of pages 10300, you almost get the Average fragmentation I've got: 5.825% (the actual number is little bit different because there are more calculable variables). As a result pages looked like these:

(the bluish ones - are 600 fragmented pages and have filled only 50%)

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:

ALTER INDEX CIX_tbl_Test_Index_Rebuild ON tbl_Test_Index_Rebuild REORGANIZE;

After index been reorganized, I've run original script again to reproduce fragmentation back to 5.835% and run Index rebuild:

ALTER INDEX CIX_tbl_Test_Index_Rebuild ON tbl_Test_Index_Rebuild REBUILD;

Here are my results from Query Profiler:

Amazingly Index Rebuild used 8 times less reads than Reorganize, 3 times less writes and finished 4 times faster!!!!

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 #2

I'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:

 Then I added couple of records to first 300 pages to split them and produce the fragmentation like this:

As a result I've got following:

960600 records in 10300 pages with the same fragmentation of 5.835%.

I've used following script to produce that picture:

INSERT INTO tbl_Test_Index_REBUILD(ID, A)
SELECT Fragment+1, 'A' FROM (SELECT TOP 26 0 AS MsgId FROM sys.messages) AS Msg,
(SELECT TOP 10000 (ROW_NUMBER() OVER( ORDER BY message_id)-1)*70 AS Fragment FROM sys.messages) AS Fragments
OPTION (MAXDOP 1);
GO
INSERT INTO tbl_Test_Index_REBUILD(ID, A)
SELECT Fragment+1, 'A' FROM (SELECT TOP 2 0 AS MsgId FROM sys.messages) AS Msg,
(SELECT TOP 300 (ROW_NUMBER() OVER( ORDER BY message_id)-1)*70 AS Fragment FROM sys.messages) AS Fragments
OPTION (MAXDOP 1);
GO

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:

Index Rebuild used 6.5 times lees Reads, ~30% less writes and finished also about 30% faster.
Not so impressive as in the first case, but still "Rebuild" outperformed "Reorganize".

Case #3

In this case I've just increased fragmentation by splitting 1000 pages:

 As a result 730K records on 11K pages with 18.127% of fragmentation.

After Reorganize and Rebuild I've got this:

Not sure if timing was captured correctly, but you still can see the significant difference in number of reads. "Rebuild" is better again.

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:

In the result I've got 963400 records in 11,7K pages with fragmentation of 29% (almost 30% limit as in MSDN's guideline)

And here is what I've got:

Rebuild is faster and uses less Reads and Writes. No surprise.

Do I have to test anymore?
Why not?

Case #5

In 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:

SET NOCOUNT ON

WHILE 1 > 0
BEGIN
    ;WITH a AS ( SELECT top 1 * FROM tbl_Test_Index_REBUILD
        ORDER BY NEWID()
    )
    UPDATE a SET ID = ABS(CAST(NEWID() AS VARBINARY) % 1000000)
    OPTION (MAXDOP 1)
END

Surprisingly, when I run "INDEX REORGANIZE", I almost immediately got an error:

Msg 1205, Level 13, State 52, Line 68
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

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.