Post reply

DBCC DBREINDEX

  • June 10, 2006 at 3:20 am

    #115457

    Dear all,

     

    DBCC DBREINDEX: To rebuilt all the indexes for a table.

    I executed the code like this DBCC DBREINDEX(tablename,'',70)

    "70" is the fillfactor means it will fill the index page upto 70 percent.

    I need to know why we leave this 30 percent and how it helps us.

     

    from

    Killer

  • June 12, 2006 at 6:26 am

    #643052

    Basically, you are leaving space for the data to grow.  Check BOL, but in short, when a page runs out of room it splits the page.  more splitting means more overhead, so if you leave your self room to grow.




    Curtis Smith
    SQL Server DBA
    Well in worked in Theory ...

  • June 12, 2006 at 8:41 am

    #643137

    Whe you use this option, must have in mind how often data is modified.

    This option, fillfactor is useful to optimize the performance of INSERT or UPDATE statements.

    When an index page is full, SQL Server must take time to split the page to make room for new rows (new index page must be alocated) . This option reduce page splitting.

    Use the fillfactor option on tables into which many rows are inserted or updated because clustered index key values are frequently modified.

    The fillfactor option is applied only when the index is created or rebuilt, Sql server does not maintain the specified percentage.

     

  • June 12, 2006 at 7:33 pm

    #643267

    CONVERSELY... smaller fill factors also means SELECTs have to wade through more blank pages which will slow them down.  If the table is a static lookup table (one that rarely, if ever, has changes), use FILLFACTOR = 100 for the maximum performance.  What I'm saying is that not every table should have a FILLFACTOR = 70... only the ones the have daily inserts or updates (as Maria suggested).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • June 12, 2006 at 9:04 pm

    #643275

    Thanx a lot.

    pls also help in my other posts.

     

    from

    Killer

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

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