SQL University – Indexing Week

Welcome back to this week’s installment for SQL University. The topic this time is all about indexing. I know, I know. It sounds incredibly exciting, right? I mean, what could possibly be more exciting than a comprehensive review of a B-tree? Or a lively discussion about having a heap or a clustered index?

Lots of things, actually. But since we are still in session here at SQL U, let’s get through this chapter in our books so we can move on to some of the more exciting stuff, like FILESTREAM.

tempdb

I still recall the first time I even heard the term “index”. I was working with a particularly troublesome piece of T-SQL code and I couldn’t find the /gofaster switch. I recalled that the DBA had once helped me before so I decided to try my luck again and I went over to ask for some help (which, of course, he was happy to provide, as any good DBA would be). Examining my query he indicated that it could benefit from an index or two and he showed me the details of their creation. I was all to happy to get them created and so I did. I deployed them right away. And then my phone rang. It was the DBA and he wanted to remind me of something.

“You know that you already have seven indexes on that table, and you just created two more?”

“Um, I guess so. Is that going to be a problem?”

“I’m not sure, but if you find something in your batch or your reports running slower, let me know and we’ll see if we can adjust from there.”

And so began my awareness of indexes. And just how awesome it is to have a good DBA around.

That’s where you need to be as well. You should be familiar with the basics of indexes, how tables are designed, and how the data is accessed. Don’t bother with XML or spatial indexes at this point, stick to things like clustered, non-clustered, unique, filtered, and included columns for now. Get a solid understanding of the basic structures before trying to move onto more specialized things such as XML, spatial, and full-text.

msdb

Sometimes indexes are exactly what you need. Other times, indexes are exactly what you don’t want. And the only way for you to know what you need, and when, is through experience. You can read all you want from a book about indexing, but the bottom line is your shop is unique, and your situation is not always going to fall into line with a scenario in a book. That means you need to find out what works for you, your business, and your scenario. That way, when people come to you for performance help, you will be able to better understand the role that indexes play in performance.

I recall one time where we had an web application getting time outs in production, but test was running “fine”. Turns out that “fine” meant the page loaded in 27 seconds, whereas the production version would cough up a timeout error after 30 seconds (the query in prod would finish in about 35 seconds). Naturally this was a database problem, and not a design problem, or a data problem (or even an ASP/IIS problem). Well, OK, it was a data problem, because the data in prod and test was not the same (imagine that!) But that didn’t help our real issue, which was why the hell was that query taking so long in the first place?

This is where my knowledge of indexes came into play. I could see that the index itself was not aligned with what the query was requesting. If I altered the order of columns in the index (because we couldn’t change any code), then the query time went below one second. Had I not had any awareness of how indexes worked, then I would have never thought to verify that the query and the index were properly aligned, for lack of a better term. That awareness allowed me to get the job done (and I have lost count of the number of times readjusting the column order for the index has been a quick solution).

[Note: I am not telling you to go around and rearrange the columns in all your indexes in order to get a performance gain, I am just pointing out how awareness of how indexes work will have a benefit for you as you perform the duties of your job. If you went around implementing everything you read on a blog then your shop would make BP look well organized.]

model

At this level you need to do more than just get the job done, you need to be leading by example. Here is where you take a turn and look to be proactive. If you already have some type of in-house monitoring system (or a third-party tool instead), you can go through the tables in your databases, examine the indexes, and see where you are able to provide some help. What does that mean? Well, here are some examples:

1. Check for missing and unused (or little used) indexes
2. Check for duplicate indexes
3. Check for too many indexes (how many is too many? it depends!)

Being proactive is the key here; getting ahead of problems before they become your problem, and only your problem (as opposed to the people that most likely designed and deployed their poor decisions that lead to extra work, it’s never their problem otherwise they would help work on a solution instead of just standing there and watch you work on the solution).

master

OK, you’ve got everything in place already. You have awareness, you have knowledge, you have some experience, and you are proactive. What’s next?

Teaching others.

Now, this doesn’t mean that you need to go on the lecture circuit, or write a book on indexing, or even a bunch of articles or blogs. No, what I mean here is that when someone is sitting in your cube and looking for help you need to be able to explain how indexes work in a way that they can understand. This will accomplish two things. First, it will force you to learn a lot about indexes (if you ever want to really learn something then all you need to do is to try teaching that subject to someone else). Second, it will allow for you to help plant a seed of knowledge with another person (whether that person chooses to nurture that seed is up to them, not you).

So, start thinking about different ways that you could explain indexes. Start with a common example, like the index in a back of a book, or the card catalog at the library. Pick something that works for you, because that will make it easier for you to then in turn explain it to others.

My favorite example? Why, the pork bellies index, of course. But you need the right crowd to pull that one off. If you are trying to explain indexing to someone at the PASS Summit, just use an example that refers to a karaoke song book, they’ll understand that rather easily.

resourcedb

There are lots of references out there about indexes. You could start with the Books Online just to get an idea about them and then move onto TechNet, MSDN, or even SQLServerPedia. It’s all good material, especially for someone that is looking to get the fundamentals down.

But for me, if I ever want to know the sordid, dirty details of indexes, I only have to think of what I call K2; Kimberly (blog | twitter) and Kalen (blog).

There are others that are quite knowledgeable about indexes, no question. But the top of my index pyramid is with them, and no one else.