Does index rebuild time depend on the fragmentation level?

Question

Is the required time for index rebuild dependent on the level of fragmentation?

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

I am asking for the RUNTIME (for example in seconds) that may be required to perform the required action, not about which action is required in what particular situation. I am aware of basic best practices when index reorg or rebuild / statistic updates should be done.

This question does NOT ask about REORG and the difference between REORG and REBUILD.

The background: Due to setup of different index maintenance jobs (each night, heavier job at the weekends...) I wondered if a daily "light intense" OFFLINE index maintenance job should be better performed on low-middle fragmented indexes to keep the off-times small - or does it not even matter and the rebuild on a 80% fragmented index might take the same off-time as the same operation on the same index 40% fragmented.

I followed the suggestions and tried to find out myself what is going on. My experimental setup: On a test server doing NOTHING else and not being used by anyone or anything else I created a table with a Clustered Index on a uniqueidentifier primary key column with some additional columns and different data types [2 numerics, 9 datetime, and 2 varchar(1000)] and simply added rows. For the test presented I added about 305,000 rows.

Then I used an update command and randomly updated a range of rows filtering on an integer value and changed one of the VarChar Columns with a changing string value to create fragmentation. After that I checked the current avg_fragmentation_in_percent level in sys.dm_db_index_physical_stats. Whenever I created a "new" fragmentation for my benchmark, I added this value including the physical_page_count value to my recordings the following diagram is made of.

Then I Ran: Alter index ... Rebuild with (online=on); and grabbed the CPU time by using STATISTICS TIME ON into my recordings.

My expectations: I expected to see at least the indication of a kind of linear curve that show a dependency between fragmentation level and cpu time.

This is not the case. I am not sure if this procedure is really appropriate for a good result. Maybe the number of rows / pages are too low?

However the results indicate that the answer to my original question definitely would be NO. It looks like the required cpu time SQL Server needs to rebuild the index is neither depending on the fragmentation level nor depending on the Page Count of the underlying index.

The first chart shows the cpu time required to REBUILD the index in comparison to the previous fragmentation level. As you can see the average line is relative constant and there is not at all a relation between fragmentation and required cpu time observable.

To respect the possible influence of the changing number of pages in the index after my updates that could require more or less time to rebuild, I calculated FRAGMENTATION LEVEL * PAGES COUNT and used this value in the second chart that shows the relation of required cpu time vs. fragmentation and page count.

As you can see, this also does not indicate that the required time to rebuild is influenced by the fragmentation even if the number of pages varies.

After making those statements I guess my procedure must be wrong because the cpu time required to rebuild a huge and highly fragmented index then might only be influenced by the number of rows - and I do not really believe in this theory.

So, because I really and definitely want to find this out now, any further comments and recommendations are very welcome.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

I know that it's old thread, but I think it will be beneficial to share Paul Randal's post here.

Algorithm Speed

An index rebuild will always build a new index, even if there’s no fragmentation. The length of time the rebuild takes is related to the size of the index, not the amount of fragmentation in it.

https://www.sqlskills.com/blogs/paul/sqlskills-sql101-rebuild-vs-reorganize/

Answer 5

Is the required time for index rebuild depending on the level of fragmentation?

I believe this will not be the major parameter on which SQL server will decide and takes time to rebuild\re-organize the index:

There are various other factors involved based on "DATA" via which it decides for how much time will it take: Parameters like

Factor 1: Table size

Factor 2: Availabililty concerns

Factor 3: Partitioning

Factor 4: Index columns and uniqueness

If you want to read more on these factors you can refer here.

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

Again the answer can be it Depends! For the numbers you will need to test the scenario and see the outputs how it goes. Track such details like for FRAG level 80 , rebuild took X hrs\mins\secs and for Frag level 40, rebuild took Y hrs\mins\secs. Calculate and retain the history say over 15 days, (depends upon the maintenance activity scheduled) and you may to a conclusion on how much time its actually taking in comparing the both.

Additionally :

You can gather the data\calculation on the index rebuild progress:

either using DMV sys.dm_exec_requests OR

If you have Ola's Maintenance plans for Re-indexing-Re-organizing, there is an option to save the history of the actions performed during maintenance within table CommandLog as explained in SQL Server Index and Statistics Maintenance. Once the data is saved, you can query for command type `ALTER_INDEX--REBUILD' and difference for the same between columns START TIME and END TIME

Answer 6

Yes, because usually a rebuild needs to scan the original index in order while streaming the rows (in order) into a new physical index partition. Fragmentation hurts uncached scans, so yes the rebuild is going to take longer.

How much longer depends on the fragmentation and on how CPU bound the whole process is. Serializing rows is quite CPU intensive so it might not matter at all. Or, you might be getting random IO rates of typically 1.5MB/sec which is easily 5-10x slower than a fast rebuild would be (depends on schema and data). Depending on the assumptions you make you can probably contrive anything between 1x and 100x slowdown.

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?

It's not a linear relationship. The fragmentation metric is a very rough proxy for how much time it takes to scan a partition.