SQL SERVER: Get lock escalations

CheckLockEscalations.sql

-- Check for lock escalation issues
SELECT 
    'lock escalation' AS [result_name],
    OBJECT_NAME(p.object_id, db_id()) AS TableName,
    p.index_id,
    i.name AS IndexName,
    SUM(CASE WHEN l.resource_type = 'EXTENT' THEN 1 ELSE 0 END) AS ExtentLocks,
    SUM(CASE WHEN l.resource_type = 'PAGE' THEN 1 ELSE 0 END) AS PageLocks,
    SUM(CASE WHEN l.resource_type = 'KEY' THEN 1 ELSE 0 END) AS KeyLocks,
    SUM(CASE WHEN l.resource_type = 'OBJECT' THEN 1 ELSE 0 END) AS TableLocks,
    SUM(CASE WHEN l.resource_type = 'RID' THEN 1 ELSE 0 END) AS RowLocks
FROM sys.dm_tran_locks l
INNER JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
LEFT JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE l.request_session_id <> @@SPID
    --AND p.object_id = OBJECT_ID('schema.table')
GROUP BY p.object_id, p.index_id, i.name