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.