DROP INDEX and CREATE INDEX vs ALTER INDEX REBUILD

3

December 11, 2014 by Kenneth Fisher

I saw someone ask a few weeks ago if it was faster to drop and re-create an index or do an ALTER INDEX REBUILD. I’m not sure, although I suspect they will take about the same amount of time. And what do I do when I’m not sure how something is going to work? I try it out!

The hardest part of this is figuring out what exactly to test. In order to do a somewhat complete test I want to test both clustered indexes (CI) and non-clustered indexes (NCI). However I also need to test a table with a single index (CI vs NCI) and one with multiple indexes running a separate set of tests on both the CI and NCI indexes. I don’t feel like the fragmentation level will make any difference and REBUILD ONLINE doesn’t have the same functionality as DROP and CREATE.

I’m going to use a copy of Sales.SalesOrderDetail and build new indexes on ProductId int and CarrierTrackingNumber nvachar(50) to get a reasonable sized index.

Initial Setup
SELECT * INTO IndexTest
FROM AdventureWorks2014.Sales.SalesOrderDetail
GO

Clustered Index Test Single Index

Setup

CREATE CLUSTERED INDEX ci_Test ON IndexTest(ProductID, CarrierTrackingNumber)
GO

DROP and CREATE

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
DROP INDEX IndexTest.ci_Test;
GO
CREATE CLUSTERED INDEX ci_Test ON IndexTest(ProductID, CarrierTrackingNumber);
GO

ALTER INDEX REBUILD

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
ALTER INDEX ci_Test ON IndexTest REBUILD;
GO

Results

-- DROP and CREATE
Table 'IndexTest'. Scan count 3, logical reads 1566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 281 ms,  elapsed time = 680 ms.
   
-- ALTER INDEX REBUILD
Table 'IndexTest'. Scan count 2, logical reads 1580, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 157 ms,  elapsed time = 581 ms.

No major differences here. To be honest I ran this code over and over again and kept coming up with different times (IO stayed the same of course). Over all it looked to close to call.


Non Clustered Index Test Single Index

Setup

DROP INDEX IndexTest.ci_Test
CREATE NONCLUSTERED INDEX nci_Test ON IndexTest(ProductID, CarrierTrackingNumber)
GO

DROP and CREATE

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
DROP INDEX IndexTest.nci_Test;
GO
CREATE NONCLUSTERED INDEX nci_Test ON IndexTest(ProductID, CarrierTrackingNumber);
GO

ALTER INDEX REBUILD

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
ALTER INDEX nci_Test ON IndexTest REBUILD;
GO

Results

-- DROP and CREATE
Table 'IndexTest'. Scan count 3, logical reads 1566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 218 ms,  elapsed time = 194 ms.
   
-- ALTER INDEX REBUILD
Table 'IndexTest'. Scan count 2, logical reads 494, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 110 ms,  elapsed time = 98 ms.

Hmm, non clustered indexes showed a big difference here. Elapsed time was consistently about half for the REBUILD and IO is around 1/3.


Clustered Index Test Multiple Index

Setup

CREATE CLUSTERED INDEX ci_Test ON IndexTest(ProductID, CarrierTrackingNumber)
GO

DROP and CREATE

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
DROP INDEX IndexTest.ci_Test;
GO
CREATE CLUSTERED INDEX ci_Test ON IndexTest(ProductID, CarrierTrackingNumber);
GO

ALTER INDEX REBUILD

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
ALTER INDEX ci_Test ON IndexTest REBUILD;
GO

Results

-- DROP and CREATE
Table 'IndexTest'. Scan count 3, logical reads 1566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 253 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'IndexTest'. Scan count 3, logical reads 1566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'IndexTest'. Scan count 2, logical reads 1584, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 484 ms,  elapsed time = 1104 ms.
   
-- ALTER INDEX REBUILD
Table 'IndexTest'. Scan count 2, logical reads 1584, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 188 ms,  elapsed time = 395 ms.

Wow, BIG difference here. Not really surprising when you think about it though. When you drop or add a clustered index all of the non-clustered indexes have to be rebuilt. This is because the clustered index columns are in each non clustered index. (Read here and here.) Since the rebuild doesn’t require changing the clustered index this doesn’t apply. Of course if rebuilding all of your indexes is your goal then this isn’t a huge issue. Of course there is an ALL option on ALTER INDEX REBUILD if that’s what you need.


