none
Time it takes a rebuild index

    질문

  • Hi,

    I need to perform a rebuild of at least 100 indexes that are found with a percentage of framentacion over 90%, the problem is that I have windows of time limits.
    There is some way to calculate how long the index rebuild will take to get a total estimate of the 100 that need maintenance.

    2018년 7월 13일 금요일 오후 4:32

모든 응답

  • >the problem is that I have windows of time limits.

    Why do you have time limits? Index rebuild/reorganize can be configured to run as Online operation, which has negligible impact on database. No need to take an outage for index maintenance. Just run during off-peak hours like evenings or weekends, to improve speed.

    Reorganize and Rebuild Indexes

    Otherwise, the only way to get an accurate time estimate is to run index maintenance against a copy of the database in dev/test environment, if available.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • 편집됨 philfactor 2018년 7월 13일 금요일 오후 7:18
    • 답변으로 제안됨 Naomi NModerator 2018년 7월 16일 월요일 오전 12:25
    2018년 7월 13일 금요일 오후 4:42
  • No. 

    However I would highly recommend you use Ola's scripts which have a time limit before starting another one.

    See "timelimit".  It will finish the current index and not start another if the timelimit is exceeded:  

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    • 답변으로 제안됨 Naomi NModerator 2018년 7월 16일 월요일 오전 12:25
    2018년 7월 13일 금요일 오후 4:50
    중재자
  • SORT_IN_TEMPDB = ON, ONLINE = ON

    You can use above 2 features to rebuild it fast and Availability of index

    For ONLINE = ON

    Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation


    mohammad waheed

    2018년 7월 13일 금요일 오후 6:23
  • Note that sort in tempdb will only add anything if the storage subsystem is configured so you get more IOPS by adding the tempdb files for this work. If the storage is shared, then it won't gain anyting.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    2018년 7월 14일 토요일 오후 6:20
    중재자
  • Hi,

    I need to perform a rebuild of at least 100 indexes that are found with a percentage of framentacion over 90%, the problem is that I have windows of time limits.
    There is some way to calculate how long the index rebuild will take to get a total estimate of the 100 that need maintenance.

    While you cannot directly pre-determine the time the index rebuild operation is going to take, you can, however, track the progress while the rebuild is running to estimate how much time it is going to take to run to completion. As Phil mentioned, you can run the rebuild on a copy in a lower environment and track the progress using the profiler or extended events as given below. 

    1. Using profiler- Progress Report-Online Index Operation

    2. Extended Event- Progress_report_online_index_operation 

    Note: the above is helpful in case of Online rebuilds (enterprise edition only) if you're running offline rebuild, see this post on how you can do the calculation. Again, these may not be very accurate.

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    2018년 7월 14일 토요일 오후 10:27
  • Hi,

    I need to perform a rebuild of at least 100 indexes that are found with a percentage of framentacion over 90%, the problem is that I have windows of time limits.
    There is some way to calculate how long the index rebuild will take to get a total estimate of the 100 that need maintenance.

    Good day Camilo,

    In addition to previous great responses, SQL 2017 brings a new solution for this exact case (and other cases) using the resumable index rebuilds (Applies to SQL Server 2017 and Azure SQL Database).

    This feature enable you to configure the exact time that the procedure run using the parameter MAX_DURATION. Once the time is over the process will be paused, and you can continue it at any time you want (for example if you have low activities for 15 minutes that you can pause after 15 minute and continue next time you have low activities). You can also Pause manually at any time during the process.

    For more information please check this link to the official Book On Line:

    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-2017#resumable-indexes


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    2018년 7월 15일 일요일 오전 2:28
    중재자
  • Hi,

    I need to perform a rebuild of at least 100 indexes that are found with a percentage of framentacion over 90%, the problem is that I have windows of time limits.
    There is some way to calculate how long the index rebuild will take to get a total estimate of the 100 that need maintenance.

    There is no way sorry. But make sure whatever indexes you are rebuilding is not small I mean if page_count in index is < 2000 there is no need to rebuild or reorganize at all.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    2018년 7월 15일 일요일 오전 6:29