none
Is there any way to estimate Index Rebuild Time RRS feed

  • Question

  • I am trying to rebuild index on a view of size around 16 GB and it has been running since last 5 hours. 

    Is there any way to estimate how long it would take. 

    My database is on Service Tier Standard and Performance level S2.

    Thanks!

    Friday, August 11, 2017 5:03 AM

All replies

  • Of course we can hardly even guess without a ton more information, but I'll say I'm not surprised that reindexing 16gb would take five hours on an S2.  Suggest next time you need to do that, raise up to at least a P4 first, that's 10x the DTUs and would probably run about 10x faster.  $2.50/hr.  But it will still be slow on a P4, at least 30 minutes - I would guess.

    Probably best NOT to do it right now, or all that work will be rolled back and you will have to start over.  My guestimate is that you're at least 50% done.  I'd even say, hmm, 85% or more.  Now I'd like to learn if that turns out to be right!

    Josh



    • Edited by JRStern Friday, August 11, 2017 5:17 AM
    • Proposed as answer by Askwizard Monday, August 14, 2017 4:30 AM
    Friday, August 11, 2017 5:14 AM
  • Yes, you were right Josh, it took 5 hrs 50 mins to complete.

    Thanks for the advice for upgrading the database to P4 but the only reason why I don't like upscale and downscale a big database ( this database is over 60 GB), it kills all connections at the moment when it switches the performance level and there is no way you can track when it's going to do that and you have to wait for this process to complete before running any other process on that database.

    Mukesh

    Monday, August 14, 2017 12:13 AM
  • --Index Defrag
    SELECT   r.percent_complete, 
       estimated_finish_time = DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP),  
     t.[text]FROM   sys.dm_exec_requests AS r CROSS APPLY  
     sys.dm_exec_sql_text(r.[sql_handle]) AS t WHERE   r.session_id = <SPID>;

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, August 14, 2017 4:01 AM
  • Yes, you were right Josh, it took 5 hrs 50 mins to complete.

    Whoo-hoo!

    Thanks for the advice for upgrading the database to P4 but the only reason why I don't like upscale and downscale a big database ( this database is over 60 GB), it kills all connections at the moment when it switches the performance level and there is no way you can track when it's going to do that and you have to wait for this process to complete before running any other process on that database.

    Mukesh

    Yes, of course that's right.    It helps if you have a maintenance window daily or weekly where you can do these things.  But we don't either.  And that's why when I first got going on Azure, one of the first things I screamed about was exactly this, that you can't even do standard table maintenance under Azure because service level changes are too disruptive and lower performance levels throttle system maintenance tasks excessively.  It remains an immense problem on Azure - even with online rebuilding.

    Josh


    • Edited by JRStern Monday, August 14, 2017 7:21 AM
    Monday, August 14, 2017 7:19 AM
  • Maybe you should tell us in more details about it
    Monday, August 14, 2017 7:28 AM