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.