Category: Indexing

Financial Advice

When I talk about “data access patterns” in relation to databases, it’s often met with blank stares.

What is a data access pattern? What does it have to do with indexes?

Well, my dear friend, today you’re gonna learn it good and hard.

Medical Doctor

Data access patterns refer to the most common ways that queries filter, join, order, and display data.

The earliest point of a data access pattern is your where clause. Different queries may have different patterns of data to look for.

Giving some generic OLTP-ish examples, you might have queries that look for:

• Customer orders
  • Within a date range
  • Ordered by most recent
• Items in a customer order
  • With the total price
  • Plus shipping
  • Plus tax
• Items in stock
  • Total quantity

Depending on how normalized your data is, getting some of this stuff will likely require 2-3 tables getting joins together in some manner.

But all of these different scenarios define your data access patterns, and this is how you need to gear your indexes.

A lot of people get caught up on the minutiae of indexes without taking care of any of the basics, worrying about foreign keys, GUIDs, fragmentation, and other ridiculous memes.

Personal Trainer

Your data access patterns should define your indexes, because that — along with well-written queries to use those indexes most efficiently — is what’s going to make your application fast.

I’ve blogged about some of the fundamental concepts behind this in the past:

• Let’s Design An Index Together Part 1
• Let’s Design An Index Together Part 2
• Let’s Design An Index Together Part 3
• A General Indexing Strategy For Normal Queries
• Predicate Selectivity and Index Design
• Rethinking Key Column Order

If you still need help with your index design after reading those, drop me a line! That’s the kind of thing I love helping people out with.

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.

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.