Advertisements

Archive

Posts Tagged ‘Online Index Maintenance’

Online Index Rebuild can Cause Fragmentation

June 9, 2014 Leave a comment

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.

Advertisements
%d bloggers like this: