Is there a way to find the current setting for LOCK_ESCALATION on particular table? Can it be found through system views?


As far as I know, you'd have to look using SQL Server Profiler, the Lock:Escalation event

Esclation information isn't in sys.dm_tran_locks or it's predecessors

| improve this answer | |

I understand you want to know the setting.

This is easy: just run the query below. The possible values are TABLE, AUTO and DISABLE.

select t.lock_escalation_desc from sys.tables t where t.name = 'your_table';

TABLE is the default mode. On this mode, the lock goes from both PAGE or ROW directly to TABLE level.

DISABLE can be useful in some scenarios, but use it carefully.

AUTO includes an intermediate level between ROW/PAGE and TABLE: it's the PARTITION level. Instead of locking the whole table, it only locks the needed partition.

Source: https://www.sqlskills.com/blogs/paul/sql-server-2008-partition-level-lock-escalation-details-and-examples/

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.