Index fragmentation in SQL Server is a critical concern that can impact the performance and efficiency of database operations. As data within a database undergoes frequent modifications, such as inserts, updates, and deletes, the logical order of index pages may become misaligned with the physical storage order. This misalignment results in index fragmentation, where the data pages are scattered rather than contiguous, leading to increased I/O operations and degraded query performance. Recognizing and addressing index fragmentation is imperative for maintaining optimal database performance.
This code snippet serves as a powerful and controlled testing ground, enabling database professionals to imitate the fragmentation in full control. The script performs random DML operations on SQL table, measuring the primary key index fragmentation after each set of operations. The loop continues until reaching the desired fragmentation percentage limit.
In my case I execute the script on a standalone SQL Server instance on my laptop; the execution parameters include starting with 100’000 rows in a table and reaching 30% of fragmentation. The parameters can be modified, however the administrator must be careful if the execution do not exceeds the server capabilities. My execution took around 40 seconds.
--1. Creating a table
set nocount on
drop table if exists __FragmentationTable
create table __FragmentationTable
(
ID int identity
, Text_value nvarchar(100)
, Int_value int
, Date_value date
, constraint PK_ID___FragmentationTable primary key (ID)
)
--2. Populating a table with random values
declare @numrows int = 1
declare @maxrows int = 100000 --number of rows to be kept accross the executions
declare
@rand_text nvarchar(100)
, @rand_int int
, @rand_date date
while @numrows <= @maxrows --populating a table with random values
begin
set @rand_text = CONVERT(nvarchar(100), NEWID())
set @rand_int = FLOOR(RAND() * (@maxrows - 1 + 1)) + 1
set @rand_date = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01')
insert __FragmentationTable ([Text_value], [Int_value], [Date_value])
values(@rand_text, @rand_int, @rand_date);
set @numrows = @numrows + 1;
end
--3. UPDATING random number of rows IN LOOP
declare
@loop_count int = 1,
@fragmentation_perc decimal(8,5) = 0,
@fragmentation_perc_target decimal(8,5) = 30 --percentage of fragmentation to be reached
declare @random_updates int
declare @random_deletes int
declare @row_count int = @maxrows
declare @sql nvarchar(max)
while @fragmentation_perc <= @fragmentation_perc_target --while loop, until reaching the fragmentation
begin
set @random_updates = FLOOR(RAND() * (@row_count - 1 + 1)) + 1 --randomised number of updated rows
set @random_deletes = FLOOR(RAND() * (@row_count/10 - 1 + 1)) + 1 --randomised number of deleted & inserted rows
set @sql = 'DROP TABLE IF EXISTS ##SampleUpdatesTable; '
set @sql = concat(@sql, 'select ID into ##SampleUpdatesTable from __FragmentationTable tablesample(',@random_updates,' rows); ') --interestingly - tablesample is not ideal
set @sql = concat(@sql, 'DROP TABLE IF EXISTS ##SampleDeleteTable; ')
set @sql = concat(@sql, 'select ID into ##SampleDeleteTable from __FragmentationTable tablesample(',@random_deletes,' rows); ') --interestingly - tablesample is not ideal
exec sys.sp_executesql @sql
--updates
update ft
set
Text_value = NEWID(),
Int_value = ABS(CHECKSUM(NewId())) % 1000000,
date_value = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01')
from __FragmentationTable ft
join ##SampleUpdatesTable sut on ft.ID = sut.ID
--deletes
delete ft
from __FragmentationTable ft
join ##SampleDeleteTable sut on ft.ID = sut.ID
--inserts
set @numrows = 1
while @numrows <= @random_deletes
begin
set @rand_text = CONVERT(nvarchar(100), NEWID())
set @rand_int = FLOOR(RAND() * (@maxrows - 1 + 1)) + 1
set @rand_date = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01')
insert __FragmentationTable ([Text_value], [Int_value], [Date_value])
values (@rand_text, @rand_int, @rand_date);
set @numrows = @numrows + 1;
end
set @row_count = (select count(*) from __FragmentationTable) --updating row count - not ideal @maxrows
--4. Checking the fragmentation
select @fragmentation_perc=IDXPS.avg_fragmentation_in_percent --checking fragmentation percentage
from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IDXPS
inner join sys.indexes IDX on IDX.object_id = IDXPS.object_id
and IDX.index_id = IDXPS.index_id
where IDX.name='PK_ID___FragmentationTable'
print concat('Exec no.: ', @loop_count, char(9), ' Updated rows: ', @random_updates, char(9),' Deleted rows: ', @random_deletes, char(9), ' Rows count after that: ', @row_count, char(9), ' Fragmentation %: ', @fragmentation_perc)
set @loop_count = @loop_count + 1
end