[스크립트] 실시간/통계 latch&lock 확인

latch/lock 통계

SELECT  
'Sum_Wait_Stat.' as [What],sum(page_lock_wait_in_ms) as page_lock_wait_in_ms,sum(row_lock_wait_in_ms) as row_lock_wait_in_ms,
sum(page_latch_wait_in_ms) as page_latch_wait_in_ms,sum(page_io_latch_wait_in_ms) as page_io_latch_wait_in_ms
FROM [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) iops
INNER JOIN [sys].[indexes] i
    ON i.[object_id] = iops.[object_id]  AND i.[index_id] = iops.[index_id]

 object 별 latch/lock 통계

SELECT 'Wait_Stat.' as [What],DB_NAME([database_id]) AS [Database] ,OBJECT_NAME(iops.[object_id], [database_id]) AS [ObjectName] 
    ,CASE  WHEN i.[is_unique] = 1  THEN 'UNIQUE '  ELSE ''   END + i.[type_desc] AS [IndexType],i.[name] AS [IndexName] , 
page_io_latch_wait_in_ms ,page_latch_wait_in_ms, page_lock_wait_in_ms, row_lock_wait_in_ms, 
iops.[page_io_latch_wait_count], page_lock_wait_count, iops.[page_latch_wait_count],page_lock_count,row_lock_count,row_lock_wait_count, 
leaf_allocation_count,leaf_insert_count,leaf_delete_count,leaf_update_count,
nonleaf_insert_count,nonleaf_delete_count,nonleaf_update_count,
nonleaf_allocation_count --,*
FROM [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) iops
INNER JOIN [sys].[indexes] i
    ON i.[object_id] = iops.[object_id]  AND i.[index_id] = iops.[index_id]
where i.[type_desc] !='HEAP' and OBJECT_NAME(iops.[object_id], [database_id]) not like 'sys%'
-- and OBJECT_NAME(iops.[object_id], [database_id])= ' '
ORDER BY iops.page_latch_wait_in_ms + iops.page_io_latch_wait_in_ms DESC

 

실시간 latch

 SELECT 'RealTime_Latch ' as [What],wt.session_id, wt.wait_type, wt.wait_duration_ms           
		, s.name AS schema_name  , o.name AS object_name   , i.name AS index_name    ,*        
FROM sys.dm_os_buffer_descriptors bd 
JOIN (           
  SELECT *
    --resource_description          
  , CHARINDEX(':', resource_description) AS file_index            
  , CHARINDEX(':', resource_description, CHARINDEX(':', resource_description)+1) AS page_index  
  , resource_description AS rd          
  FROM sys.dm_os_waiting_tasks wt           
  WHERE wait_type LIKE 'PAGELATCH%'  or wait_type LIKE 'PAGEIOLATCH%'                     
  ) AS wt           
    ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)           
    AND bd.file_id = SUBSTRING(wt.rd, wt.file_index+1, 1) --wt.page_index)           
    AND bd.page_id = SUBSTRING(wt.rd, wt.page_index+1, LEN(wt.rd))
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON  p.index_id = i.index_id AND p.object_id = i.object_id
JOIN sys.objects o ON i.object_id = o.object_id 
JOIN sys.schemas s ON o.schema_id = s.schema_id
order by wt.wait_duration_ms desc;