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

Problem

Over the course of my career as a DBA I’ve read a lot of documentation and articles on database maintenance and more specifically around index maintenance. One of the most consistent topics discussed has always been whether you should rebuild or reorganize your indexes and when to do so. Since this topic has been covered fairly extensively this tip is going to simply look at the performance of some SELECT statements after the index maintenance has been performed.

Solution

Since this tip is strictly going to focus on query performance after the index maintenance has been completed the links listed below provide some good information on the differences between index rebuilds and reorganizations as well as outline industry best practices for index maintenance.

• SQL Server Fragmentation and Index Maintenance Tips
• Reorganize and Rebuild Indexes
• SQL Server 2005 Index Best Practices
• Fixing Index Fragmentation in SQL Server 2005 and SQL Server 2008

Sample Table for SQL Server Index Performance Testing

For us to perform this test we will first create a simple test table with a few columns and load a fair amount of data into it. The reason we want to load so much data is because index reorganizations have almost no effect on very small tables no matter how fragmented they are and I have even read articles where after a small table was reordered the fragmentation was slightly worse than it was before the maintenance was done. Here is the code to create this table structure and load the sample data.

-- 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

SQL Server Index Performance Testing

Before we perform any index maintenance let’s get a baseline of what our index fragmentation looks like and a snapshot of our current query performance so we have something to compare to after the maintenance is performed. There are a few different methods for capturing index fragmentation information as is outlined in the following links.

• Identify Database Fragmentation in SQL 2000 vs SQL 2005
• Index Fragmentation Report in SQL Server 2005 and 2008

For our test case we’ll just use the following simple query to get this information.

SELECT object_name(object_id) as tablename,
       index_id,index_type_desc,
       avg_fragmentation_pct,page_count,page_count*8/1024 as "size(mb)" 
FROM [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL,  NULL, DEFAULT) 
WHERE object_name(object_id) = 'testtable' and index_id=2

Here is the output from this query. You can see from the results below that after the initial load the nonclustered index on this table is quite fragmented. This is the index we will work with for this test.

For our test we will use three different types of queries to see if there is any difference in performance after the maintenance. The first will be a straight SELECT with a single value in the WHERE clause. The second will be a SELECT with a range of values in the WHERE clause. Finally we’ll test a count of the indexed column so the database engine will have to perform an index scan. Here is the T-SQL for this test.

SELECT col2 FROM testtable WHERE col3=55627;
SELECT col2 FROM testtable WHERE col3 BETWEEN 72000 AND 75000;
SELECT count(col3) FROM testtable;

Let’s run these statements now before doing any maintenance so we have a baseline we can use for comparison. The statistics for each query can be gathered using SQL Profiler. Here are the baseline test results.

Now that we have our baseline captured we can test both the reorganization and rebuild performance impacts. In order to ensure the database, and more specifically the table and indexes, is in the exact same state for each test we’ll take a full backup of the database that we can restore after performing each database maintenance task. Here is the complete list of tasks for the test scenario. Note that SQL Profiler was running throughout the execution of the test scenario so that we could capture the SELECT statement performance metrics.

-- REBUILD test
-- After this completes run query against [dm_db_index_physical_stats] to
-- get fragmentation stats
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);
SELECT col2 FROM testtable WHERE col3=55627;
SELECT col2 FROM testtable WHERE col3 BETWEEN 72000 AND 75000;
SELECT count(col3) FROM testtable; 
RESTORE DATABASE [TestDB] FROM DISK = N'C:\TestDB.bak' 
 WITH FILE = 1, NOUNLOAD, STATS = 5;
-- REORG test
-- After this completes run query against [dm_db_index_physical_stats] to 
-- get fragmentation stats
ALTER INDEX [idx_testtable_col3] ON [dbo].[testtable] REORGANIZE 
  WITH ( LOB_COMPACTION =  ON );
SELECT col2 FROM testtable WHERE col3=55627;
SELECT col2 FROM testtable WHERE col3 BETWEEN 72000 AND 75000;
SELECT count(col3) FROM testtable;

Let’s first take a look at the [dm_db_index_physical_stats] query results. We can see from the below results that as suspected the page count has been reduced quite a bit. The interesting thing that I noticed in these results was that the reorganization statistics are quite close to the rebuild statistics. I would have thought that the reorganization would not have been able to perform this much cleanup and was expecting the reorganization numbers to fall somewhere in the middle between our baseline and the rebuild statistics.

More importantly let’s take a look at the SQL Profiler results.

The results of this test are quite interesting. We can see that we have pretty much the same query performance for all three test queries after both types of index maintenance. In fact we see pretty much the same performance even compared with the baseline queries we ran. The only exception where we see the benefit of the maintenance seems to be with the index scan. This is to be expected since we have fewer pages that make up the index so the database engine can perform the scan using fewer reads. The next question is: Does the same hold true for other DML statements?

Next Steps

• Extend this test to other DML operations – INSERT, UPDATE, DELETE (watch for another tip)
• 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
• Learn more about general database maintenance

Ben Snaidero

Ben Snaidero has been a Database Administrator for just over 10 years. Starting out working mainly with Oracle he got into SQL Server in 2005 and has worked primarily with SQL Server for the last 3 years. His main focus with both Oracle and SQL Server is in the area of performance tuning.

• MSSQLTips Awards: Achiever (75+ tips) – 2018 | Author of the Year Contender – 2016-2017