I've got a new customer who is having a lot of blocking problems on his database, but he didn't have any real details about where the blocking was occurring. This query is something that I use to return useful aggregate details on where the blocking has occurred since the last service restart.
USE YOURDBNAME;
SELECT
db_name(database_id) [Database],
object_name(object_id) [Table],
row_lock_count + page_lock_count [#Locks],
row_lock_wait_count + page_lock_wait_count
[#Blocks],
row_lock_wait_in_ms + page_lock_wait_in_ms
[BlockWaitTime],
index_id [IndexID]
FROM
sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL)
WHERE
row_lock_wait_count + page_lock_wait_count > 0
AND
object_name(object_id) IS NOT NULL
ORDER BY
BlockWaitTime DESC
Your output will be something like this:
You can see it gives you a quick visual on where the blocking is happening. Pretty helpful, but don't take it verbatim. The lock and block counts are accurate, but there are some known inaccuracies with the block wait time (see below link). In my opinion, the query is still accurate enough to give you an idea of where your blocking is occurring, and let you know where to look further. Hope you find it useful!
No comments:
Post a Comment