Creating SQL Server index fragmentation

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 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