Transcript

Question 1

Is table partitioning good for query performance?

Answer 1

I really like this question because it’s open-ended.

If someone’s resume says that they’ve worked extensively with table partitioning and they’re just like, yeah, it’s really good for query performance, dig in with that candidate and ask: really, like, there’s no problems?

That might be an indication that they haven’t had as much hands-on experience as you might think.

Here’s how I would tackle this question

Table partitioning is great for managing large amounts of data and it can be good for query performance in the sense of: if I’ve got some slow queries during times when I’m loading data into a table or deleting it from a table because there’s lots of blocking going on between the load process and the queries, table partitioning might help performance.

If I can switch in a big chunk of data, I just require an exclusive lock for a short amount of time. The same thing with switching data out or truncating a partition.

But when it comes to the query plans and the query performance itself, outside of those data load times, sometimes if I partition a table, I might get some tricky execution plans who have a hard time getting a fast query against the partitioned object.

So, in my answer, I want to emphasize that I know what the benefits of table partitioning are– because when people say the word performance, they might just mean query runtime, right?

They might be thinking about that locking and blocking issue, and it is true that some of the benefits of partitioning could really– we don’t get rid of a locking issue, we still have to have locks to switch data in, but we might reduce a locking issue and thereby improve performance.

However, if they’re just asking about query execution plans, as you’ve seen in this course, we do have some optimization areas where we may need to tune those queries and speed them up after we partition the table.

I like to strike a balance and try to mention the good performance aspects, as well as where I may have to do some work.