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

Question

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

Jeff Moden

SSC Guru

Points: 1003903
More actions
August 8, 2019 at 12:00 am

#3668367

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

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

Mike Byrd Default port Points: 1458 More actions · Answer 1

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 More actions · Answer 2

Todd Gronevelt

Newbie

Points: 2

August 8, 2019 at 12:45 pm

#3669855

Thanks Jeff.

roger.plowman SSChampion Points: 10265 More actions · Answer 3

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: 1003903 More actions · Answer 4

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.

The table must be empty either as a new or truncated table. Common for a lot of ETL.
The table must only have a clustered index. Common for a lot of ETL.
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.

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)

Mike Good SSCertifiable Points: 7415 More actions · Answer 5

Mike Good

SSCertifiable

Points: 7415

August 8, 2019 at 1:22 pm

#3669890

Great post!

NBSteve Hall of Fame Points: 3994 More actions · Answer 6

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: 1003903 More actions · Answer 7

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.

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)

DinoRS SSCrazy Points: 2683 More actions · Answer 8

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: 1003903 More actions · Answer 9

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.

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)

Paul White SSC Guru Points: 150467 More actions · Answer 10

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!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1003903 More actions · Answer 11

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.

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)