Online & Offline Index Rebuild

Question

I had this weird case today.

Inside a database I had about 50 (1000+ pages, most of them 50000+ pages) tables which had their indexes fragmented above 70% even though I am running a nightly rebuild using Ola's scripts.

When further investigating today I noticed that when I rebuild the index with the online = on option the index would keep the same amount of fragmentation.

However the index does get built as I can see my system doing it, but when it is switched in it still has the same amount of fragmentation.

After searching on the internet, there was a possible reason that I did not have enough space in my datafile, so I extended my datafile without any result.

Then however when doing a rebuild without the online option, my index fragmentation was gone.

This means that in my case an online rebuild will not remove the fragmentation, and the offline does.

It's running on Enterprise Edition. Does anybody have an idea why this behaviour occurs?

Table code

CREATE TABLE [dbo].[TheTable](
    [BLA1] [decimal](22, 6) NULL,
    [BLA2] [smallint] NULL,
    [BLA3] [datetime] NULL,
    [BLA4] [varchar](5) NULL,
    [BLA5] [int] NULL,
    [BLA6] [varchar](50) NULL,
    [BLA7] [varchar](15) NULL,
    [BLA8] [decimal](22, 6) NULL,
    [BLA9] [decimal](22, 6) NULL,
    [BLA10] [decimal](22, 6) NULL,
    [BLA11] [int] NULL,
    [BLA12] [decimal](22, 6) NULL,
    [BLA13] [decimal](22, 6) NULL,
    [BLA14] [varchar](15) NULL,
    [BLA15] [varchar](12) NULL,
    [BLA16] [varchar](5) NULL,
    [BLA17] [varchar](6) NULL,
    [BLA18] [int] NULL,
    [BLA19] [decimal](22, 6) NULL,
    [BLA20] [varchar](1) NULL,
    [BLA21] [int] IDENTITY(1,1) NOT NULL,
    [BLA22] [varchar](100) NULL,
    [BLA23] [smallint] NULL,
    [BLA24] [datetime] NULL,
    [BLA25] [varchar](8) NULL,
    [BLA26] [varchar](6) NULL,
    [BLA27] [varchar](12) NULL,
    [BLA28] [varchar](3) NULL,
    [BLA29] [int] NULL,
    [BLA30] [int] NULL,
    [BLA31] [int] NULL,
    [BLA32] [varchar](11) NULL,
    [BLA33] [smallint] NULL,
    [BLA34] [smallint] NULL,
    [BLA35] [varchar](12) NULL,
    [BLA36] [smallint] NULL,
    [BLA37] [smallint] NULL,
    [BLA38] [varchar](10) NULL,
    [BLA39] [varchar](30) NULL,
    [BLA40] [varchar](25) NULL,
    [BLA41] [smallint] NULL,
    [BLA42] [smallint] NULL,
    [BLA43] [varchar](6) NULL,
    [BLA44] [varchar](15) NULL,
    [BLA45] [int] NULL,
    [BLA46] [decimal](22, 6) NULL,
    [BLA47] [uniqueidentifier] NULL,
    [BLA48] [uniqueidentifier] NULL,
    [BLA49] [nvarchar](50) NULL,
    [BLA50] [nvarchar](20) NULL
) ON [PRIMARY]


GO
CREATE NONCLUSTERED INDEX [XKBLA] ON [dbo].[TheTable]
(
    [bla4] ASC,
    [bla5] ASC,
    [bla16] ASC
)
INCLUDE (   [bla7],
    [bla9],
    [bla24]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO

Inserting a Sample Record

INSERT INTO [dbo].[TheTable]
           ([BLA1],[BLA2],[BLA3],[BLA4],[BLA5],[BLA6],[BLA7],[BLA8],[BLA9],[BLA10],[BLA11],[BLA12],[BLA13],[BLA14],[BLA15],[BLA16],[BLA17]
            ,[BLA18],[BLA19],[BLA20],[BLA22],[BLA23],[BLA24],[BLA25],[BLA26],[BLA27],[BLA28],[BLA29],[BLA30],[BLA31],[BLA32],[BLA33],[BLA34]
            ,[BLA35],[BLA36],[BLA37],[BLA38],[BLA39],[BLA40],[BLA41],[BLA42],[BLA43],[BLA44],[BLA45],[BLA46],[BLA47],[BLA48],[BLA49],[BLA50])
     VALUES
           (71002614000.000000,62,'2005-12-14 16:40:46.763','Bla',708512,'Bla','BLA',15.000000,-3.000000,12.000000,1,0.851000,0.060000,'BLA',0122863484,
            00003,02182,NULL,NULL,'E',1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
            NULL,NULL,NULL,NULL,NULL)
GO

More information

This is the behaviour seen when rebuilding the index:

I then rebuild the index online:

Then it gives me the following fragmentation:

When I rebuild the index with Maxdop=1:

Then the fragmentation is gone!

If I then rebuild it again with maxdop > 0:

The fragmentation is back again.

---

Fill factor 0 is being used for those indexes. These are vendor defined. There are no LOB types in the table. There is no clustered index on the table. Even when I do a manual rebuild it still keeps having the fragmentation on the non-clustered index, only an offline rebuild clears it.

Answer 1

The issue was due to the maxdop > 1 and the allow_page_locks = OFF

The issue is blogged about here (as originally mentioned by Shanky in a comment):

How It Works: Online Index Rebuild – Can Cause Increased Fragmentation by Bob Dorr – Principal SQL Server Escalation Engineer, Microsoft Customer Service and Support.

...and is called leap frog effect.

Answer 2

If you scroll way down in Ola's Index Optimize script to where he sets @CurrentCommand02, you'll see this in the code:

Index type 0 is HEAPs.

But there's a good reason to not do this. When you rebuild a HEAP, you rebuild all the nonclustered indexes, as well. Compare the STATISTICS TIME, IO output:

CREATE TABLE #t1 (c1 UNIQUEIDENTIFIER DEFAULT NEWID())

INSERT #t1 ( c1 )
DEFAULT VALUES
GO 10000

SET STATISTICS TIME, IO ON

ALTER TABLE #t1 REBUILD

CREATE NONCLUSTERED INDEX ix_t1_1 ON #t1 (c1)

CREATE NONCLUSTERED INDEX ix_t1_2 ON #t1 (c1)

CREATE NONCLUSTERED INDEX ix_t1_3 ON #t1 (c1)

CREATE NONCLUSTERED INDEX ix_t1_4 ON #t1 (c1)

CREATE NONCLUSTERED INDEX ix_t1_5 ON #t1 (c1)

ALTER TABLE #t1 REBUILD

Just the HEAP:

Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.

With indexes:

Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 42 ms.

Of course, I would urge you to stop obsessing over index fragmentation, and fix actual problems with your indexes. For instance, HEAPs can cause forwarded fetches, and may not release pages when you delete data from them. Fun!