In another thread
http://forums.sqlteam.com/t/timeoute-expired-the-timeout-period-elapsed-prior-to-completion/?source_topic_id=6271there was discussion that a table with a Clustered Index on an ever-increasing IDENTITY with 100% Fill Factor was getting badly fragmented. @ScottPletcher suggested that it might be caused by an NVarchar column being extended significantly and causing page splits, and I wanted to have a play with that scenario to learn more about it, but I failed to reproduce the situation, so here's my test code in the hope someone can point out what I have done wrong - probably a School Boy Error !!
My code is based on a very useful article I read by Martin Smith:
http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean
DECLARE @InRowData varchar(3) = 'ON' -- IN_ROW_DATA = ON or OFF
, @NVarcharSize int = 8001 -- 8001 or 20000 - Size to Update NVarchar column to
, @Rows int = 100 -- 100 or 1000 - Number of rows in the test
, @Modulo int = 3 -- 3 - Rows with (ID / @Modulo = 1 ) will be updated
CREATE TABLE T
(
X INT NOT NULL,
Y CHAR(3000) NULL,
Z NVARCHAR(MAX) NULL,
);
CREATE CLUSTERED INDEX ix
ON T(X);
EXEC sp_tableoption 'T', 'large value types out of row', @InRowData;
--Insert rows into [T] in random order (so that the table is fragmented)
DECLARE @C1 AS CURSOR,
@X AS INT
SET @C1 = CURSOR FAST_FORWARD
FOR SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND @Rows
ORDER BY CRYPT_GEN_RANDOM(4)
OPEN @C1;
FETCH NEXT FROM @C1 INTO @X;
SET NOCOUNT ON;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO T (X)
VALUES (@X);
FETCH NEXT FROM @C1 INTO @X;
END
SET NOCOUNT OFF;
-- Store the Page Layout in [T_Index1] and fragmentation in [T_Frag1] (to examine later)
SELECT page_id,
X
-- , geometry::Point(page_id, X, 0).STBuffer(1) AS G
INTO T_Index1
FROM T
CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER BY
X,
page_id
SELECT ps.index_id
, [Partitions] = ps.partition_number
, [Frag%] = ps.avg_fragmentation_in_percent
, [Frags] = fragment_count
, ps.page_count
, index_depth
, alloc_unit_type_desc
, [Name] = OBJECT_NAME(ps.OBJECT_ID)
INTO T_Frag1
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'T'), NULL , NULL, N'Limited') AS ps
-- Rebuild index
ALTER INDEX ix ON T REBUILD;
-- Store the Page Layout in [T_Index2] and fragmentation in [T_Frag2] - which should now be contiguous and not fragmented
SELECT page_id,
X
-- , geometry::Point(page_id, X, 0).STBuffer(1) AS G
INTO T_Index2
FROM T
CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER BY
X,
page_id
SELECT ps.index_id
, [Partitions] = ps.partition_number
, [Frag%] = ps.avg_fragmentation_in_percent
, [Frags] = fragment_count
, ps.page_count
, index_depth
, alloc_unit_type_desc
, [Name] = OBJECT_NAME(ps.OBJECT_ID)
INTO T_Frag2
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'T'), NULL , NULL, N'Limited') AS ps
-- Select IDs of rows to have their NVarchar column extended (based on @Modulo)
-- Save to #TEMP sorted into random order
SELECT [ID] = IDENTITY(int, 1, 1),
X
INTO #TEMP
FROM T
WHERE X % @Modulo = 0
ORDER BY CRYPT_GEN_RANDOM(4)
DECLARE @ID int = 0
, @RowsUpdated int = 0
-- Extend NVarchar column (in random order in case that causes an issue that a single multi-row UPDATE would not)
SET NOCOUNT ON;
WHILE @ID <> -1
BEGIN
-- Get next ID to process
SELECT TOP 1
@ID = ID
, @X = X
FROM #TEMP
WHERE ID > @ID
IF @@ROWCOUNT = 0 SELECT @ID = -1 -- Terminate
UPDATE T
SET Z = REPLICATE('X', @NVarcharSize)
FROM T
WHERE X = @X
SELECT @RowsUpdated = @RowsUpdated + @@ROWCOUNT
END
SET NOCOUNT OFF;
DROP TABLE #TEMP
-- Store the Page Layout in [T_Index3] and fragmentation in [T_Frag3] to check for changes before / after rebuild
SELECT page_id,
X
-- , geometry::Point(page_id, X, 0).STBuffer(1) AS G
INTO T_Index3
FROM T
CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER BY
X,
page_id
SELECT ps.index_id
, [Partitions] = ps.partition_number
, [Frag%] = ps.avg_fragmentation_in_percent
, [Frags] = fragment_count
, ps.page_count
, index_depth
, alloc_unit_type_desc
, [Name] = OBJECT_NAME(ps.OBJECT_ID)
INTO T_Frag3
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'T'), NULL , NULL, N'Limited') AS ps
-- Rebuild index again
ALTER INDEX ix ON T REBUILD;
-- Store the Page Layout after rebuild in [T_Index4] and fragmentation in [T_Frag4]
SELECT page_id,
X
-- , geometry::Point(page_id, X, 0).STBuffer(1) AS G
INTO T_Index4
FROM T
CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER BY
X,
page_id
SELECT ps.index_id
, [Partitions] = ps.partition_number
, [Frag%] = ps.avg_fragmentation_in_percent
, [Frags] = fragment_count
, ps.page_count
, index_depth
, alloc_unit_type_desc
, [Name] = OBJECT_NAME(ps.OBJECT_ID)
INTO T_Frag4
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'T'), NULL , NULL, N'Limited') AS ps
----------
-- Results:
SET NOCOUNT ON
SELECT [@InRowData]=@InRowData, [@NVarcharSize]=@NVarcharSize, [@Rows]=@Rows, [@Modulo]=@Modulo, [@RowsUpdated]=@RowsUpdated
PRINT 'Create randon:'
SELECT * FROM T_Frag1
PRINT 'Rebuild index(1):'
SELECT * FROM T_Frag2
PRINT 'Update NVarchar:'
SELECT * FROM T_Frag3
PRINT 'Rebuild index(2):'
SELECT * FROM T_Frag4
SET NOCOUNT OFF
/**
-- Review Row IDs and Pages - if desired
PRINT 'Create randon:'
SELECT * FROM T_Index1 ORDER BY page_id, X
PRINT 'Rebuild index(1):'
SELECT * FROM T_Index2 ORDER BY page_id, X
PRINT 'Update NVarchar:'
SELECT * FROM T_Index3 ORDER BY page_id, X
PRINT 'Rebuild index(2):'
SELECT * FROM T_Index4 ORDER BY page_id, X
--
**/
GO
DROP TABLE #TEMP
GO
DROP TABLE T
GO
DROP TABLE T_Frag1
GO
DROP TABLE T_Index1
GO
DROP TABLE T_Frag2
GO
DROP TABLE T_Index2
GO
DROP TABLE T_Frag3
GO
DROP TABLE T_Index3
GO
DROP TABLE T_Frag4
GO
DROP TABLE T_Index4
GO
