Quick one today:

Sometimes, we end up in situations where we need to uncover the underlying lock escalation or lock assignments due to some query executions. In these situations, we have an internal system procedure that displays this information in great detail.

Locks help attain concurrency, but they are expensive to maintain. So Sql Server continuously strives to maintain the right balance to achieve better performance with higher concurrency.

Lock Allotment

When you are running a query on several tables or on a large table, several locks are issued. Sometimes reviewing this pertinent information could help explain the nature of Sql Server behavior with certain long running queries.

In this above snapshot, the ‘ALTER TABLE‘ query is a single threaded query that needs locks on each and every key / data page to complete the action successfully. A total of 2100 locks are issued to this SPID 53. After a few seconds, Sql Server realizes that it is better to perform lock escalation to page lock or even table lock than several individual locks.

Running it without any parameters returns the lock held by current session. With SPID as parameter, we could retrieve locks held by other sessions.

Note: Going forward, Microsoft recommends that we use sys.dm_tran_locks dynamic management view. sp_lock is still supported well into Sql Server 2016, but it is better to be familiar with old and new techniques. I’ll add a separate post on the new one soon.

Hope this helps,
_Sqltimes