How to see the Current fill factor of an index?

  • Tejwant Kaur-333539

    SSCommitted

    Points: 1647

    Hi All,

    I am just wondering if you could tell me how/where to see what the current fill factor of an index is?

    Also, How/what measures are taken to determine whether the current fill factor is enough or not?

    Thanks,

    TK

  • Tom Goltl

    SSCertifiable

    Points: 5705

    Read up on dbcc showcontig.  Fill factor needs to take into account what the clustered key is, and what kind of insert/update/delete activity is going on in the database.  For instance, if the table has an Identity as the clustered key then I make my fill factor 100%.  There's no reason to have free space on any of the currently used pages since no insert will ever happen on those pages.  There are plenty of threads on this topic.

    Tom

  • Tejwant Kaur-333539

    SSCommitted

    Points: 1647

    Thanks. I did find this article, and it's pretty good. -

    http://www.quest-pipelines.com/newsletter-v5/0204_B.htm 

    The only thing is I want to see how to find the current fill factor size? This is because the database has been handed over to me and there is no documentation telling what the fill factor was set to - overall database and for individual tables.

  • colin.Leversuch-Roberts

    SSC Guru

    Points: 52551

    If you script the index any fill factor will be included in the script. You can also examine index properties from within EM to see this.

    Setting fill factors is an art in its own right, to be honest if you're asking this question then you probably shouldn't be even thinking about fill factors.

    Tom, you're actually wrong as the clustered index is actually the entire row, regardless of how many columns of the table are defined as the clustered index, so the assumption that as the key is a sequential non updateable column there can be no fragmentation is flawed.

    To test this create a table of an identity + a 1000 varchar with only 20 chars used of the varchar, this will pack around 300 rows per page so you'd need to populate with at least 5k rows.

    If you then start increasing the varchar column up to 1000 chars you will get fragmentation.

    Agreed if you only ever insert then there will be no fragmentation, but that's a heap ( with or without a clustered index )

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Tom Goltl

    SSCertifiable

    Points: 5705

    Thanks for the good info.  Although if you do just insert you will get fragmentation, only it will come at the end of the page.  As the last page fills it will split and continue to split.  Thanks again, I wouldn't have thought about packing of the varchar fields.

    Tom

  • colin.Leversuch-Roberts

    SSC Guru

    Points: 52551

    actually I didn't explain myself very clearly I now see. I should have said if you then update selected rows increasing the varchar from 20 to 1000 you'll get fragmentation due to splits.

    This is known as deferred vs inplace updates , an inplace update does not create a row, a deferred update has to delete the existing row and create a new row, this is far more expensive in terms of locking, resource, contention etc. and of course creates fragmentation.

    I have to say that I can't remember when the last DBA I interviewed for a role could describe or explain this. ( or knew what it was )

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • wensheng_zou

    Newbie

    Points: 1

    select * from sys.indexes where fill_factor<>0

Viewing 7 posts - 1 through 7 (of 7 total)

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