I'm trying to diagnose a query that is intermittently taking a long time. I suspect it may be blocked trying to acquire a lock. I don't have permissions to use a profiler in the environment experiencing the issue.
Is there any way for me to obtain statistics on how long this individual query is blocked, without using an external profiler?
Best Answer
(If you have access to DMVs then look into sp_whoisactive with
@find_block_leaders = 1
. Just tell you DBA (if you are not) to deploy it and grant you execute permission.)SQL server dynamic management views are your best friend :
Below are several ways to find out blocking :
A more deeper look into blocking :
Using Extended Event or BLOCKED_PROCESS_REPORT, you can find and get alerted when blocking occurs.