Non Clustered Index Test Multiple Index

Setup

-- No setup required as both indexes are already in place.

DROP and CREATE

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
DROP INDEX IndexTest.nci_Test;
GO
CREATE NONCLUSTERED INDEX nci_Test ON IndexTest(ProductID, CarrierTrackingNumber);
GO

ALTER INDEX REBUILD

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
ALTER INDEX nci_Test ON IndexTest REBUILD;
GO

Results

-- DROP and CREATE
Table 'IndexTest'. Scan count 2, logical reads 1584, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 156 ms,  elapsed time = 182 ms.
      
-- ALTER INDEX REBUILD
Table 'IndexTest'. Scan count 1, logical reads 441, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 110 ms,  elapsed time = 185 ms.

No difference in time here but big difference in IO. This is important since with larger tables or slower IO subsystems the IO difference is likely to make a time difference also.


Summary

Over all it looks like ALTER INDEX REBUILD is the better way to go. With consistently lower IO and frequently better time (or at worst similar times) it appears to be the better way to go. Of course as always this is what I’m seeing and it’s a good idea to run your own tests. Your mileage may vary.

3 thoughts on “DROP INDEX and CREATE INDEX vs ALTER INDEX REBUILD

  1. Gabriel says:

    Regarding test #2 (Non Clustered Index Test Single Index), I got the same result when comparing drop/create with just rebuild. However, when I compared drop create with *disable*/rebuild, the two operations performed identically.

    This leads me to believe that when rebuilding an enabled index, there are shortcuts being taken when compared to building it from scratch. Disabling an index effectively invalidates it and a subsequent rebuild ends up being the same as a straight create operation.

    This is an important point for bulk loads, where indexes should be disabled or dropped to maximize insert throughput. It appears that there is no performance difference either way, the only consideration being loss of metadata in the case of a drop.

    • Excellent point and thanks for the addition. I think your analysis is probably correct. My understanding is that as soon as you disable an index it is completely invalidated and has to be rebuilt from scratch. This would make it the same as a drop from a REBUILD point of view.

      I will say that the “loss of metadata” part on the drop vs disable is a big deal in my opinion. Once way back when (sql 2000/2005) I had to write code to remove & store all index definitions and then reapply afterwards. DISABLE is much easier.

      Thanks again for the comment!

  2. Richard says:

    Hello,

    It’s good to know I am not the only person who is in a learning path. I’ve been maintaing an SQL server for the past 8 years, but been extensively learning for the past 1,5 years. My original background has been Oracle for the past 20 years.
    Nice article, although a couple of points here I could add.

    1) You need to take the CREATE/ALTER into the context of what you are doing and when. I think the REBUILD has been created to be utilized by the built in Maintenance Plan. Also it is easier to build a script to use the REBUILD option than use DROP/CREATE index.

    2) The REBUILD option should I think by design be faster. I cannot comment on SQL Server, but the Oracle theory is that the the engine reads the existing index (not data from scratch), creates the new one, once the process is done the old index is dropped.

    3) One thing missed here – REBUILD will require allocating new database/file blocks thus expanding the database first (and TEMPDB) and during the allocation of extents there will be some overhead. So the question on which is faster – DROP/CREATE of REBUILD will depend on the size of the objects and the contex.

    4) Sometime DROP/CREATE is unavoidable, when you want to copy your whole database from one server to another dusing an upgrade process and you have a table already created with indexes it would add a huge wait if you had to import 20 mln rows. You will have to DROP the indexes, import the data, then CREATE indexes. This would be faster. Again – consider this in the context of what you are doing.

    5) DROP/CREATE is sometimes unavoidable. I once used the REBUILD option in an Oracle databse and queries were stuck. It looked like the index was corrupt and the only way to fix the problem was not to use REBUILD. Happened only once during my 20 year life experience with Oracle, but it can happen anytime anywhere.

    6) Speed is not the only factor you should be worried about. To many of us seem to be a bit obsessed with this subject.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: