You've heard that page splits are bad, and they're an indication that your table design is making your storage work too hard. You've heard that the right answer to fix it is adjusting fill factor lower, or doing regular index maintenance.
Before you watch the below animation, you'll wanna get up to speed with how index seeks work. Then, let's explain page splits with an animation:
Accelerated Database Recovery (ADR) is a database-level feature that makes transaction rollbacks nearly instantaneous. Here's how it works.
Without ADR, when you update a row, SQL Server copies the old values into the transaction log and updates the row in-place. If you roll that transaction back, SQL Server has to fetch the old values from the transaction log, then apply them to the row in-place. The more rows you've affected, the longer your transaction will take.
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.
I just shake my head when I see people setting fill factor in an attempt to "fix" fragmentation.
The default fill factor is 100%. That means during an index rebuild, SQL Server packs 100% of your 8KB pages with sweet, juicy, golden brown and delicious data.
SQL Server 2017 & newer have a new DMV, sys.index_resumable_operations, that show you the percent_completion for index creations and rebuilds. It works, but...only if the data isn't changing. But of course your data is changing - that's the whole point of doing these operations as resumable. If they weren't changing, we could just let the operations finish.
In the last episode, we looked at your index designs with the output of sp_BlitzIndex. You might have gotten a little overwhelmed what with all the different warnings (and all the learning resources!)
You might have thought, "Is there an easier way?"
Update May 20 - make sure to read to the end for an update.
Okay, look, it's a mouthful of a blog post title, and there are only gonna be maybe six of us in the world who get excited enough to check this kind of thing, but if you're in that intimate group, then the title's already got you interested in the demo. (Shout out to Riddhi P. for asking this cool question in class.)
Today, Jonathan Kehayias wrote about how fragmentation impacts execution plans. I'm really into performance tuning, so I read the post carefully, really interested to see how it impacted query runtime. You should read it too - it's a great post with a solid demo.
But oddly, he didn't mention the query speeds.
No, not that
It's not the F word.
Not like this embarrassing page that was somehow updated in 2017, which still tells you to reorg at 5% and rebuild at 30% fragmentation, like your data is still on a drive that looks like a record player and might still start making a clicking noise when it fails.
Some of our clients have very high forwarded record counts and aren't aware of it until they run sp_BlitzFirst and get an alert about high Forwarded Records per Second. Some of these clients are using Ola Hallengren's IndexOptimize stored procedure to maintain their indexes. This brought up a question of whether or not rebuilding a…
Even with lots of monitoring in place, we should perform periodic checks of our SQL Servers.
Think of this like "Spring Cleaning", except I would recommend that it be more frequently than just once a year. Doing it monthly might be a bit ambitious due to our busy schedules, but quarterly could be achievable.
I hate rebuilding indexes
There. I said it. It's not fun. I don't care all that much for reorgs, either. They're less intrusive, but man, that LOB compaction stuff can really be time consuming. What I do like is updating statistics. Doing that can be the kick in the bad plan pants that you need to get things running smoothly again.
Oh God, he's talking about statistics again Yeah, but this should be less annoying than the other times. And much shorter. You see, I hear grousing. Updating statistics was bringin' us down, man. Harshing our mellow. The statistics would just update, man, and it would take like... Forever, man. Man. But no one would actually…
Here's a handy flowchart for whenever you find yourself worrying about fragmentation:
*Talk to your doctor about whether fragmentation is a problem for you. Seek help for index maintenance lasting longer than four hours. Serious side effects may occur if all index maintenance is ignored completely.
Everybody wants to know about index fragmentation
It is an inescapable vortex of malaise and confusion. Like that swamp in The Neverending Story that killed the horse. Sorry if you haven't seen that movie. The horse wasn't that cool, anyway.
Neither is index fragmentation, but it's not worth losing sleep over. Or a horse.
One of the blocking scenarios I find most interesting is related to online index rebuilds. Index rebuilds are only mostly online. In order to complete they need a very high level of lock: a schema modification lock (SCH-M).
Here's one way this can become a big problem:
If you're a production database administrator responsible for backups, corruption checking, and index maintenance on SQL Server, try Ola Hallengren's free database maintenance scripts. They're better than yours (trust me), and they give you more flexibility than built-in maintenance plans.
However, the index maintenance defaults aren't good for everyone. Here's how they ship:
[crayon-6abf306f7058e440339162/]
The defaults on some of these parameters are a little tricky:
A while back, we posted about how SQL Server index fragmentation doesn't matter. That blog post generated a lot of heated discussion and has served to make people aware that fragmentation may not be the source of their woes. Sometimes, though, there are data access patterns where fragmentation really does matter. Perpetual Fragmentation Some data…
Do you rely on index rebuilds to make queries run faster? Or do you always feel like statistics are "bad" and are the cause of your query problems? You're not alone-- it's easy to fall into the trap of always blaming fragmentation or statistics. Learn why these two tools aren't the answer to every problem…
I first knew something was up when I looked at the job history for a simple maintenance plan. It had two steps:
Rebuild all the indexes in the database - this took 10 minutes each night.
Update statistics - this took 2-3 hours each night.
