CODEWITHSUNDEEP
sql-serversql-server-2008
Ralf Stein
Ralf Stein

Reputation: 209

SQL Server Lock Escalation all tables are with "TABLE"

I have a problem with deadlocks all the time. The application has more than 1000 tables and the amount of users are 100 in average. When I checked the lock escalation type with this query, all tables are with "TABLE"

SELECT lock_escalation_desc FROM sys.tables

Is this the default behaviour? None with ROW or PAGE? I checked another application in another server and all the tables are "TABLE" also.

Thanks.

Upvotes: 2

Views: 3235

Answers (1)

Martin Smith
Martin Smith

Reputation: 453648

Yes that is the default escalation level and is perfectly normal.

Perhaps you are confused about what escalation is? This is what happens once SQL Server holds too many locks at a lower level and consolidates them for performance and resource reasons.

You would need to check allow_row_locks and allow_page_locks in sys.indexes to see if you have some configuration issue that is preventing these lower level locks being taken.

w.r.t. the escalation level itself if you use partitioned tables then you could instead consider AUTO to avoid locking the whole thing. But the reason this isn't the default is because against common workloads this turned out to be more deadlock prone.

Locks are never escalated from row to page. it always goes from row or page to at least the whole partition level.

You should investigate why you are seeing lock escalation in the first place (if indeed you are). Under read committed isolation level the typical behaviour is for select queries to take row locks and release them as soon as the data is read.

Are you running at unnecessarily restrictive isolation levels? Are these escalations happening to data modification statements that could be broken up into smaller batches?

Upvotes: 3

Related Questions