asked on

How to know which tables getting lock escalation

Hi,

How to know which tables getting lock escalation? Can below query can tell? so that I have to change lock escalation only for specific tables only.

SELECT OBJECT_NAME(ddios.[object_id], ddios.database_id) AS [object_name] ,
i.name AS index_name ,
ddios.index_id ,
ddios.partition_number ,
ddios.index_lock_promotion_attempt_count ,
ddios.index_lock_promotion_count ,
( ddios.index_lock_promotion_attempt_count
/ ddios.index_lock_promotion_count ) AS percent_success
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
INNER JOIN sys.indexes i ON ddios.OBJECT_ID = i.OBJECT_ID
AND ddios.index_id = i.index_id
WHERE ddios.index_lock_promotion_count > 0

Vitor Montalvão

Why are you so worried with the lock escalation?
Do you have a particular reason to not let the SQL Server engine take care of the lock escalations?

ASKER CERTIFIED SOLUTION

Máté Farkas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Baber Amin

ASKER

Actually reason to do that is we have some operations which creates locks on the tables which stops the applications functionality. We can afford if applications slows down little bit when large operations are going on (which are rare, few times a month only) but keep functioning.

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial