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.
Best Answer
The issue was due to the
maxdop
> 1 and theallow_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.