Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments   |   Related Tips: More > Indexing

Attend these FREE MSSQLTips webcasts >> click to register


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


Last Update:


signup button

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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools