SQL Server Index Reorganization vs Rebuild Performance Impact – Part 2

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 ActionCPU(ms)ReadsWritesDuration(ms)
insert6252850867
delete53141681247817422
update7818708230615859
After Index Rebuild
DML ActionCPU(ms)ReadsWritesDuration(ms)
insert311005183608
delete944000724539920
update911886122699512
After Index Reorg
DML ActionCPU(ms)ReadsWritesDuration(ms)
insert638821811189
delete49641527242212326
update9318775207812397

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

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *