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

By:   |   Comments (3)   |   Related: > Indexing


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.

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.

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.

tablename index_id index_type_desc avg_fragmentation_pct page_count size(mb)
testtable 2 NONCLUSTERED INDEX 99.1 13320 104

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.

Test Scenario CPU(ms) Reads Writes Duration(ms)
single value 0 201 0 0
by range 563 22583 0 172
index scan 1216 13566 0 351

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.

maintenance type tablename index_id index_type_desc avg_fragmentation_pct page_count size(mb)
baseline testtable 2 NONCLUSTERED INDEX 99.1 13320 104
rebuild testtable 2 NONCLUSTERED INDEX 0.03 8653 67
reorganization testtable 2 NONCLUSTERED INDEX 0.51 8709 68

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

test scenario maintenance type CPU(ms) Reads Writes Duration(ms)
single value baseline 0 201 0 0
rebuild 0 201 0 0
reorganization 0 201 0 0
by range baseline 563 22583 0 172
rebuild 545 22583 0 274
reorganization 594 22583 0 181
index scan baseline 1216 13566 0 351
rebuild 1279 8772 0 362
reorganization 1217 8956 0 336

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

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

View all my tips



Comments For This Article




Tuesday, January 17, 2023 - 8:07:09 AM - Ben Snaidero Back To Top (90833)
@Jeff Moden Honestly I can barely remember what I had for lunch last week :) That said it was mostly likely just my personal 2 core laptop I did the testing on. Unfortunately I no longer have this machine to double check.

Monday, January 16, 2023 - 9:18:38 PM - Jeff Moden Back To Top (90832)
p.s. You even have the same problem in this article after the rebuild as I did. There's actually a reason for that. Do you remember how many CPU's your machine 7 years ago had?

Monday, January 16, 2023 - 9:15:28 PM - Jeff Moden Back To Top (90831)
Man, I can't tell you how long I've been looking for an independent party to do the same testing that I've done. Different method (that's good because it's truly an independent confirmation) but same conclusion. This was tough one to find.

Thank you, Ben, for taking the time to put this article together.














get free sql tips
agree to terms