What does fragmentation mean? How does it happen? Can you fix it with fill factor? Should you rebuild your indexes to fix it?
At the SQLBits conference, I tried a new way of explaining it using markers and pieces of whiteboard paper as database pages, and doing inserts live on the fly.
What you see onscreen is actually what the in-person audience saw up on the projector. Prior to the session, I rigged up a tripod and camera aimed down at the desk so the audience could watch me work.
I had so much fun with this session, and I’m so proud of the results. I want to thank SQLBits for recording the sessions and making them freely available on YouTube, and the sponsors for making it possible!
23 Comments. Leave new
Love the automated subtitles, you can learn so much more than expected.
Around the 4 minute mark for instance, you can learn that it is not SQL Server allocating paging, but instead it is the secret service. They’ve definetly kept that part secret!
A simple yet effective explanation of fragmentation. Thanks Brent.
So, based on what you’ve said at the end of the presentation about Reorganizing it seems to be meaningless, because it will just take care of external fragmentation….
Phenomenal presentation. Clear and concise, easy to grasp – I didn’t feel like I was falling behind at any point – the white-erase tablets worked well as aids.
Thanks, glad you liked it!
Great way to explain! Innovative and well done!
Thanks, glad you enjoyed it!
Thanks for the great explanation!
I think choosing the correct data type size is important to avoid internal fragmentation.
What is your opinion?
Why? Can you give an example?
Yes,
Create Table Test
( ID Bigint, LetsAssumetheSumoftheOtherColumns Char(3100) )
You should not be using large CHAR datatypes. I’m going to be blunt here: I’m not even sure where you’ve seen data types like that in use. If you’re just talking theory, let’s focus on real life practice instead. Thanks for understanding.
Yes,
It is only in theory.
OK. You and I have interacted a few times here, and I need you to respect my time. If you have a real-world problem you’re facing, by all means, let’s talk about that – but otherwise, let’s not talk theory about something you’ve never seen in real life.
I would hate to be in a position where I need to ban you from adding comments on the blog. Fair?
Fantastic presentation.
I had to laugh at the explanation of why the Fill Factor setting is still present – the part about the magnetic spinning rusty frisbees.
Thanks, glad you liked it!
I love the live demo pages.
I really like the explanation on physical fragmentation, how fill factor affects it, and why now that we just throw the metal disks around the office as frisbees, it no longer matters. I can send this off to one of my dev colleagues who is still worried about external fragmentation in the age of the cloud.
This should be published in the fragmentation docs of sql server!
Great and easy to grasp explanation! ?
Thanks!
Thanks, glad you liked it!
I agree that setting a server level fill factor other than the default of 100 (0) is absolutely the wrong thing to do.
Shifting gears a bit, At timeline 17:46 in the ‘tube above, you point out that there “edge cases” where even you have used a Fill Factor of something other than 0 (100, and not at the server level).
My question to you, good Sir, is what are some of those “edge cases”?
And, to say it out loud, I loved this presentation. Thanks for putting it together.
Thanks man, glad you liked it!
The first time I heard of one of the edge cases, Joe Sack explained it in the MCM program: a company had a table where new rows were inserted with all of the columns set to null, and then gradually the app would go back and update the columns with their contents.
For example, say you have a process that imports zip files that are uploaded by clients. When the file first comes in, you don’t know which client it’s for – you just know that it’s a file, and you have to track it from the moment it comes in.
Say it’s a dbo.IncomingFile table, and it has an Id, file name, file receipt date, and then a whole bunch of columns that you won’t know until you analyze the file’s contents – ClientID, LocationID, FileContents, etc., but those are all null when the row is first inserted.
As different processes extract it, decrypt it, and analyze the contents, more and more columns get populated, and they’re all getting populated fast and furious – but not at the same time. As a result, the new rows are constantly growing in size, so their containing pages are splitting like crazy.
Joe had an amusing answer for it: have one of the columns be Padding, varchar(1000). On insert, pad that with 1000 X’s. On update, set that column to null. Presto, the row takes up additional space when it’s first inserted, and leaves enough space behind for subsequent updates without moving the page.
That requires app changes of course, so an alternate way to do it would be fill factor – but that only makes sense if the table is relatively small, and the changes aren’t coming that quickly and furiously, and you can rebuild the indexes frequently enough. But boy, you talk about an edge case there! The Padding solution works so much better for most cases.
When Joe discussed that at first, I thought, “I’ve never seen anything like that.” And then when I got out of the MCM program, I hit that exact problem at two clients, right in a row, hahaha!
Ah… thanks, Brent. “ExpAnsive” updates are very “ExpEnsive”. 😀
Shifting gears a bit…. I didn’t get a notification that you’d replied and I don’t see the ol’ “Nofify me” box anymore. Did you stop doing notifications on this site?
Ooo, no clue – might have a problem on the site that I need to check out. Sorry about that!
Thanks for a great explanation. I can use this now to convince by boss the change fill factor from our standard 80% right now.