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?”
“Is there a way I can just run a job, and have something automatically clean my indexes up?”
Those of us who got our start on Windows back in the days when it ran on spinning rusty magnetic frisbees called “hard drives” used to run apps to defragment the data. These apps would shuffle bytes around, getting them all lined up into contiguous rows. The idea was that if your data was all in a row, then your spinning rusty magnetic frisbees would be able to read the data faster. See, the way they worked internally was that they had a head that physically moved around the drive, reading one place at a time, just like a record player.
What’s a record player?
Music used to be stored on vinyl discs. No, not the compact ones – those were plastic. This is before that. No, even before tapes. These vinyl discs had little grooves in them that would cause a needle to vibrate, and …
NO I’M NOT MAKING THAT UP, IT WAS REALLY A THING.
Lemme just stop there. The whole “defragment your drives” advice is really, really old, that’s what I’m saying. It stems from a day and age where technology was completely different than it is today. Your SQL Server’s data files probably don’t live on spinning rusty magnetic frisbees anymore, and even when they do, they’re scattered amongst lots of other data files – even from different servers all sharing the same drives. All these different data files are being accessed at the same time. The whole reason we used to defragment our data is just dramatically less relevant today.
Today, a lot of your data is stored in RAM. You know what that first R stands for? Of course you don’t, kid, just like you don’t remember that cars used to have record players. <sigh> RAM doesn’t stand for Sequential Access Memory, and you don’t need all your data pages lined up in a row in RAM. A table’s pages aren’t all lined up in a row in RAM, nor does that matter. (Does fragmentation matter if you hit disk? Yes, but then you’re screwed anyway.)
To make matters worse, a lot of folks try to “fix” fragmentation by setting fill factor lower than 100%, purposely leaving empty space on each 8KB data page so that they can add more data later. When they do that, they:
- Make the database larger (which also makes their backups, restores, corruption checking, index rebuilds, etc all take longer)
- Make their memory smaller (because each 8KB data page will have a percentage of empty space, and that space is cached in RAM as well)
- Make table scans take longer (because more logical reads are involved)
- Give a unicorn heartburn
So what’s a DBA supposed to do?
Leave the server-level fill factor at the default 100%. If you can prove that the default is causing you a problem on a specific index, I’m okay with lowering it on specific indexes, but I’m going to make you prove you made things better instead of worse. If you want to do that, read Jonathan Kehayias’ post on how to track page splits with Extended Events, and make sure to read the comments too.
Run Ola Hallengren’s index maintenance scripts weekly. We talked about these free tools when we were covering backups and restores, and they’ve got more tricks up their sleeve: index reorganization, rebuilds, and statistics updates. I’m not a fan of their defaults, though: start with these less-aggressive defaults instead. Avoid running ’em more than once a week, though, unless you can prove that rebuilding your indexes & updating your stats is fixing things (rather than making ’em worse.)
And if your mentor told you that you have to run daily index rebuilds in order to fix execution plans, I’ve got good news and bad news. The bad news is that your mentor was wrong – but the good news is that I’ll teach you the real problem over the next couple of episodes.
Wanna learn more? Here are a few more resources for this episode’s topic:
- Why Defragmenting Your Indexes Isn’t Helping – a 1-hour video where I explain the two kinds of fragmentation, show why one kind does matter, and show in more detail why setting fill factor is such a bad idea
- What’s So Bad About Shrinking Databases? This common maintenance task actually causes fragmentation, which is especially terrible if you’re doing both this and defragmenting your indexes.
- Columnstore Online Index Rebuilds – Columnstore indexes need more attention to maintenance. Niko Neugebaeur is the go-to for in-depth columnstore tips, and in this post, he analyzes the online rebuild process and its overhead. (You’re definitely gonna want online index rebuilds when you’re dealing with big data warehouses.)
Now that we’ve got the right indexes in place, the next episode will tackle how queries are executed.