We're partitioning a handful of tables. We're hoping better concurrency will be part of the result from this change. However, even though setting the table level lock escalation to AUTO (partitions) for these tables should enhance that effect, is it something we should automatically try first?

From MSDN: "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."

Since it indicates "in rare instances", can I take from that, that we should apply partition level lock escalation to these tables from the get-go until we see adverse effects suggesting that TABLE level locks would be better? I will say, that we already know that most of our existing queries and transactions should be coded to almost always use the partition keys.

  • That's my take on "rare instances", yes. – Randolph West Oct 12 '16 at 4:21
  • @RandolphWest, I'm sure what you wrote made sense before translation, but I'm not looking for reassurance. – John Oct 12 '16 at 4:27
  • Given that it is indeed rare to see table-level locking perform better than partition-level locking on partitioned tables, and that I've seen it maybe once or twice in the decade or so that partitioned tables have been around, I'm not sure what more I could offer you. Forgive me for not being crystal clear. – Randolph West Oct 12 '16 at 17:15
  • @RandolphWest, full disclosure, I was looking for some reassurance. I took the quote to mean, go ahead (apply partition level locking). But I'm happier to hear about your actual experience in the matter, and that's good to hear. Thanks for the feedback. Feel free to post an answer as well. – John Oct 12 '16 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.