Partition Level Locking: Explanations From Outer Space

It’s not that I don’t like partitioning

It’s just that most of my time talking about it is convincing people not to use it.

They always wanna use it for the wrong reasons, and I can sort of understand why.

Microsoft says you can partition for performance.

Partitioning large tables or indexes can have the following manageability and performance benefits.

How?

You may improve query performance, based on the types of queries you frequently run and on your hardware configuration. For example, the query optimizer can process equi-join queries between two or more partitioned tables faster when the partitioning columns in the tables are the same, because the partitions themselves can be joined.

Takeaway: PARTITION EVERYTHING

But…

When SQL Server performs data sorting for I/O operations, it sorts the data first by partition. SQL Server accesses one drive at a time, and this might reduce performance. To improve data sorting performance, stripe the data files of your partitions across more than one disk by setting up a RAID. In this way, although SQL Server still sorts data by partition, it can access all the drives of each partition at the same time.

Takeaway: This was written before anyone had a SAN, I guess?

Ooh ooh but also!

In addition, you can improve performance by enabling lock escalation at the partition level instead of a whole table. This can reduce lock contention on the table.

Takeaway: Except when it causes deadlocks

HoBT-level locks usually increase concurrency, but introduce the potential for deadlocks when transactions that are locking different partitions each want to expand their exclusive locks to the other partitions. In rare instances, TABLE locking granularity might perform better.

Why tho?

Good question! In the Chicago perf class last month, we had a student ask if partition level locks would ever escalate to a table level lock. I wrote up a demo and everything, but we ran out of time before I could go over it.

Not that I’m complaining — partitioning, and especially partition level locking, can be pretty confusing to look at.

If you really wanna learn about it, you should talk to Kendra — after all, this post is where I usually send folks who don’t believe me about the performance stuff.

Demo a la mode

To talk about this, I’ve partitioned the Votes table in the Stack Overflow database by CreationDate.

Now, partition level locking isn’t the default, you have to set it per-table. It’s not the default because of the deadlock scenarios that are talked about in the BOL link up there.

Query that

Let’s look at how updates work!

I’m going to use sp_WhoIsActive to look at the locks, with the command EXEC dbo.sp_WhoIsActive @get_locks = 1.

This query will hit exactly one year, and one partition, of data.

What do locks look like?

Confusing part, the first

We have an object lock, and a HOBT lock. Both have been granted, with a single request.

But the Object lock is only IX (intent exclusive), which means that other queries can still dance around it.

If I run a query that hits a different partition, say for the year 2014, it will finish without being blocked.

The X lock (exclusive), is just on the one partition for the year 2009. If I run a select query for that partition, it will be blocked.

Patterns

This basic pattern will continue if we cross a partitions, and hit two years of data

If we cross lots of partition boundaries

Or if we cross all of them

Confusing, part the second

When we cross a single partition boundary, this is what locks look like. I can deal with this. The HOBT X lock has two requests. One for each partition.

This’ll happen for a few more partition crossing queries.

Until we hit 2013, and then our locks change. Now we have three requests for X locks on HOBTs, and one IX. Huh. We have a bunch of X locks on pages now, too.

When we cross every partition boundary, this is what the locks end up looking like. The HOBT locks went up, we have two kinds of Page locks, and now Key locks as well.

So what’s going on with all that?

Remember that this setting changes lock escalation. Locks don’t always escalate, and SQL Server can choose to lock different partitions with different granularity.

This becomes a little more obvious with a pretty simple query!

Different locks!

 

This makes the locking a bit more clear. Some partitions have different kinds of locks, different levels of locks, and different numbers of locks.

I’m not saying there’s a flaw in Who Is Active — sys.partitions is per database, so unless We added a bunch of nasty, looping, dynamic SQL in here, we couldn’t get partition-level information.

I know what you’re thinking

These are all using the partitioning key. What happens if we change our where clause to something that doesn’t? Say, where BountyAmount is NULL, instead. That column isn’t even indexed.

Heh heh heh

Our X locks are still only on HOBTs and pages. Our object lock is still only IX!

This is exciting, because even using a non-partitioning key where clause doesn’t lead to an X lock on the object.

We still lock different partitions with different granularity, too. We have a mix of HOBT, Key, and Page locks.

Thanks for reading!

, , ,
Previous Post
So You Want a Cheap Disaster Plan
Next Post
Contest: Guess the SQL Server 2019 Release Date

5 Comments. Leave new

  • I feel quite fortunate that I seem to be able to use table partitioning for the right reasons. We’re effectively snapshotting data at regular intervals so making that interval the partition key has given us great benefits: 1)We can partition switch the data in 2)We mostly query a single interval at a time and therefore benefit greatly from partition elimination 3)It plays really nicely with clustered columnstore, again because of partition elimination.

    TLDR; it is easy to use incorrectly but when done correctly it works really well. Guess that can be said for SQL Server as a whole!

    Reply
  • Partitioning is an awesome tool for the right reasons at the right time, but people being who they are start to apply what was a great thing everywhere. Even where they don’t make sense.

    As the old saying goes when all you have is a hammer, everything looks like a nail.

    Reply
  • Now (2016 SP1) is available in lower editions and it is still simple to use (almost). I never saw somebody using it.

    Reply
  • The main problem with partitioning on SQL Server is that you cannot select explicit partitions. So partition elimination is always done by the query optimizer and works fine for simple queries. But if you have complex queries then the partition elimination get lost in the shuffle against competing index accessing.

    And then you start with heavy query optimisations to ensure a proper execution plan.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}