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


By:   |   Updated: 2015-04-23   |   Comments   |   Related: More > 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 @[email protected]+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 @[email protected]+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 @[email protected]+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 @[email protected]+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


Last Updated: 2015-04-23


get scripts

next tip button



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

View all my tips
Related Resources



Comments For This Article





download





Recommended Reading

Deleting Historical Data from a Large Highly Concurrent SQL Server Database Table

Update Statistics for All Tables and Databases in a SQL Server Instance

SQL Server Maintenance Plan Index Rebuild and Reorganize Tasks

Getting Started with SQL Server Maintenance Plans - Part 1

SQL Server Maintenance Plans Maintenance Cleanup Task














get free sql tips
agree to terms