 |
Return Create A Forum - Home |
 |
--------------------------------------------------------- |
|
ExcelSoft Database Professionals |
 |
https://esdbp.createaforum.com |
|
--------------------------------------------------------- |
|
***************************************************** |
|
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); |
|
***************************************************** |
