Results 1 to 6 of 6

Thread: Fill Factor

  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Fill Factor

    SQL Server allows for a lot of defaults, so anyone can get up and running quickly

    In DB2 (wel z/os) you mostly have to specify everything, including fill factor

    Does anyone have a general rule of thumb how they code fill fgactor for OLTP tables

    Heap tables

    Code Tables?

    Yeah, Yeah, it depends.

    What do you?

    And you can say nothing, 0 or 100 is ok to

    Someone will say, check the amount of fragmentation to determine, but that's after the fact.

    Please just post what you do today

    Thanks
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I generally don't touch the default fillfactor. If a table needs to be brought down in size, or is a read-only archive, I will specify 100 for a fill factor on the rebuild of the index, but that is about it for me.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Default on the Build is 0/100 meaning the same thing

    Had a Prod DBA tell me to have a fill factor between 50-90.

    And Since a Large majority use Identity Columns, a fill factor of 100 makes sense

    You won't be incurring page splits

    One thing they did mention is that updates may cause a split if the row size increases

    Or if you use a Natural Key, you might want to leave room at the bottom of pages for INSERTS

    But I betcha, 90% of all Development is using 0
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Your prod DBA may be falling for one of the classical blunders. The first of which, is of course, never to get involved in a land war in Asia. The second (and only slightly less important) is that fillfactor only comes into play, when you are building or rebuilding an index. If the prod DBA has something that automatically rebuilds the indexes, he has a point. I have heard that 80% is a good setting for global fillfactor, but again, it depends on a lot of different things.

    Here is an example of an update causing a pagesplit:
    Code:
    create table testfill
    (col1 int identity(1, 1) not null Primary key,
    col2 varchar(8000))
    go
    insert into testfill (col2) values (replicate ('a', 3500)), (replicate ('b', 3500))
    go
    select page_count
    from sys.dm_db_index_physical_stats(db_id(), object_id('testfill'), -1, 0, default)
    go
    update testfill
    set col2 = replicate ('c', 5000)
    where col1 = ident_current('testfill')
    go
    select page_count
    from sys.dm_db_index_physical_stats(db_id(), object_id('testfill'), -1, 0, default)
    Wow. An actual use for the ident_current() function. Never been able to figure a use for it, before.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, the entire shops set to rebuilding indexes on a weekly (Saturday Morning) basis.

    I did not tell him to do so.

    He intimated that this is just "Good Common Practices" that every shop uses.

    I would have thought, it was a good practice when the fragmentation was at a high level...which I would not expected for a heap table

    My Bad was that I put a clustered index on the table (a sproc logging table) this time. The Clustered rebuild was eating up double the size of the table (in the log, tempdb?) I thought he said the datafile, because the clustered index is stored on the same page as the data.


    Which I say, so what? Is any of this true and does it really matter?

    I removed the Clustered Index..actually changed it to non unique non clustered

    I used to have no Indexes on this table
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Brett Kaiser View Post
    But I betcha, 90% of all Development is using 0
    That's a pretty safe bet. Why not? There are other db options that really get overlooked which degrade performance; like parallelism....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •