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.