SQL Server Query Performance After Index Maintenance for Reorganization vs Rebuild Operations - Part 2
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.
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
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.
|After Index Rebuild|
|After Index Reorg|
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.
- 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:
- Selectively Rebuild indexes with SQL Server maintenance plans
- SQL Server Script to rebuild all indexes for all tables and all databases
- Fixing index fragmentation in SQL Server 2005 and 2008
- Managing SQL Server database fragmentation
- Custom SQL Server index defrag and rebuild procedures
- Fragmentation and index maintenance
About the author
View all my tips