In a few client environments, I have seen index monitoring and maintenance procedures triggered by fragmentation levels. I have sometimes wondered how these procedures were tested when they were put in place, other than waiting for fragmentation to occur through normal operations. Wouldn’t it be a good idea to have a process that generates a level of fragmentation to test these processes?
Here is a process that can be used as the basis for generating index fragmentation to properly test index monitoring and maintenance procedures. I will create a test table, populate it with data, create an index on the test table, then execute a process that keeps inserting data into the test table until the desired level of index fragmentation is reached.
During testing, 100 rows were inserted into the test table initially, then each iteration of the fragmentation process inserted another 100 rows. It took only two iterations through the process to produce 50 percent index fragmentation. For more precise fragmentation, use a larger number of rows during the initial table population routine or a fewer number of rows during the data insert.
NOTE – This process was created and tested on SQL Server 2016. Be sure to set the database environment where you want to create the test table before executing these commands.
--Drop the test table if it exists drop TABLE dbo.TestTable GO --Create the test table CREATE TABLE dbo.TestTable ( RowID int IDENTITY(1,1), MyKeyField VARCHAR(10) NOT NULL, MyDate DATETIME NOT NULL, MyInt DATETIME NOT NULL, MyString VARCHAR(30) NOT NULL ) GO --Code to insert first rows into table DECLARE @RowCount INT DECLARE @RowString VARCHAR(10) DECLARE @Random INT DECLARE @Upper INT DECLARE @Lower INT DECLARE @InsertDate DATETIME DECLARE @s CHAR(30) SET @Lower = -730 SET @Upper = -1 SET @RowCount = 0 WHILE @RowCount < 100 --Set this value to the number of rows desired BEGIN SET @RowString = CAST(@RowCount AS VARCHAR(10)) SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) SET @InsertDate = DATEADD(dd, @Random, GETDATE()) SET @s = ( SELECT c1 AS [text()] FROM ( SELECT TOP (30) c1 --Change this value to the desired length of the resulting string. FROM ( VALUES ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'), ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'), ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z'), ('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('!'), ('@'), ('#'), ('$'), ('%'), ('&'), ('*'), ('('), (')'), ('?'), ('+'), ('-'), (','), ('.') ) AS T1(c1) ORDER BY ABS(CHECKSUM(NEWID())) ) AS T2 FOR XML PATH('') ); INSERT INTO TestTable (MyKeyField ,MyDate ,MyInt ,MyString) VALUES (REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString , @InsertDate ,RAND(@RowCount) ,@s) SET @RowCount = @RowCount + 1 END GO --Verify number of row inserted into test table select count(*) from TestTable; --Create index on test table SET ANSI_PADDING ON GO CREATE NONCLUSTERED INDEX [dbo.TestTableIndex] ON [dbo].[TestTable] ( [MyString] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO --Check initial fragmentation select avg_fragmentation_in_percent from sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') where object_id = (select object_id from sys.tables where name = 'TestTable') AND index_type_desc = 'NONCLUSTERED INDEX' GO --Routine to loop through row inserts until fragmentation reaches desired level DECLARE @FragEnd INT DECLARE @FragPercent INT SET @FragEnd = 30 --Set this number to degree of fragmentation desired SET @FragPercent = 0 SET IDENTITY_INSERT [dbo].[TestTable] ON WHILE @FragEnd > @FragPercent BEGIN select @FragPercent = max(avg_fragmentation_in_percent) from sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') where object_id = (select object_id from sys.tables where name = 'TestTable') AND ALLOC_unit_type_desc = 'IN_ROW_DATA'; insert into [dbo].[TestTable] ( [RowID],[MyKeyField],[MyDate],[MyInt],[MyString]) select top(100) [RowID],[MyKeyField],[MyDate],[MyInt],[MyString] from dbo.TestTable; END; GO