Whenever I want to check a setting in SQL Server I always prefer to find it using a query.  Every once in awhile these settings turn out to be on the verge of being undocumented.  This is the case with table level lock escalation (also known as partition level lock escalation).  The T-SQL query below brings back the information you’re looking for.

One other bit of trivia with regard to partition level lock escalation.  The default is “TABLE” even when the table is partitioned.  Although this may seem like an oversight on Microsoft’s part, it is in fact intentional with the goal of preventing deadlocks in situations the original developers didn’t account for.

Wondering what the differences are between the three different LOCK_ESCALATION levels (be it TABLE, AUTO, or DISABLE)?  See the bottom of this article for links to the best articles on the topic.

–The following query finds the lock_escalation for SQL Server tables.

SELECT
SCHEMA_NAME(SCHEMA_ID)AS SchemaName
, t.name AS TableName
, lock_escalation_desc
FROM
sys.tablesAS t
WHERE
t.schema_id=SCHEMA_ID(‘YourSchema’)
AND
t.name =‘YourTable’

Looking for more information on partition level lock escalation?  Check the links below:

The following post should be your first choice.  The easy to understand examples provide real insight into how partition level lock escalation works and perhaps most importantly, demonstrates a common scenario where it can cause a deadlock.  It turns out there’s a reason LOCK_ESCALATION is set to “TABLE” by default.

SQLskills.com: SQL Server 2008: Partition-level lock escalation details and examples

Looking for the nitty gritty details on lock escalation?  This MSDN article is the place to go if you want to be able to impress your friends at the next MS SQL User Group.  (Do you know the threshold for lock escalation?  Typically, locks are escalated once 5,000 locks are acquired.)

 MSDN: Lock Escalation(Database Engine)

And finally, last but not least: Are you wondering what the difference is between the lock escalation levels of TABLE, AUTO, and DISABLE?  The details below are excerpted from the MSDN article ALTER TABLE (Transact-SQL)

SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

Specifies the allowed methods of lock escalation for a table.

AUTO

This option allows SQL Server Database Engine to select the lock escalation granularity that is appropriate for the table schema.

• If the table is partitioned, lock escalation will be allowed to partition. After the lock is escalated to the partition level, the lock will not be escalated later to TABLE granularity.
• If the table is not partitioned, the lock escalation will be done to the TABLE granularity.

TABLE

Lock escalation will be done at table-level granularity regardless whether the table is partitioned or not partitioned. This behavior is the same as in SQL Server 2005. TABLE is the default value.

DISABLE

Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity.