Thursday, October 25, 2018

How to query for locking and blocking details?

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.

       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]
       row_lock_wait_count + page_lock_wait_count > 0
       AND object_name(object_id) IS NOT NULL
       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