-- You can use sys.dm_db_index_operational_stats to
-- track how many attempts were made to escalate
-- to table locks (index_lock_promotion_attempt_count),
-- as well as how many times escalations actually
-- succeeded (index_lock_promotion_count).
-- The following query shows the top 5 objects with the
-- highest number of escalations:
SELECT TOP 5
OBJECT_NAME(object_id, database_id) object_nm,
index_id,
partition_number,
index_lock_promotion_attempt_count,
index_lock_promotion_count
FROM
sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL)
ORDER BY
index_lock_promotion_count DESC