Troubleshooting Microsoft SQL locks and resource issues

Version 1

    Verified Product Versions

    AppSense Environment Manager 10.0AppSense Management Center 10.0AppSense Environment Manager 8.6AppSense Environment Manager 8.5AppSense Environment Manager 8.4AppSense Environment Manager 8.3AppSense Environment Manager 8.2AppSense Environment Manager 8.1AppSense Environment Manager 8.0AppSense Management Center 8.6AppSense Management Center 8.5AppSense Management Center 8.4AppSense Management Center 8.3AppSense Management Center 8.2AppSense Management Center 8.1AppSense Management Center 8.0AppSense DataNow 3.6AppSense DataNow 3.5AppSense DataNow 3.0AppSense DataNow 2.0AppSense DataNow 4.0AppSense DataNow 4.1

    Introduction

    Troubleshooting a situation in which application behavior is impacted by a SQL 'deadlock' or 'wait_lock' can be extremly challenging, particularly when trying to identify which transactions are the cause of the locks and their associated T-SQL queries.

    Detail

    Below are some methods frequently used by the Solution Engineers which, when run in a SQL Server Management Studio (SSMS) query window, will display output of:-

    1. Running Processes
    2. Lock information including PID and lock type.
    3. Detailed information regarding the transaction(s) causing the blocks and the transactions waiting on the blocker.

    From analysis of the output it should be clear which PID/transactions are causing the blockage and the subsequent effect it is having in on other requests.

    USE [master]
    GO
    exec sp_who2 -- Displays all process information currently running
    exec sp_lock -- Displays lock information per PID.
      --  Displays the blocking query SQL statements and any subsequent blocked requests.
    SELECT  w.blocking_session_id AS 'Blocking Session ID',
      w.session_id AS 'Victim Session ID',
      (SELECT [text] FROM sys.sysprocesses
           CROSS APPLY sys.dm_exec_sql_text([sql_handle])
           WHERE spid = r.blocking_session_id) AS BlockingQuery,
      t.text AS 'Victim Query',
      r.wait_time/1000 AS WaitDurationSecond,
      (SELECT DATEADD(ms,-r.wait_time,GETDATE())) AS 'Transaction Started',
      w.wait_type AS WaitType,
      r.last_wait_type,
      r.percent_complete AS BlockingQueryCompletePercent,
      s.program_name,
      r.lock_timeout,
      r.wait_resource,
      r.transaction_isolation_level,
      w.resource_description,
      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 AND r.blocking_session_id > 0
    GO

     

    Below is another example that returns similar results - this generates the same information found above, along with some additional troubleshooting information. It also contains the SQL Statement being executed in the results. Additionally, this will only shows sessions that have a current executing request

    SELECT
        SPID                = er.session_id
        ,BlkBy              = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END
        ,ElapsedMS          = er.total_elapsed_time
        ,CPU                = er.cpu_time
        ,IOReads            = er.logical_reads + er.reads
        ,IOWrites           = er.writes   
        ,Executions         = ec.execution_count 
        ,CommandType        = er.command       
        ,LastWaitType       = er.last_wait_type   
        ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) 
        ,SQLStatement       =
            SUBSTRING
            (
                qt.text,
                er.statement_start_offset/2,
                (CASE WHEN er.statement_end_offset = -1
                    THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                    ELSE er.statement_end_offset
                    END - er.statement_start_offset)/2
            )       
        ,STATUS             = ses.STATUS
        ,[Login]            = ses.login_name
        ,Host               = ses.host_name
        ,DBName             = DB_Name(er.database_id)
        ,StartTime          = er.start_time
        ,Protocol           = con.net_transport
        ,transaction_isolation =
            CASE ses.transaction_isolation_level
                WHEN 0 THEN 'Unspecified'
                WHEN 1 THEN 'Read Uncommitted'
                WHEN 2 THEN 'Read Committed'
                WHEN 3 THEN 'Repeatable'
                WHEN 4 THEN 'Serializable'
                WHEN 5 THEN 'Snapshot'
            END
        ,ConnectionWrites   = con.num_writes
        ,ConnectionReads    = con.num_reads
        ,ClientAddress      = con.client_net_address
        ,Authentication     = con.auth_scheme
        ,DatetimeSnapshot   = GETDATE()
        ,plan_handle        = er.plan_handle
    FROM sys.dm_exec_requests er
    LEFT JOIN sys.dm_exec_sessions ses
    ON ses.session_id = er.session_id
    LEFT JOIN sys.dm_exec_connections con
    ON con.session_id = ses.session_id
    OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
    OUTER APPLY
    (
        SELECT execution_count = MAX(cp.usecounts)
        FROM sys.dm_exec_cached_plans cp
        WHERE cp.plan_handle = er.plan_handle
    ) ec
    OUTER APPLY
    (
        SELECT
            lead_blocker = 1
        FROM master.dbo.sysprocesses sp
        WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses)
        AND sp.blocked = 0
        AND sp.spid = er.session_id
    ) lb
    WHERE er.sql_handle IS NOT NULL
    AND er.session_id != @@SPID
    ORDER BY
        CASE WHEN lead_blocker = 1 THEN -1 * 1000 ELSE -er.blocking_session_id END,
        er.blocking_session_id DESC,
        er.logical_reads + er.reads DESC,
        er.session_id

    References

    A better SP_Who2:

    http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3

     

    Different techniques to identify blocking in SQL Server:

    https://www.mssqltips.com/sqlservertip/2732/different-techniques-to-identify-blocking-in-sql-server/