tran_locks.sql

tran_locks.sql

SELECT
	dm_tran_locks.request_session_id AS IDСессии,
	CASE
		WHEN resource_type = 'object'
			THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
		ELSE OBJECT_NAME(partitions.object_id)
	END AS ИмяОбъекта,
	indexes.name AS ИмяИндекса,
	CASE dm_tran_locks.resource_type
		WHEN 'KEY'
			THEN 'Запись'
		WHEN 'PAGE'
			THEN 'Страница'
		WHEN 'OBJECT'
			THEN 'Таблица'
	END AS Гранулярность,
	RTRIM(dm_tran_locks.resource_description) AS Описание,

	CASE dm_tran_locks.request_mode
		WHEN 'S'
			THEN 'S (SELECT)'
		WHEN 'X'
			THEN 'X (INSERT)'
		WHEN 'U (UPDATE)'
			THEN 'Таблица'
		ELSE dm_tran_locks.request_mode
	END AS РежимЗапроса,
	CASE dm_tran_locks.request_status
		WHEN 'GRANT'
			THEN 'Установлена'
		ELSE 'Ожидает'
	END AS СтатусБлокировки,
	CASE ISNULL(indexes.index_id, 0)
		WHEN 1
			THEN 1
		ELSE 0
	END AS ИндексКластерный
FROM
	sys.dm_tran_locks AS dm_tran_locks
	LEFT JOIN sys.partitions AS partitions
	ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
	LEFT JOIN sys.indexes AS indexes
	ON indexes.object_id = partitions.object_id
		AND indexes.index_id = partitions.index_id
WHERE dm_tran_locks.resource_subtype = ''
	AND resource_associated_entity_id > 0
	AND (request_mode IN ('S', 'U', 
			'X', 'RangeS-S', 'RangeS-U', 'RangeI-N', 'RangeI-S', 
			'RangeI-U', 'RangeI-X', 'RangeX-S', 'RangeX-U', 'RangeX-X')
		OR request_status = 'WAIT')
	AND resource_type <> 'RID'
	AND resource_database_id = DB_ID()
	AND CASE
		WHEN resource_type = 'object'
			THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
		ELSE OBJECT_NAME(partitions.object_id)
	END NOT LIKE 'sys%'
	AND CASE
		WHEN resource_type = 'object'
			THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
		ELSE OBJECT_NAME(partitions.object_id)
	END <> 'Config'
	AND CASE
		WHEN resource_type = 'object'
			THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
		ELSE OBJECT_NAME(partitions.object_id)
	END <> 'Params'