SQL Server Performance

Pls verify my "page split" thinking

Discussion in 'Performance Tuning for DBAs' started by merrillaldrich, Mar 22, 2006.

  1. merrillaldrich New Member

    I am tuning fill factors, and my brain is slightly fried, so I wonder if someone can confirm this:

    If a table is clustered on an identity column, then all the inserts will happen at the "end, " and no update will ever change the location of a record (identity cannot be updated). So the best fill factor would be 0 (or 100), so all pages are full.

    On the other hand, an index without that condition, on a table that undergoes inserts / updates, would want to have some space on each data page to accomodate changes throughout the data -- fill factor 90 or 85.

    Am I correct?

    OR

    In the case of a table clustered on identity, might the size of a row change when, for example, a VARCHAR column is updated, causing a full data page to split because of the larger row?
  2. Twan New Member

    yes, varchar column increases can cause a page split in a table with a clustered index on an identity field.

    I personally don't tend to change the fill factor from its default too much, as I don't have masses of updates happening anyway, but others here may be able to advise more on best fillfactors to use...

    factor 0 or 100 is only really suitable for read-only tables...

    Cheers
    Twan
  3. satya Moderator

    http://www.sql-server-performance.com/jc_sql_server_quantative_analysis5.asp fyi.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. merrillaldrich New Member

Share This Page