• Replies 2 replies
  • Subscribers 336 subscribers
  • Views 62 views
  • Users 0 members are here
Related

UP_REBUILD_ALL_INDEXES

David Vivino (Past Member)
$organization

Is UP_REBUILD_ALL_INDEXES necessary if I’m already running the standard SQL Server Rebuild Indexes Maint Plan on a weekly basis?

  • $organization

    Hi David

    Both methods produce the same result, so if you use Rebuild Indexes Maint Plan for DB Impresario then do not need to run Tessitura's proc. UP_REBUILD_ALL_INDEXES.

    A couple of notes:

    1.
    As of now Tessitura's  proc. UP_REBUILD_ALL_INDEXES uses DBCC DBREINDEX which I think will be modified soon because (a quote): Microsoft recommends avoiding the use of DBCC DBREINDEX as this feature will be removed in a future version of SQL Server.

    2.
    In UP_REBUILD_ALL_INDEXES they apply a custom fillfactor=70% for several tables (like T_TRANSACTION) ; the Rebuild Indexes Maint Plan uses the fill factor value last specified for the index --so essentially preserves it, if you select "Reorganize pages with the default amount of free space". So, we are OK with the fillfactor  too. 

    You can check this out the existing  fillfactor value by running for instance:

    select name, fill_factor
    from sys.indexes where OBJECT_id =OBJECT_id ('T_TRANSACTION')

    Thanks,

    Simon

    sbasyuk@carnegiehall.org

     

  • $organization

    Wow, thanks Simon!  Great answer!!

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Simon Basyuk
    Sent: Monday, September 24, 2012 12:24 PM
    To: Vivino, David
    Subject: Re: [Tessitura Technical Forum] UP_REBUILD_ALL_INDEXES

     

    Hi David

    Both methods produce the same result, so if you use Rebuild Indexes Maint Plan for DB Impresario then do not need to run Tessitura's proc. UP_REBUILD_ALL_INDEXES.

    A couple of notes:

    1.
    As of now Tessitura's  proc. UP_REBUILD_ALL_INDEXES uses DBCC DBREINDEX which I think will be modified soon because (a quote): Microsoft recommends avoiding the use of DBCC DBREINDEX as this feature will be removed in a future version of SQL Server.

    2.
    In UP_REBUILD_ALL_INDEXES they apply a custom fillfactor=70% for several tables (like T_TRANSACTION) ; the Rebuild Indexes Maint Plan uses the fill factor value last specified for the index --so essentially preserves it, if you select "Reorganize pages with the default amount of free space". So, we are OK with the fillfactor  too. 

    You can check this out the existing  fillfactor value by running for instance:

    select name, fill_factor
    from sys.indexes where OBJECT_id =OBJECT_id ('T_TRANSACTION')

    Thanks,

    Simon

    sbasyuk@carnegiehall.org

     

    From: David Vivino <bounce-davidvivino4732@tessituranetwork.com>
    Sent: 9/21/2012 10:18:38 AM

    Is UP_REBUILD_ALL_INDEXES necessary if I’m already running the standard SQL Server Rebuild Indexes Maint Plan on a weekly basis?




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!