DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML 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);
       *****************************************************