Does index rebuild time depend on the fragmentation level?

For everyone interested, I have created a chart showing the index REBUILD duration of about 2500 index rebuilds within couple of weeks in relation to the fragmentation of the index and it's size in pages.

This data is based on 10 SQL Servers, hundreads of tables and on Ola Hallengren's optimizing procedures. The general threshold for rebuilding is set to 5% fragmentation.

I have cut off some of the the largest tables (10 Mi + Pages) in this statistics to make it more readable.

The chart shows the required time (duration) as size of the bubbles. The biggest bubble's values are about 220 seconds. It shows that the required time to rebuild an index is not really related to the fragmentation. Instead it seems to be more depending on the number of pages the index has. Also it indicates that low-level fragmentation is more time-consuming than higher fragmentaion.

The second chart is just zoomed into the area <= 200 K Pages. It shows the same, it takes longer for larger indexes, not for more fragmentation.

REBUILD of index does not depend on fragmentation. It drops index entirely and creates it from scratch.

REORGANZE index - is for reducing fragmentation without index rebuild, so no drop and create.

MS advises using Reorganize for 30% fragmentation or less. For higher fragmentation Rebuild is preferred.

Here is MSDN article on this: Reorganizing and Rebuilding Indexes

UPDATE

In terms of time taken to complete operation, it obviously depends on index fragmentation. Rebuilding hugely fragmented index will take less time than reorganizing; rebuilding slightly fragmented index will take much longer. I would suggest taking MS guidelines as starting point and running some tests on your tables. Breakeven point in terms of fragmentation % will depend on specific table, index size and type of data.

Do does the rebuild of a 80% fragmented index approximately take 2 minutes if the rebuild of the same index fragmented 40% takes 1 minute?

The algorithm for a REBUILD vs REORG is different. A REORG will NOT allocate new extents as opposed to a REBUILD. A REORG will work with currently allocated pages (allocates one 8Kb random page so that it can move the pages around) and moves them around and then deallocate the pages if needed.

From my SQLSkills internals (formerly IE0) notes ....

For REBUILD :

• It can use multiple CPUs - can leverage parallelism to do the work fast.
• For heavily fragmented indexes (e.g. 80% as in your example), a REBUILD will be much faster than a REORG. REBUILD will just create another copy of the index vs REORG will get bogged down in removing the fragmentation and hence will be slower. This is the reason that Paul Randal gave his general recommendation that it will be good to do a REBUILD of a heavily fragmented index.
• A REBUILD will allow you to change the recovery mode to BULK_LOGGED for minimal logging there by generating fewer log records.

For Index REORG :

• It is always single threaded. No parallelism.
• It is slower for heavily fragmented indexes and faster for lightly fragmented indexes. The cost of creating an index vs doing a reorg of a lightly fragmented index is more and hence a REORG will be faster for lightly fragmented index.
• A REORG is always fully logged operation.

Read on - Notes - SQL Server Index Fragmentation, Types and Solutions