Transcript

Question 3

What are three tools or techniques that you would use to tune a query that uses a partitioned table?

Answer

Alright, let’s answer this.

Well, you’ve seen a lot of tools in this course. I really hope, as a candidate, that execution plans get mentioned.

Here’s my sample answer

If I’m tuning a query that uses a partitioned table, execution plans are going to be a great tool to help me understand if I’m getting partition elimination. If I can run the query and get an actual plan, then I can get really direct information about whether partition elimination happened on different operators.

Other tools that I might use when I’m tuning the query: I might use TSQL rewrites to make sure that I can get that partition elimination, or I might use the special partition function to control the access pattern and help the optimizer see what it should do with each partition.

I can also tune this by changing indexes. I could test out what happens against a partitioned columnstore index, maybe non-clustered, depending on the table. And I could potentially do a non-aligned rowstore index, but there’s some tradeoffs, because having non-aligned indexes limits what we can do with the partitioned table.

This is a fun question to answer, and my challenge on this would be stopping myself and remembering that I probably should not answer for five minutes. So, I tried to keep it short.

I think this is a really fun question to ask folks to see if they say they have experience doing query tuning, how they would approach this.

If they don’t talk about partition elimination, you can do probing questions and say, well, how can you tell if the query is using one partition or more partitions? You may have to probe to dig in there and see how they’re thinking about it, and what their experience has been with the different tools in SQL Server.