Different techniques to identify blocking in SQL Server

By:   |   Updated: 2012-07-27   |   Comments (14)   |   Related: 1 | 2 | 3 | 4 | > Monitoring


Problem

SQL Server is able to service requests from a large number of concurrent users. When SQL Server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes request access to the same resources at the same time. A conflict in which one process is waiting for another to release a resource is called a block. Although in SQL Server a blocked process usually resolves itself when the first process releases the resource but there are times when a process holds a transaction lock and doesn't release it. In this tip, we will learn different techniques to troubleshoot and resolve blocks in SQL Server.

Solution

In order to resolve a blocked process, we first need to determine which process is the blocking process and then if possible kill the blocking process. There are many different ways in SQL Server to identify a blocks and blocking process that are listed as follow:

  • Activity Monitor
  • SQLServer:Locks Performance Object
  • DMVs
    • sys.dm_exec_requests
    • sys.dm_tran_locks
    • sys.dm_os_waiting_tasks
  • SQL Server Profiler Locks Event Category

Each of these tools reports different information which is helpful in resolving blocks quickly. Let's have a look at these tools in details:

1) Activity Monitor

Activity Monitor is a tool in SQL Server Management Studio that gives you a view of current connections on SQL Server. You can use Activity Monitor to view information about the current processes and locks held on SQL Server resources. To open Activity Monitor in SQL Server Management Studio, right-click the SQL Server instance name in Object Explorer and then select Activity Monitor:

Activity Monitor

Launch Activity Monitor

To find blocked process with Activity Monitor, first click on Processes in Activity Monitor to open the Process Info page:

ProcessInfo Page

Process Info Page and Locating Blocking Process

Then locate the process that is waiting, and then scroll over to the Blocked By column and note the Process ID in that column. Find that Process ID in Process Info page. and

Locate Blocked Process

If you want to terminate the blocking process, right-click it and choose Kill Process:

Kill Blocked Process

2) The SQLServer:Locks performance object

You use SQLServer:Locks object counter in Performance Monitor to view current statistics or create a log or alert to monitor locks. For example, you can monitor Average Wait Time, Number of deadlocks/sec and Lock Timeouts/sec statistics to determine whether there is a problem with resource contention on SQL Server. However, you will need additional information to determine the exact cause of the problem. Follow the steps below to monitor the SQLServer: Locks performance counter:

On the Start menu, point to Run, type perfmon in the Run dialog box, and then click OK to launch Performance Monitor.

Launch Performance Monitor

