In our last episode, we used sp_BlitzIndex @Mode = 2 to get to know the contents of our database, sorted our indexes from biggest to smallest, and started asking questions about leftover backup tables that we probably didn’t need to keep around anymore.

Now that you’ve taken out the trash, let’s have a closer look at what’s left. This time, run sp_BlitzIndex without a Mode parameter:

You get back a prioritized list of index design issues like:

• Aggressive indexes – where a lot of locking is taking place
• Hoarder – meaning someone’s been creating indexes like they’re free
• Indexaphobia – where SQL Server is begging for more indexes
• Self-loathing heaps with forwarded fetches – causing lots of extra reads for no good reason

When you’re dealing with an existing hand-me-down database that’s been in your company for years, your tables are probably facing a mix of extremes. Some tables don’t have any indexes at all, and we’re scanning the table every time we’re looking for just one row. Some tables have dozens of indexes because folks just created every recommended index willy-nilly.

This can be a little overwhelming at first, but before you start digging into the index design and learning what anti-patterns you’re facing, let’s stop for a second to ask:

Who’s supposed to manage the indexes here?

Whether you’ve got a third party application or one developed internally by a team of developers, there’s always a gray area around who’s supposed to be designing the right indexes. Going down the left side of this grid is a list of job duties involved with designing, maintaining, and hosting a database application:

The top group of tasks – designing tables, writing queries – that part is pretty straightforward. Whoever builds the application does this work. After all, if you suddenly removed a column from their table, you’d be in trouble because the application would stop working.

The bottom group of tasks – provisioning the VM, installing SQL Server, keeping it patched – that’s also pretty straightforward. It’s your job, hahaha – you’re in the DBA Training Plan, and that’s your work. It wouldn’t be fair to expect the app developers to do this job.

The middle group is where it gets sketchy.

Either of you could do it: either the app developers, or the DBA team. It just comes down to the support policy for each application. In some apps, like Microsoft Sharepoint, the customers are forbidden from screwing around with the indexes. If you have a performance problem, you basically have to throw hardware at it. In other apps, like Microsoft Dynamics, customers are encouraged to do their own index designs to support the way they use the application. To learn more about this choice, read my post on who’s allowed to add indexes to ISV apps.

Your job as a DBA is to:

1. Share the sp_BlitzIndex results with the developers
2. Say, “It looks like there are a lot of improvements we could make together. Who should drive this effort, me or you?”
3. After getting the answer, communicate it clearly to the end users, too – as in, “We’ve found a lot of index improvements we could make, and the developers will be driving that effort.” (Or you will! Either way.)

Sometimes, it’ll be somebody else’s problem – but sooner or later, the job duties are gonna fall on your lap. Let’s get started with a few simple guidelines to help get your tables performing well.

Brent’s 3 Index Guidelines

You were assigned my free How to Think Like the Engine video class in a prior homework episode, so I’m going to assume you already know the differences between clustered indexes, nonclustered indexes, and heaps. With that in mind, here are a few index guidelines to start with as you’re reviewing the output of sp_BlitzIndex:

1. Start every table with a clustered index. There will be times when you don’t need one, but when in doubt, start with one. Only remove it when you can prove that it’s a performance problem.
2. Aim for around 5 indexes or less per table. The more indexes you add, the more copies of the table you have. Granted, you’re picking which columns are in each copy – but they’re still by no means lightweight. I’ve seen databases where every insert required hundreds of writes to disk because there were hundreds of indexes per table.
3. Aim for around 5 columns or less per index. The more columns you add, the larger the index becomes. All of those columns have to be updated synchronously whenever the data changes, so in a perfect world, you avoid indexing “hot” fields that change all the time. (Yes, this includes “included” columns – they take up space in the indexes too, and they have to be updated every time they change. They ain’t free.)

These are starting guidelines,
not commandments carved in stone.

I get it – because I stand up on podiums and present, you think I’m Moses standing up on the mount, holding The Three Index Commandments. I can see why you’re confused – I look a lot like a Biblical character alright.

These are just starting points, meant to help you take the first few steps on your journey. You’re going to find plenty of scenarios along the way when you need to take a different approach. However, until you understand why those guidelines are in place, you want to abide by ’em.

Your next steps are to learn why I wrote those guidelines. These learning resources I’m about to point you to are huge – you could spend the next several months just learning about indexing. It’s up to you how deep you want to go:

If you like learning via books:

• SQL Server Query Performance Tuning – chapter 8 digs into indexing. It’s several dozen pages, and teaches you most of what you need to know to get started. Plus, if you’re going to invest in a book, I want you to be able to keep gaining from it over time, so this book will pay off in more areas of our DBA Training Plan when I refer to other chapters later.
• Expert Performance Indexing – a whole book dedicated just to SQL Server indexes.

If you like learning via blog posts:

• Stairway to SQL Server Indexes – SQLServerCentral.com’s Stairways series covers a lot of topics in digestible chunks. If you like this one, you’ll probably like the Columnstore Stairway too.
• Our indexing posts – not arranged in any particular order, though. Someday I’ll get around to organizing these into an easy-to-follow e-book. Maybe. But first, brunch.

If you prefer videos & online classes:

• Fundamentals of Index Tuning – 1-day class with hands-on labs using the Stack Overflow database on your own laptop.
• Mastering Index Tuning – 3-day class with hands-on labs, but make sure you take Fundamentals first. This one is tough.

Whoa – this is hard work, right? Figuring out the right indexes, testing to make sure they’re getting used – can’t we just defragment our indexes and call it a day? Nope, and I’ll explain why in the next episode.