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

  • Jeff Moden

    SSC Guru

    Points: 994667

    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: 1202

    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: 10149

    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: 994667

    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: 7361

    Great post!

  • NBSteve

    Hall of Fame

    Points: 3227

    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: 994667

    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)

  • DinoRS

    SSCrazy

    Points: 2516

    well if you think about what NOLOCK is supposed to do, it does kinda make sense if you have a table with an index on it. Only way for NOLOCK to work in a quick and efficient manner is actually making a Database Snapshot, everything else would take much more time. Now you have this index which you sort of make a shadow copy of (as part of your INSERT Statement) as I would assume might be mostly interesting for reasons of seeding ( in this case I'd probably simply get the last existing value) and well … Identity itself.

    I would assume the index gets a MERGE during the actual snapshot insert so take a snapshot of the table, create a "Merge Index" and exactly when this Merge Index creation kicks in, the fill factor predefined for the existing index is taken into account and the "Merge Index" filled up accordingly.

    I've not given a lot of thoughts on NOLOCK (except: simply don't … if possible) but after reading your article it kinda makes sense to me.

    I might be off on the explanation above but pretty sure something like that is happening in the background, maybe someone else have a better theory?

  • Jeff Moden

    SSC Guru

    Points: 994667

    I'm not sure where all of the talk about "NOLOCK" is coming from.  The method I demonstrated uses "TABLOCK".  The same holds true with "SNAPSHOT" and "MERGE".  None of that comes into play here.  And, I did explain in the article precisely what is happening in the background.

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

  • Paul White

    SSC Guru

    Points: 150442

    I just came across this article. Nice work, Jeff 🙂

    This hadn't occurred to me as a side-effect when I wrote the articles you linked to in the main text.

    Cheers!

  • Jeff Moden

    SSC Guru

    Points: 994667

    Thanks for the feedback, Paul.  The articles of yours that I provided a link to are awesome.  I'm still testing a bunch of things based on your excellent documentation.  Thank you for taking the time on those.  Much appreciated, especially since they allowed me to figure out why something that couldn't happen, does.

    --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 12 posts - 1 through 12 (of 12 total)

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