-- создадим событие 
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LockCounts')  
DROP EVENT session LockCounts ON SERVER  
GO  
DECLARE @dbid int  
 
SELECT @dbid = db_id('DB_Light')  
 
DECLARE @sql nvarchar(1024)  
SET @sql = '  
CREATE event session LockCounts ON SERVER  
ADD EVENT sqlserver.lock_acquired (WHERE database_id =' + CAST(@dbid AS nvarchar) +')  
ADD TARGET package0.histogram(   
SET filtering_event_name=''sqlserver.lock_acquired'', source_type=0, source=''resource_0'')'  
 
EXEC (@sql)  
GO  
ALTER EVENT session LockCounts ON SERVER   
STATE=start  
GO  
 
 
 
--   
-- Посмотрим таблицы с блокировками 
 
use [DB_Light]
 
declare @tt table(lobject_id bigint, lock_count bigint)
;WITH lock (lobject_id, lock_count)  
AS (
 SELECT objstats.value('.','bigint') AS lobject_id,   
objstats.value('@count', 'bigint') AS lock_count   FROM (  
SELECT CAST(xest.target_data AS XML)  
LockData  
FROM sys.dm_xe_session_targets xest  
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address  
JOIN sys.server_event_sessions ses ON xes.name = ses.name  
WHERE xest.target_name = 'histogram' AND xes.name = 'LockCounts'  
) Locks  
CROSS APPLY LockData.nodes('//HistogramTarget/Slot') AS T(objstats)  
)
 
insert into @tt (lobject_id , lock_count)
select lobject_id , lock_count from lock;
 
select * from @tt LockedObjects
INNER JOIN sys.objects o  
ON LockedObjects.lobject_id = o.object_id  
WHERE o.type != 'S' AND o.type = 'U'  
ORDER BY lock_count desc 
 
 
--   
-- Stop the event session.  
--   
ALTER EVENT SESSION LockCounts ON SERVER  
state=stop  
GO