Post reply

Optimization jobs

  • December 1, 2009 at 11:21 am

    #139753

    When we reorg or rebuild indexes, more space would get consumed on the disk also right?

    Sometimes, if the space available is less, then on running the rebuild or reorg then we may fall completely short of space on the disk itself.

    Are there any links available in Microsoft or any sites which would confirm this.

    Please advice.

    M&M

  • December 1, 2009 at 1:33 pm

    #1086091

    Indexes are rebuilt by constructing a new copy of the index and then deleting the old one.

    This means that essentially there are two copies of the index for a short period of time.

  • December 1, 2009 at 2:55 pm

    #1086144

    Here are a couple of articles about indexes

    http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx

    http://www.sqlservercentral.com/articles/Indexing/68439/

    And a previous SSC forum discussion on the topic

    http://www.sqlservercentral.com/Forums/Topic604239-146-1.aspx

    The forum discussion contains a link to a MS whitepaper on the subject.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • December 2, 2009 at 12:29 pm

    #1086710

    Just one doubt, in SQL server 2005 we have reorganize, rebuild index tasks in maintenance plans

    But in SQL server 2000 when we run optimization jobs, does it do rebuild of index or reorg of index.

    M&M

  • December 3, 2009 at 8:13 am

    #1087110

    mohammed moinudheen (12/2/2009)

    Just one doubt, in SQL server 2005 we have reorganize, rebuild index tasks in maintenance plans

    But in SQL server 2000 when we run optimization jobs, does it do rebuild of index or reorg of index.

    You need to explicitly specify what type (if any) of index maint you want in your maintenance plans in any version of sql server.

    I also counsel you to NOT use maintenance plans for indexing because they do ALL indexes even if they don't HAVE any need to be reorg/rebuilt. Wasted effort. There are numerous scripts you can find online to do conditional index maint. BOL even has one.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • December 4, 2009 at 3:21 am

    #1087558

    Michelle Ufford, aka SQLFool, has a very well regarded script to do what the Guru recommends;

    http://sqlfool.com/2009/06/index-defrag-script-v30/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply