Is the table too small?
Fragmentation figures are meaningless for small tables that span up only a few extents
This is captured by @minPageCount in the script. The default is 8: meaning it will be skipped if one extent in size.
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
Best Answer
Rebuilding an index takes more CPU than reorganizing it. It locks the database so that has to taken into account. Indexes should be rebuilt when the fragmentation is more than 40% or so. After that it becomes too slow and cumbersome for the server to reorganize. You should reorganize an index when the fragmentation is roughly 10%-40%. So healthy index is basically less than 10% fragmentation, little bit less than 10% perhaps.
These are my rules of thumb and they have worked for me, but they are guidelines. So many things change the performance so you need to find out what woorks for you in your environment.
I think that you can rebuild an index online nowadays, at least if you use the Enterprise version of SQL Server, the keyword ONLINE if I am not mistaken.