SQL Server Index Reorganization vs Rebuild Performance Impact

By: Ben Snaidero | Comments | Related: > Maintenance

Problem

After writing my last tip, SQL Server Query Performance After Index Maintenance for Reorganization vs. Rebuild Operations, and seeing the results I was really curious if the same performance would hold true for other DML operations like INSERT, UPDATE and DELETE. The tip will look at the performance of these DML operations after the index maintenance has been performed.

Solution

Sample Table Setup

If you still have the backup file from my last tip you can use that as your sample data for this test as well. If, not you can use the below T-SQL to create a table with a fairly large amount of sample data and a fragmented index.

-- Create sample table and indexes
CREATE TABLE testtable ([col1]  [int] NOT NULL primary key  clustered,
                        [col2] [int]  NULL,
                        [col3] [int]  NULL,
                        [col4] [varchar](50) NULL); 
 
CREATE INDEX idx_testtable_col3 on testtable (col3  asc);

-- Load sample data into table
DECLARE @val INT
SELECT @val=1
WHILE @val < 5000000
BEGIN  
   INSERT INTO testtable (col1, col2, col3, col4) 
       VALUES (@val,round(rand()*100000,0),
               round(rand()*100000,0),'TEST' + cast(@val AS VARCHAR))
   SELECT @val=@val+1
END
GO

As we did in the last test let's take a baseline backup that we will restore after each scenario, so we ensure that each scenario starts with the exact same data\index layout. Here are the T-SQL commands to perform the backup and restore.

BACKUP DATABASE [TestDB] TO  DISK = N'C:\TestDB.bak' 
 WITH COMPRESSION,NOFORMAT, NOINIT,  STATS = 10

RESTORE DATABASE [TestDB] FROM DISK = N'C:\TestDB.bak' 
 WITH FILE = 1, NOUNLOAD, STATS = 5;

The last thing we'll need for this test are some DML statements to run against our database. I've included the script that I used or you can use the T-SQL below to generate your own DML script.

-- Generate insert statements
DECLARE @val INT
SELECT @val=5000001
WHILE @val < 5000050
BEGIN  
   SELECT 'INSERT INTO testtable (col1, col2,  col3, col4)
       VALUES ('+cast(@val as varchar)+',
               round(rand()*100000,0),
               round(rand()*100000,0),
               ''TEST'' + cast('+cast(@val as varchar)+' AS varchar))'
   SELECT @val=@val+1
END
GO

-- Generate update statements
DECLARE @val INT
DECLARE @delval INT
SELECT @val=1
WHILE @val < 50
BEGIN  
   SELECT TOP 1 @delval=col3 FROM testtable ORDER BY NEWID()
   select 'DELETE FROM testtable WHERE col3='+cast(@delval as varchar)
   SELECT @val=@val+1
END
GO

-- Generate delete statements
DECLARE @val INT
DECLARE @updval INT
SELECT @val=1
WHILE @val < 50
BEGIN  
   SELECT TOP 1 @updval=col3 FROM testtable ORDER BY NEWID()
   SELECT 'UPDATE testtable  
      SET col2=round(rand()*100000,0),col4=''TEST'' + 
               cast('+cast(@val as varchar)+' AS varchar)
	WHERE col3='+cast(@updval as varchar)
   SELECT @val=@val+1
END
GO

Performance test

For this test we will follow a pattern similar to what was done in the last tip. We'll run our script first against the baseline database and then after a restore we will rebuild the index and run the script again. After one last restore we'll run an index reorg and then run our test script one last time. During each script run you'll need to have SQL Profiler running to capture the performance statistics. Here is the TSQL code for the entire test scenario.

--Baseline test
RUN "DML test script.sql"

-- REBUILD test
RESTORE DATABASE [TestDB] FROM DISK = N'C:\TestDB.bak' 
 WITH FILE = 1, NOUNLOAD, STATS = 5;

ALTER INDEX [idx_testtable_col3] ON [dbo].[testtable] REBUILD PARTITION = ALL 
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,  SORT_IN_TEMPDB =  OFF, 
        ONLINE = OFF, ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS =  ON);

RUN "DML test script.sql"

-- REORG test
RESTORE DATABASE [TestDB] FROM DISK = N'C:\TestDB.bak' 
 WITH FILE = 1, NOUNLOAD, STATS = 5;

ALTER INDEX [idx_testtable_col3] ON [dbo].[testtable] REORGANIZE 
  WITH ( LOB_COMPACTION =  ON );

RUN "DML test script.sql"

Now that we have our test scenario all setup we can just run through the steps capturing all the performance metrics through SQL Profiler. Note for this test that I did not bother looking at the index fragmentation statistics after each step as this would remain consistent with the results shown in the first tip. The below tables summarizes our test scenario results.

Baseline
DML Action	CPU(ms)	Reads	Writes	Duration(ms)
insert	62	528	50	867
delete	531	41681	2478	17422
update	78	18708	2306	15859
After Index Rebuild
DML Action	CPU(ms)	Reads	Writes	Duration(ms)
insert	31	1005	183	608
delete	94	40007	2453	9920
update	91	18861	2269	9512
After Index Reorg
DML Action	CPU(ms)	Reads	Writes	Duration(ms)
insert	63	882	181	1189
delete	496	41527	2422	12326
update	93	18775	2078	12397

Looking at these results we do see some improvement after the index maintenance is performed, especially after the index rebuild. From my past experience I have always found that after performing index maintenance you usually see even more improvement than illustrated by this example, but this could possibly be attributed to two factors. First, this test is performed in isolation with only one table and one index. In the real world when we are making data changes it usually involves multiple tables, so after any maintenance is performed we would expect to see this improvement multiplied based on the number of tables involved. Second, this test was done on a simple one column index. More complex index structures, multicolumn or indexes with included columns, may see even more performance benefits after maintenance has been completed.

Next Steps

Look for future index maintenance tips on the following topics:
- Page structure before and after index maintenance
- Performance test using more complex index structures, multi column indexes and indexes with included columns
Read more information on index maintenance:

About the author

Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

SQL Server Query Performance After Index Maintenance for Reorganization vs Rebuild Operations - Part 2