Spinning Disk Mentality In SQL Server: Why You Don’t Need To Defragment Indexes Or Change Fill Factor

Pervasive

---

I often have to talk clients down from odd ledges. At this point, there’s about 20 years of advice about SQL Server out there, and for various reasons received wisdom often gets migrated en masse, whether it’s still applicable or not.

Outdated trace flags and settings are pretty common, and don’t get me started on coding practices.

But I get it — that stuff came along with advice that still makes sense — like parallelism and memory settings, separating off tempdb, etc.

Unfortunately, it often leads people to the “it used to be fast so it should always be fast” trap based on what used to work, rather than what makes sense currently. Or even what the current problem is.

And to avoid making any meaningful changes, they often double down on bad ideas and flail around with nonsensical ones.

Surprise and Dismay

---

Some advice made a lot of sense when you had servers on old spinning disks, and 32bit software with 2-3GB of RAM available for user space tasks.

You just couldn’t cache much data, and every time those disk heads had to pick up and spin about, things got creaky. Modern storage tends to avoid such dilemmas, but people still treat it like a record player that might skip if they rub a little too much funk on their Roger Rabbit.

Things like changing fill factor and constant index maintenance just aren’t the problem solvers they used to be, back when I/O — especially the random variety — was quite a nuisance to accomplish. SSD, Flash, and RAM just don’t have those moving pieces for you to concern yourself with.

I’m not saying there’s not a time a place to make those changes, but I am saying that the ROI on them is much lower than it used to be.

Pick On

---

Not surprisingly, I see people doing quite irresponsible things without measuring any metric particular to the what setting(s) they’re changing. The only expected outcome seems to be nods of approval if it “seems faster” or “got a little better”.

This process also seems to avoid determining what actual problems are, and focusing on a bit of advice from one of three blog posts by an author from 2009 where none of the pictures load and the code formatting is just italicized text.

And hey, look, if that’s your fetish, cool. There’s certainly some invaluable gems out there that Microsoft has managed to not delete yet, or migrate for the eleventeenth time and break every link in existence by tacking a GUID to the end of it.

The conversation usually goes something like:

“Why is fill factor 60 on every index?”

“To cut down on page splits.”

“Did you have a lot of those?”

“I don’t know, xxVanWilderFan420xx said they were bad and we should avoid them.”

Well, okay then.

I suppose cargo culting around things that don’t work at least makes you look busy.

After all, you can just copy and paste that italicized code and F5 your way to nowhere.

And Chew

---

I’m probably guilty of this too, with things I don’t quite understand or I’m not knowledgeable about.

A while back I had an issue with Windows BSODs constantly, and all the advice I could find came from posts on NVIDIA forums (dated 2012, of course) that suggested rebuilding the ESET database and doing a clean install of the drivers.

Did I try it? You bet I did.

Did I try it more than once? You’re on a roll if you said yes.

But all it did was prolong fixing the real problem, which turned out to be some RAM that had gone bad.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.