8

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.

Index Fragmentation & Rebuild CPU Time Statistics

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.

Improve this question
0

6 Answers 6

Reset to default
3

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/

Improve this answer
8

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. Index Rebuild Duration

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. enter image description here

Improve this answer
2
  • I know this is like 7 years later...but I'm genuinely curious...Do you remember how you created these charts? I like this format, and I'm just curious what you used to make them.
    – Chad Baldwin
    Sep 18 at 19:56
  • Hello @ChadBaldwin as far as I remember this was based on basic Excel charts with one of the advanced designs it brings.
    – Magier
    Sep 19 at 20:56
6

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.

Improve this answer
0
4

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

Improve this answer
3
  • Kin, TY for your comment but I feel you have overseen the Core of my question. You are comparing reorg vs rebuild. I asked about a comparison of rebuild vs. Rebuild for different fragmentation Levels (ceteris paribus).
    – Magier
    Nov 27, 2015 at 23:57
  • @Magier if you carefully re-read my answer, it does answer your core question - if an index is heavily fragmented, rebuild it. The cost of doing a rebuild of a lightly fragmented is way more than doing a reorg. Also, there is no right or wrong way of addressing fragmentation by doing a rebuild or reorg, it all depends on your system availability, data, index size, disk IO subsystem, etc. Also you can easily spin up some tests as per your environment to compare rebuild vs. Rebuild for different fragmentation Levels. Can't you ?
    – Kin Shah
    Nov 28, 2015 at 3:18
  • I never asked or mentioned about REORG. It's all about REBUILD. And, yes, sure I could setup tests and try to create specific fragmentation levels to find out how long rebuild will take, but I wanted to see if anyone already knows and can just tell me the expected results of that approach.
    – Magier
    Nov 28, 2015 at 9:36
2

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

Improve this answer
1
  • @KASQLDBA I went into the statistics / log of Ola's CommandLog Table. The duration is very very random and there is no relation to fragmentation level recognizable. Since I have those values only on a production environment the required time to rebuild might a lot be influenced by other processes so this seems to deliver no general answer.
    – Magier
    Dec 2, 2015 at 19:09
0

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.

Improve this answer
5
  • 1
    @Magier good research. CPU time is never affected by fragmentation. You are testing tiny tables that are fully cached in memory so there is no reading IO at all. The test is invalid. Test with bigger tables (like 100MB) and do CHECKPOINT; DBCC DROPCLEANBUFFERS before each test. I'm interested in seeing the results, too. I once did a similar test where I measured scan speed depending on fragmentation but I do not remember the result.
    – usr
    Dec 2, 2015 at 19:54
  • Also be aware that the fragmentation number is kind of a loose indicator because what really counts is the physical disk head movement. I can imagine a lot of IO patterns that are reasonably fast but have 100% fragmentation as measured by SQL Server using its narrow definition. For example the allocation pattern 1_2_3_4 where 1-4 is scanned and _ is a hole should be fast.
    – usr
    Dec 2, 2015 at 19:56
  • what value exactly do I have to look at then? I actually get the following information from Rebuild: CPU time = 0 ms, elapsed time = 70 ms. Table 'tFrag2'. Scan count 4, logical reads 512067, physical reads 26, read-ahead reads 71209, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 8657 ms, elapsed time = 27246 ms. SQL Server Execution Times: CPU time = 8657 ms, elapsed time = 27386 ms.
    – Magier
    Dec 3, 2015 at 12:43
  • Are these times from 3 queries? It's a little confusing. You can tell from the first numbers that a lot of data is cached. Also 70ms is too short for a valid benchmark. Can you clarify what those numbers represent?
    – usr
    Dec 3, 2015 at 13:54
  • The times I mentioned came from STATISTICS_TIME and STATISTICS_IO. I am going to restart a new benchmark right now and this time I want to have proper results. So any further suggestions are very welcome. I do not understand what cleaning the data cache helps since I am note interested in getting the data back fast but re-building the index, what, afaik, has to be done on disk anyways?
    – Magier
    Dec 22, 2015 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.