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.
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.
CPU time = 281 ms,  elapsed time = 680 ms.
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.
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.
CPU time = 218 ms,  elapsed time = 194 ms.
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.
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.
CPU time = 203 ms,  elapsed time = 253 ms.
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.
CPU time = 484 ms,  elapsed time = 1104 ms.
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.
-- No setup required as both indexes are already in place.
CPU time = 156 ms,  elapsed time = 182 ms.
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.
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.
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.
« TSQL Tuesday #61: Giving Back, you gain more than you give.
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.
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.
Is there a difference between ALTER TABLE REBUILD and ALTER INDEX ALL REBUILD?
1) The fact that you are using ALL in the second one means that the nonclustered indexes will be rebuilt as well.
2) ALTER TABLE REBUILD rebuilds a heap. ALTER INDEX REBUILD rebuilds a clustered index. You can't rebuild a heap with ALTER INDEX and you can't rebuild a clustered index with ALTER TABLE.