As Microsoft SQL Server PFE, I am always playing with SQL Server. I generally spend most of my time on the engine side of world; so I am somewhat of a newbie when it comes to BI. So I understand when I see something in BI world and am a bit lost. However when I ran into this, rebuilding index online caused fragmentation. I was some what baffled. You can try it if you wish, I tested it on SQL 2008, SQL 2008 R2, and SQL 2012.
-- Create table for testing. CREATE TABLE Test (Col1 INT IDENTITY(1,1), Col2 CHAR(50), Col3 CHAR(50), Col4 Char(50) CONSTRAINT pkTest PRIMARY KEY ( [Col1] ) WITH (ALLOW_PAGE_LOCKS = OFF) ) -- Insert test data. INSERT INTO dbo.Test (Col2, Col3, Col4) SELECT TOP 1000000 REPLICATE('A',50),REPLICATE('A',50),REPLICATE('A',50) FROM sys.all_objects O1 CROSS JOIN sys.all_objects O2 -- Check fragmentation. SELECT * FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null) -- Rebuild Index online using MAXDOP ALTER INDEX pkTest ON Test REBUILD WITH (Online=ON, MAXDOP=8) -- Check fragmentation. SELECT * FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null)
Some of you might noticed an interesting property. ALLOW_PAGE_LOCK.
So turns out this is by design. If ALLOW_PAGE_LOCK is turned off, we are building a index using parallelism, and building it online; it can cause fragmentation (Ref 1 & Ref 2).
Well lesson learned for future and no, it will not be last one.