Skip to content

Instantly share code, notes, and snippets.

@ronascentes
Created October 31, 2017 17:20
Show Gist options
  • Save ronascentes/932d6b859f935d5f4af1cba9dce41ece to your computer and use it in GitHub Desktop.
Save ronascentes/932d6b859f935d5f4af1cba9dce41ece to your computer and use it in GitHub Desktop.
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).
-- get from https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/06/11/three-usage-scenarios-for-sys-dm_db_index_operational_stats
SELECT TOP 3
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment