Post reply

Fill Factor

  • November 10, 2017 at 7:07 am

    #402662

    Dear Experts,
    Regarding the Fill Factor on indexes, Does SQL apply the Fill Factor value of an index to the heap/table related to that index. If yes,then which Index's fill factor does it consider if there are multiple indexes on the table ? Thank you.

  • November 10, 2017 at 7:12 am

    #1967519

    If the table has a clustered index, that index will have its own fill factor, so that will, in effect, be the fill factor for the table.  If the table is a heap, there's no clustered index and so the table doesn't have a fill factor (although each non-clustered index on the table will).

    John

  • November 10, 2017 at 7:56 am

    #1967527

    John Mitchell-245523 - Friday, November 10, 2017 7:12 AM

    If the table has a clustered index, that index will have its own fill factor, so that will, in effect, be the fill factor for the table.  If the table is a heap, there's no clustered index and so the table doesn't have a fill factor (although each non-clustered index on the table will).

    John

    Thanks John. So when an insert happens, does it take into consideration the available free spaces in the existing pages or new pages are allocated at the end , for the incoming data ?

  • November 10, 2017 at 8:00 am

    #1967530

    In a heap, new rows are inserted at the end.  Is that what you mean?

    John

  • November 10, 2017 at 8:29 am

    #1967533

    John Mitchell-245523 - Friday, November 10, 2017 8:00 AM

    In a heap, new rows are inserted at the end.  Is that what you mean?

    John

    No , I mean for a table with a clustered Index and/or non-clustered indexes(s) ?

  • November 10, 2017 at 8:36 am

    #1967534

    In a clustered or a non-clustered index, if there is not sufficient space on a page to insert a row, a page split occurs.

    John

  • November 10, 2017 at 12:21 pm

    #1967570

    Arsh - Friday, November 10, 2017 7:56 AM

    John Mitchell-245523 - Friday, November 10, 2017 7:12 AM

    If the table has a clustered index, that index will have its own fill factor, so that will, in effect, be the fill factor for the table.  If the table is a heap, there's no clustered index and so the table doesn't have a fill factor (although each non-clustered index on the table will).

    John

    Thanks John. So when an insert happens, does it take into consideration the available free spaces in the existing pages or new pages are allocated at the end , for the incoming data ?

    Data is stored on whatever page where the logical sort order dictates.  If there is free space on the page (CI or NCI), then the insert will occur there.  If not, a "bad" page split will happen unless the new row is at the logical end of the sort order in which it will be added to the "last" page if there's room or do a "good" page split to add a new page to the end of the logical sort.

    --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)

  • November 10, 2017 at 12:26 pm

    #1967571

    Jeff Moden - Friday, November 10, 2017 12:21 PM

    Arsh - Friday, November 10, 2017 7:56 AM

    John Mitchell-245523 - Friday, November 10, 2017 7:12 AM

    If the table has a clustered index, that index will have its own fill factor, so that will, in effect, be the fill factor for the table.  If the table is a heap, there's no clustered index and so the table doesn't have a fill factor (although each non-clustered index on the table will).

    John

    Thanks John. So when an insert happens, does it take into consideration the available free spaces in the existing pages or new pages are allocated at the end , for the incoming data ?

    Data is stored on whatever page where the logical sort order dictates.  If there is free space on the page (CI or NCI), then the insert will occur there.  If not, a "bad" page split will happen unless the new row is at the logical end of the sort order in which it will be added to the "last" page if there's room or do a "good" page split to add a new page to the end of the logical sort.

    And, the information in each index is stored according the definition for fill factor within that index. So, if we add a row to a clustered index, obviously, that fill factor is taken into account there. Then, if we also have a nonclustered index on a column within that same table, then that insert uses that fill factor, not the other one. Each one is defined and managed independently as Jeff describes above.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • November 10, 2017 at 7:30 pm

    #1967622

    Grant Fritchey - Friday, November 10, 2017 12:26 PM

    Jeff Moden - Friday, November 10, 2017 12:21 PM

    Arsh - Friday, November 10, 2017 7:56 AM

    John Mitchell-245523 - Friday, November 10, 2017 7:12 AM

    If the table has a clustered index, that index will have its own fill factor, so that will, in effect, be the fill factor for the table.  If the table is a heap, there's no clustered index and so the table doesn't have a fill factor (although each non-clustered index on the table will).

    John

    Thanks John. So when an insert happens, does it take into consideration the available free spaces in the existing pages or new pages are allocated at the end , for the incoming data ?

    Data is stored on whatever page where the logical sort order dictates.  If there is free space on the page (CI or NCI), then the insert will occur there.  If not, a "bad" page split will happen unless the new row is at the logical end of the sort order in which it will be added to the "last" page if there's room or do a "good" page split to add a new page to the end of the logical sort.

    And, the information in each index is stored according the definition for fill factor within that index. So, if we add a row to a clustered index, obviously, that fill factor is taken into account there. Then, if we also have a nonclustered index on a column within that same table, then that insert uses that fill factor, not the other one. Each one is defined and managed independently as Jeff describes above.

    I might be misunderstanding you, Grant.  Inserts don't have anything to do with Fill Factor except they take advantage of any free space available on the page that's enough to hold the row being inserted.  If the page is full, then it doesn't matter what the Fill Factor is on the index and the page will split to accommodate the insert.  The only thing I know of where the Fill Factor comes into play is when you rebuild the index.

    If I've misunderstood what you're trying to say, my apologies.

    --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)

  • November 11, 2017 at 1:44 am

    #1967630

    Jeff Moden - Friday, November 10, 2017 7:30 PM

    Grant Fritchey - Friday, November 10, 2017 12:26 PM

    Jeff Moden - Friday, November 10, 2017 12:21 PM

    Arsh - Friday, November 10, 2017 7:56 AM

    John Mitchell-245523 - Friday, November 10, 2017 7:12 AM

    If the table has a clustered index, that index will have its own fill factor, so that will, in effect, be the fill factor for the table.  If the table is a heap, there's no clustered index and so the table doesn't have a fill factor (although each non-clustered index on the table will).

    John

    Thanks John. So when an insert happens, does it take into consideration the available free spaces in the existing pages or new pages are allocated at the end , for the incoming data ?

    Data is stored on whatever page where the logical sort order dictates.  If there is free space on the page (CI or NCI), then the insert will occur there.  If not, a "bad" page split will happen unless the new row is at the logical end of the sort order in which it will be added to the "last" page if there's room or do a "good" page split to add a new page to the end of the logical sort.

    And, the information in each index is stored according the definition for fill factor within that index. So, if we add a row to a clustered index, obviously, that fill factor is taken into account there. Then, if we also have a nonclustered index on a column within that same table, then that insert uses that fill factor, not the other one. Each one is defined and managed independently as Jeff describes above.

    I might be misunderstanding you, Grant.  Inserts don't have anything to do with Fill Factor except they take advantage of any free space available on the page that's enough to hold the row being inserted.  If the page is full, then it doesn't matter what the Fill Factor is on the index and the page will split to accommodate the insert.  The only thing I know of where the Fill Factor comes into play is when you rebuild the index.

    If I've misunderstood what you're trying to say, my apologies.

    Thanks Jeff, John and Grant. Jeff , I suppose Grant means that free space is created by the Fill Factor. It can also be created due to fragmentation if I am not wrong. Please clarify one more point... Does SQL store partial row-data in a page ?  I mean if it sees that the free space in a page is not enough to store the complete row but the free space in the next page can be utilized to accommodate it ? (I know Oracle does row-chaining and row-migration).. Thank you.

  • November 11, 2017 at 5:23 am

    #1967635

    For more clarity on this point , when a new row is inserted , why does it have to be inserted in the 'Index Page' ? The Index page stores only the values of the Index Key . Please correct if I'm missing something. I think this clarification helps in understanding fill factor internals. Also, if the  underlying table has clustered index and a non-clustered index , who's fill factor does it apply to the underlying table? thank you.

  • November 11, 2017 at 7:27 am

    #1967638

    Jeff Moden - Friday, November 10, 2017 7:30 PM

    I might be misunderstanding you, Grant.  Inserts don't have anything to do with Fill Factor except they take advantage of any free space available on the page that's enough to hold the row being inserted.  If the page is full, then it doesn't matter what the Fill Factor is on the index and the page will split to accommodate the insert.  The only thing I know of where the Fill Factor comes into play is when you rebuild the index.

    If I've misunderstood what you're trying to say, my apologies.

    No, no, I mangled that a bit. The core was the fill factor on each index controls that index. Thanks for the clarification.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • November 11, 2017 at 10:52 am

    #1967641

    Arsh - Saturday, November 11, 2017 5:23 AM

    For more clarity on this point , when a new row is inserted , why does it have to be inserted in the 'Index Page' ? The Index page stores only the values of the Index Key . Please correct if I'm missing something. I think this clarification helps in understanding fill factor internals. Also, if the  underlying table has clustered index and a non-clustered index , who's fill factor does it apply to the underlying table? thank you.

    The clustered index is the table. Once you have a clustered index you don't have a separate, underlying table.

    And a new row has to be inserted into index pages because all nonclustered indexes MUST be consistent with the underlying table. Hence a row gets inserted into the table (heap or clustered index) and then into all of the nonclustered indexes before the insert is considered complete.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 11, 2017 at 1:21 pm

    #1967645

    Here's an easy way to think about it... Fill factor simply describes the amount of free space you want to leave on each page of a given index...
    That free space is there so that the page can updated or inserted into, without having to split.

    So, to you original question, SQL Server "primarily" (I want to say "only" but Gail, Grant or Jeff are likely to point out an exception that I'm missing) looks at fill factor when you're rebuilding or reorganizing an index. During those operations it will rearrange the contents of each page to reestablish the specified ratio of data to free space. Outside of that, SQL Server will ignore fill factor and place newly inserted/updated rows on the pages it wants to put them on. If that page doesn't have room for the new data, there will be a page split.

    That means SQL Server will not split a page in order to maintain the fill factor... If it did, that would completely negate the benefit derived by lowering the fill factor in the first place.

  • November 11, 2017 at 2:03 pm

    #1967648

    Arsh - Saturday, November 11, 2017 5:23 AM

    For more clarity on this point , when a new row is inserted , why does it have to be inserted in the 'Index Page' ? The Index page stores only the values of the Index Key . Please correct if I'm missing something. I think this clarification helps in understanding fill factor internals. Also, if the  underlying table has clustered index and a non-clustered index , who's fill factor does it apply to the underlying table? thank you.

    Rather than repeating that which has already been so well written, please see the links at the following article... it's a bit dated but, for the questions you're asking, it will provide the necessary answers.

    https://technet.microsoft.com/en-us/library/ms180978(v=sql.105).aspx

    For more information about the limits on indexes and other things, please see the following.

    https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server#Engine

    And, no... SQL Server cannot split rows across pages.

    And FILL FACTOR only affects data at index creation or during an index rebuild.  It does not limit the amount of data that can or will be stored on a page.  It only sets the amount of initial free space left on the leaf level of pages when the index is created or rebuilt.

    As for FILL FACTOR, please see the following.

    https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index

    As for rebuilding indexes (where Fill Factor comes into play the most), it's usually not worth it unless the percent of page fullness has dropped to something less than 70%.

    --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)

Viewing 15 posts - 1 through 15 (of 17 total)

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