Transcript

Interview question number two

What is one reason that a query against a partitioned index might be slower than against a non-partitioned index?

Answer

I love this question, in part because it’s kind of hard to answer! I actually really like the hard questions, this one to me is a little difficult.

Why can a partitioned index make a query slower?

When you partition an index, you are changing the index structure. We’re saying, we want this index to be broken into partitions, or chunks. Now, every partition of the index is still ordered by the key of that index, but overall, all of the partitions are ordered by the partitioning column, which may be different from the leading key column in my index.

To use the data in these partitions, SQL Server has a more complicated job with the partitioned index. Sometimes it may have to check every partition, and it may make some bad decisions about how much it needs to read in each of those partitions sometimes.

Occasionally, we do need to tune it, because the structure of the partitioned index is just different than the non-partition index.

This one’s tricky because, at least for me, I always want to draw it out

This is the kind of question that if I was in an in-person interview, I would be like, is it okay if I use the whiteboard a little bit?

Because actually drawing out the boundary points and the little index diagrams, I feel like would help me explain it. I feel like it’s easier than using words.

If you’re in a phone screen, or you don’t have a whiteboard, then you might take the attempt like I did at just doing it verbally, and check in and ask, did that, you know, did I make that clear? Because I do think this is a tricky one to convey in words.

You could take a different approach on this one

You could say, I’m going to pick a specific scenario and explain that optimization problem with top min/max, for example. You could choose to say, I’m going to do a detailed example. Against a rowstore index, SQL Server is really good at figuring out it can go straight to the end of the index, and do a backward scan. Whereas, when this is broken up into chunks, it isn’t as good at figuring out it can do that same trick within each chunk without a rewrite.

There’s a couple of different approaches you could take with this, you could either be general, like I was, or pick a specific problem, and dig into that one.