Launching Performance Monitor

  • Right-click anywhere on the screen and then choose Add Counters.

     
    Launch Performance Monitor

    Add counters

  • Scroll down to locate SQL Server lock counters and add these three counters and then click OK.

    • Average Wait Time
    • Number of deadlocks/sec
    • Lock Timeouts/sec
    Launch Performance Monitor

    3) DMVs (Dynamic Management Views)

    sys.dm_exec_requests

    You can use the sys.dm_exec_requests dynamic management view to obtain detailed information about the requests currently executing on SQL Server. The dynamic management view includes detailed information about the query and query plan, status of request and information about the amount of time it has been executing. The columns you are most likely to use when troubleshooting a block or deadlock are as follow:

    1. blocking_session_id - The SPID of the blocking session.
    2. wait_type - Type of wait.
    3. wait_time - Length of time request has been waiting (in milliseconds).
    4. last_wait_type - If a wait has ended, its type is listed here.
    5. wait_resource - Name of resource the request is waiting for.
    6. transaction_isolation_level - Isolation level for the transaction.
    7. lock_timeout - Length of time a lock can exist before timing out

    To view blocked process execute the following query:

    USE [master]
    GO
    SELECT  session_id
     ,blocking_session_id
     ,wait_time
     ,wait_type
     ,last_wait_type
     ,wait_resource
     ,transaction_isolation_level
     ,lock_timeout
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
    GO

    sys.dm_tran_locks

    You can view information about current locks and the processes blocking them using the sys.dm_tran_locks dynamic management view. This column has one of three values: GRANT, WAIT or CONVERT. The value of CONVERT means that the requestor has been granted a request but is waiting to upgrade to the initial request to be granted. To locate information about all locks with a request status of CONVERT, you execute the following:

    USE [master]
    GO
    SELECT * from sys.dm_tran_locks
    WHERE request_status = 'CONVERT'
    GO

    The request_session_id column contains the Process ID for the process. To view locking in the particular database, execute the following query that joins sys.dm_tran_locks with sys.partitions:

    USE [master]
    GO
    SELECT   tl.resource_type
     ,tl.resource_associated_entity_id
     ,OBJECT_NAME(p.object_id) AS object_name
     ,tl.request_status
     ,tl.request_mode
     ,tl.request_session_id
     ,tl.resource_description
    FROM sys.dm_tran_locks tl
    LEFT JOIN sys.partitions p 
    ON p.hobt_id = tl.resource_associated_entity_id
    WHERE tl.resource_database_id = DB_ID()
    GO

    sys.dm_os_waiting_tasks

    The sys.dm_os_waiting_tasks dynamic management view reports information about the blocked and blocking processes. The blocked process is listed in the session_id column. The blocking is listed in the blocking_session_id column.

    Execute the following to view wait stats for all block processes on SQL Server:

    USE [master]
    GO
    SELECT   w.session_id
     ,w.wait_duration_ms
     ,w.wait_type
     ,w.blocking_session_id
     ,w.resource_description
     ,s.program_name
     ,t.text
     ,t.dbid
     ,s.cpu_time
     ,s.memory_usage
    FROM sys.dm_os_waiting_tasks w
    INNER JOIN sys.dm_exec_sessions s
    ON w.session_id = s.session_id
    INNER JOIN sys.dm_exec_requests r
    ON s.session_id = r.session_id
    OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
    WHERE s.is_user_process = 1
    GO

    This detail is good for a big picture, or to get a quick idea of the types of waits occurring, but most of the real diagnostics and tuning will occur at a statement level.

    4) SQL Server Profiler

    You use the SQL Server Profiler Locks event category to create a trace of events related to locks and deadlocks. You can choose one or more of these event classes:

    1. Deadlock_Graph_Event_Class — Creates an XML description of deadlocks.
    2. Lock:Acquired — Use in conjunction with Lock:Released to determine the types of locks being requested and the length of time they are retained.
    3. Lock:Cancel — Use to determine which locks are cancelled.
    4. Lock:Deadlock Chain — Use to determine the objects involved in a deadlock.
    5. Lock:Deadlock — Use to determine the objects and applications involved in a deadlock.
    6. Lock:Escalation — Reports information about locks that have been escalated to cover a larger resource. For example, when a row lock becomes a table lock.
    7. Lock:Released — Use in conjunction with Lock:Acquired.
    8. Lock:Timeout(timeout>0) — Provides information about locks that have timed out due to blocking issues.
    9. Lock:Timeout — Provides the same information as Lock:Timeout (timeout>0), but includes timeouts where the duration was 0.

    5) sp_who/sp_who2

    Both sp_who/sp_who2 return information about all the sessions that are currently established in the database and these are denoted as spid's. Both these store procedures accepts parameters. The blk column of sp_who and blkby column of sp_who2 contains the spid for blocking process. Running sp_who and sp_who2 is easy, for example following call of these procedures returns all process that are currently active on SQL Server:

    USE master;
    GO
    EXEC sp_who 'active';
    GO
    EXEC sp_who2 'active';
    GO

    6) Use KILL statement to terminate blocked process

    You use the KILL statement to view the status of a process or kill the process. The KILL statement has the syntax: KILL spid | UOW [WITH STATUSONLY]

    USE master;
    GO
    KILL spid | UOW [WITH STATUSONLY]
    GO

    You must pass either a spid or, if the process belongs to a Distributed Transaction Coordination (DTC) transaction, you must provide a Unit of Work (UOW). You must be a member of sysadmin or processadmin to kill a process. You can obtain the spid for the current session by running @@spid. You can obtain the spid for the sessions associated with a login by running sp_who2. If you don't specify a login, sp_who2 returns information for all current connections. If you can't kill a blocking process, you might have to restart the SQL Server service. Doing so will cause all current connections to close, so you should avoid restarting the service if possible.

    When to use each tool?

    The dynamic management views and Activity Monitor are good tools for learning the current state of a process and for resolving a current blocked process. System Monitor counters are ideal for identifying trends that indicate a stored procedure, application, or database configuration setting is causes a large number of blocked processes or timeouts due to blocking. Running a SQL Server Profiler trace is a good way to analyse detailed information and identify the cause of a large number of blocks.

    Next Steps


  • sql server categories

    sql server webinars

    subscribe to mssqltips

    sql server tutorials

    sql server white papers

    next tip



    About the author
    MSSQLTips author Basit Farooq Basit Farooq is a Senior Database Administrator and has worked in the IT industry for 11+ years.

    This author pledges the content of this article is based on professional experience and not AI generated.

    View all my tips


    Article Last Updated: 2012-07-27

    Comments For This Article




    Wednesday, May 6, 2015 - 3:00:03 PM - Luiz Back To Top (37110)

    Information is really helpful, THANKS


    Tuesday, January 27, 2015 - 6:08:26 AM - krishnaraj Back To Top (36065)

    Information is really helpful

     


    Friday, January 31, 2014 - 1:05:59 PM - Brian Back To Top (29299)

    I have a SQL Agent job that runs hourly looking for blocks. If it finds a block it will email me the block, the blocking user, their sql and the sql they are blocking

    Here is my script if anyone wants it:

    /* use the query below to create blocking to test the email job.

    begin transaction
    select *
    from [some table big ish table] (TABLOCKX)
    --commit the job once the email job has been run.
    commit transaction
    */

    DECLARE @tableHTML  NVARCHAR(MAX) ;

    SET @tableHTML =

       N'<head>' +
        N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>' +

    /*Build blocking table*/
        N'<h2><font color="#0000ff" size="4">Blocks</font></h2>' +
       N'<table border="1">' +
        N'<tr>
     <th>SessionID</th>
     <th>Program_name</th>
     <th>User</th>
     <th>DatabaseNM</th>
     <th>BlockingStartDTS</th>
     <th>WaitDurationMS</th>
     <th>BlockedSQL</th>
     <th>BlockingSQL</th>
        </tr>' +
        CAST ( (SELECT
    wt.blocking_session_id as TD, ''
    ,SP.program_name as TD, ''
    ,COALESCE(SP.LOGINAME, SP.nt_username) as TD,''
    ,db.name AS TD,''
    ,EC1.connect_time AS TD,''
    ,WT.WAIT_DURATION_MS AS TD,''
    ,h1.TEXT  AS TD,''   --AS RequestingText
    ,h2.TEXT  AS TD --AS BlockingText
    FROM sys.dm_tran_locks AS tl
    INNER JOIN sys.databases db
     ON db.database_id = tl.resource_database_id
    INNER JOIN sys.dm_os_waiting_tasks AS wt
     ON tl.lock_owner_address = wt.resource_address
    INNER JOIN sys.dm_exec_connections ec1
     ON ec1.session_id = tl.request_session_id
    INNER JOIN sys.dm_exec_connections ec2
     ON ec2.session_id = wt.blocking_session_id
    LEFT OUTER JOIN master.dbo.sysprocesses SP
     ON SP.spid = wt.blocking_session_id
    CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
    CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

       FOR XML PATH('tr'), TYPE
        ) AS NVARCHAR(MAX) ) +
        N'</table>'

     

     --JUST FOR TESTING
    --select  @TableHTML
    -- If there are records, then send email, else don't

     IF @tableHTML  is not null
     
    Begin

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = [put your profile here in single quotes]  ,
        @recipients = [put your email list here, semi column separated with the block of emails enclosed in single quotes] ,
     @subject = 'Blocking Alert',
     @body = @tableHTML,
        @body_format = 'HTML' ;

    END


    Thursday, December 19, 2013 - 1:28:37 PM - Marios Philippopoulos Back To Top (27853)

    Also, sp_whoisactive is a much better way of diagnosing current activity than sp_who2:

    http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx


    Thursday, December 19, 2013 - 1:23:50 PM - Marios Philippopoulos Back To Top (27852)

    Hi, thank you for the very useful info in this article.

    One technique you did not mention is extended events and specifically the sqlserver.blocked_process_report event, mentioned here:

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/21/an-xevent-a-day-21-of-31-the-future-tracking-blocking-in-denali.aspx

    SQL Profiler and sysprocesses are OK but will be removed from future versions of SQL Server, so people had better get used to using DMVs and extended events instead.


    Thursday, August 29, 2013 - 8:12:18 AM - wendyyang Back To Top (26523)

    good post. thank you


    Wednesday, July 17, 2013 - 12:49:56 AM - SQLDBA Back To Top (25868)

    hi,

    select

    *fromsys.dm_exec_requests

     shows

    KSOURCE_WAKEUP is showing 1483472695.

    is there any issue ?


    Thursday, December 27, 2012 - 3:54:34 AM - Adil Back To Top (21147)

    what will happen if we execute the queries which are blocked?

    like if we have copied the commands of the blocked spid's to the query window.

    1st truncate query 2nd insert query and 3rd select query n accidently we press F5.

    please help.


    Tuesday, August 14, 2012 - 1:50:08 AM - Iqbal Husain Back To Top (19028)

    Dear Farooq Salam wa Rahmatullah

     

    Thanks for such a nice and help full article. I came here in search of "How to Counten Number of connection".

    I run your commnd : 

    USE master;

    GO
    EXEC sp_who 'active';
    GO
    EXEC sp_who2 'active';
    GO
    In the result I can found only one record having "runnable" state of both commands.
    I connected user sa on my server and run the command on server, there was only one record of runnable status with PID 51.
     
    I connected use sa by my client to the same Server. I run statement on Client and find only one record with runnable status but the PID was 52.
    In the same time I run the statement on the server (after connecting the client) but the result was same.
     
    What I need is I want to know how many clients have been really connected to the SQL server. I was thinking if I can count the number of records with runnable status, my problem will be solved. But you can see the case as I stated.
    FYI: I am usning MS SQL Server 2005 Express Edition.
    Thanks in advance.
    Allah Hafiz
     
     
     
     

    Thursday, August 2, 2012 - 8:15:04 PM - Abhinav Back To Top (18894)

     

    Nice Informstion !!!

    Determine Blocking sessions:

    1)Activity Monitor 2) sp_lock 3)sys.sysprocess4)sys.dm_os_waiting_tasks5) sys.dm_exec_requests


    Wednesday, August 1, 2012 - 2:17:07 AM - sampath Reddy Back To Top (18865)

    Hi

    Good posting.

     

    Thanks,

    Sampath

     


    Friday, July 27, 2012 - 4:45:00 PM - Sqlserver Back To Top (18833)

    It is good information. Thanks


    Friday, July 27, 2012 - 4:32:35 AM - Basit Farooq Back To Top (18822)

    Hi Venkateswara

    Thanks for liking the tip.

    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com


    Friday, July 27, 2012 - 2:59:38 AM - venkateswara reddy Back To Top (18821)

    very good information for blocking

    we can also find bloking in SQL Server  with sys.sysprocesses

    SELECT * FROM sys.sysprocesses WHERE blocked <> 0















    get free sql tips
    agree to terms