Some T-SQL INSERTs DO Follow the Fill Factor! (SQL Oolie)

  • Jeff Moden

    SSC Guru

    Points: 994284

    Comments posted to this topic are about the item Some T-SQL INSERTs DO Follow the Fill Factor! (SQL Oolie)

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Mike Byrd

    Ten Centuries

    Points: 1176

    Nice Jeff,

    This follows some of the patterns I've found in my upcoming Dynamic Fill Factor article.  Amazing how it all ties together.

    Mike Byrd

  • Todd Gronevelt

    Newbie

    Points: 2

    Thanks Jeff.

  • roger.plowman

    SSChampion

    Points: 10136

    Sounds more like a bug than a feature. Is this actually useful for anything beyond bar bets? Why would a single insert that breaks the rules be advantageous? Especially given the (rather unrealistic) constraints involved.

  • Jeff Moden

    SSC Guru

    Points: 994284

    roger.plowman wrote:

    Sounds more like a bug than a feature. Is this actually useful for anything beyond bar bets? Why would a single insert that breaks the rules be advantageous? Especially given the (rather unrealistic) constraints involved.

    Yes.  I posted those uses in the article along with the bar bet use.

    As for the unrealistic constraints, there's really only 3 in most cases that will cause the others to automatically come into play.

    1. The table must be empty either as a new or truncated table.  Common for a lot of ETL.
    2. The table must only have a clustered index.  Common for a lot of ETL.
    3. You must use WITH(TABLOCK) during the insert.  Not common for a lot of people because they don't know what it does for them but very easy to implement.

    To be sure, the kind of ETL that I'm referring to is the same as I spoke of in the article.  You want to import data into a staging table and because you know you'll have some expAnsive updates as you glean'n'clean the data, you want something less than a 100% Fill Factor.  It avoids the need to import the data and then rebuild the index to help avoid the performance drag that page splits cause especially if the table has a lot of rows per page.

    In that same vein, it's also handy for making copies of tables (or building large test tables) with a given fill factor  in a limited space for testing or whatever, especially on large tables, because you avoid the "freespace overhead" of the original heap or clustered table being held in place until the new clustered table is committed.

    Heh... and I do agree.  It does sound a lot like a bug but one man's bug is another man's feature and, for a lot of the stuff I have to do, is a real convenience, bar bets included 😀 .  I'll also say again that if you use it for something more important, you DO need to have a "Plan B".  It IS definitely a totally undocumented "feature" that could go away at the drop of a hat and I hope it never does because it's useful for me.

    Speaking of that, I've not tested this when SET IDENTITY_INSERT is ON... I don't know if the sort in TempDB that it causes (which has been listed as a bug on the MS Feedback site and has cost me dearly thanks to the damned non-optional TF 1117 effect in TempDB) will interfere with this "feature".

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Mike Good

    SSCertifiable

    Points: 7356

    Great post!

  • NBSteve

    Hall of Fame

    Points: 3204

    Always exciting to see a new article from The Jeff Moden and a guarantee to learn something new.  Thanks for sharing!

  • Jeff Moden

    SSC Guru

    Points: 994284

    Thank you for the feedback, folks.  It'll be interesting to find out if anyone uses it for the things that I've started to use it for or maybe even a new use that hasn't been posted yet.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

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

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