(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 120-------------------------------------------------- Lock waits for current database By: srinivasma_exceldbp Date: December 25, 2014, 4:16 am --------------------------------------------------------- -- Get lock waits for current database (Lock Waits) -- This query is helpful for troubleshooting blocking and deadlocking issues 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) > ORDER BY total_lock_wait_in_ms DESC OPTION (RECOMPILE); *****************************************************