/*
Script: CH06 Blocking Script2.SQL
Author: James Rowland-Jones
Title: Professional SQL Server 2008: Internals and Performance Tuning
*/
-- 如何查看阻塞的进程
USE AdventureWorks2008;
SELECT lo.request_session_id as [Session]
,DB_NAME(lo.resource_database_id) as [Database]
,lo.resource_type as [Type]
,lo.resource_subtype as SubType
,lo.resource_description as [Description]
,lo.request_mode as Mode
,lo.request_owner_type as OwnerType
,lo.request_status as [Status]
,CASE WHEN lo.resource_type = 'OBJECT'
THEN OBJECT_NAME(lo.resource_associated_entity_id)
WHEN lo.resource_associated_entity_id IS NULL
OR lo.resource_associated_entity_id = 0
THEN NULL
ELSE OBJECT_NAME(p.[object_id])
END As Associated_Entity
,wt.blocking_session_id
,wt.resource_description
FROM sys.dm_tran_locks as lo
LEFT JOIN sys.partitions as p ON lo.resource_associated_entity_id = p.partition_id
LEFT JOIN sys.dm_os_waiting_tasks as wt ON lo.lock_owner_address = wt.resource_address
WHERE lo.request_session_id > 50
AND lo.resource_database_id = DB_ID('AdvenmastertureWorks2008')
AND lo.request_session_id <> @@SPID
ORDER BY [SESSION]
,[TYPE];
MSSQL如何查看阻塞的进程
最新推荐文章于 2024-07-18 13:40:02 发布