查看SQLServer Lock Waits 状态

dba 发布于 2023-12-26 阅读(43)
SELECT o.[name] AS [table_name]
    ,i.[name] AS [index_name]
    ,ios.index_id
    ,ios.partition_number
    ,SUM(ios.row_lock_wait_count) AS [total_row_lock_waits]
    ,SUM(ios.row_lock_wait_in_ms) AS [total_row_lock_wait_in_ms]
    ,SUM(ios.page_lock_wait_count) AS [total_page_lock_waits]
    ,SUM(ios.page_lock_wait_in_ms) AS [total_page_lock_wait_in_ms]
    ,SUM(ios.page_lock_wait_in_ms) + SUM(row_lock_wait_in_ms) AS [total_lock_wait_in_ms]
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
INNER JOIN sys.objects AS o WITH (NOLOCK) ON ios.[object_id] = o.[object_id]
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ios.[object_id] = i.[object_id]
    AND ios.index_id = i.index_id
WHERE o.[object_id] > 100
GROUP BY o.[name]
    ,i.[name]
    ,ios.index_id
    ,ios.partition_number
HAVING SUM(ios.page_lock_wait_in_ms) + SUM(row_lock_wait_in_ms) > 0
ORDER BY total_lock_wait_in_ms DESC
OPTION (RECOMPILE);