Why isn't partition-level lock escalation the default?

Question

In SQL Server, locks are normally escalated from row or page -> table. Starting with SQL Server 2008, a new level of lock escalation was added - partition level.

However, this isn't automatically enabled for partitioned tables - by default, the table is set to skip partition locking and go right from row or page -> table. Why would this be the case? Is there a reason why I wouldn't want to switch all my tables from TABLE to AUTO so that they escalate locks to partition level instead of to table level?

Since the default is still TABLE, I figure there must be something I'm missing about the downside of AUTO.

Answer 1

BOL has answer -- It is not ON by default as it may increases the potential for deadlock.

The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks. In SQL Server 2008, locking of partitioned tables can escalate to the HoBT level for the associated partition instead of to the table lock. A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition.

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.