Compartilhar via


Como determinar quais consultas estão mantendo bloqueios

Muitas vezes, os administradores de banco de dados precisam identificar a origem de bloqueios que estão obstruindo o desempenho do banco de dados.

Por exemplo, digamos que você suspeite que um problema de desempenho em seu servidor pode estar sendo causado por bloqueios. Ao consultar sys.dm_exec_requests, você descobre várias sessões em modo suspenso, com um tipo de espera indicativo de que o recurso que se está aguardando é um bloqueio.

Você consulta sys.dm_tran_locks e os resultados mostram que há vários bloqueios pendentes, mas as sessões às quais eles foram concedidos não têm nenhuma solicitação ativa exibida em sys.dm_exec_requests.

Este exemplo demonstra um método de determinar qual consulta efetuou o bloqueio, o plano da consulta e a pilha Transact-SQL no momento em que o bloqueio foi efetuado. Este exemplo também ilustra como o destino de emparelhamento é usado em uma sessão de Eventos Estendidos.

A realização dessa tarefa envolve o uso do Editor de Consultas no SQL Server Management Studio para aplicar o procedimento a seguir.

ObservaçãoObservação

Este exemplo usa o banco de dados AdventureWorks.

Para determinar quais consultas estão mantendo bloqueios

  1. No Editor de Consultas, emita as seguintes instruções:

    -- Perform cleanup. 
    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='FindBlockers')
        DROP EVENT SESSION FindBlockers ON SERVER
    GO
    -- Use dynamic SQL to create the event session and allow creating a -- predicate on the AdventureWorks database id.
    --
    DECLARE @dbid int
    
    SELECT @dbid = db_id('AdventureWorks')
    
    IF @dbid IS NULL
    BEGIN
        RAISERROR('AdventureWorks is not installed. Install AdventureWorks before proceeding', 17, 1)
        RETURN
    END
    
    DECLARE @sql nvarchar(1024)
    SET @sql = '
    CREATE EVENT SESSION FindBlockers ON SERVER
    ADD EVENT sqlserver.lock_acquired 
        (action 
            ( sqlserver.sql_text, sqlserver.database_id, sqlserver.tsql_stack,
             sqlserver.plan_handle, sqlserver.session_id)
        WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND resource_0!=0) 
        ),
    ADD EVENT sqlserver.lock_released 
        (WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND resource_0!=0 ))
    ADD TARGET package0.pair_matching 
        ( SET begin_event=''sqlserver.lock_acquired'', 
                begin_matching_columns=''database_id, resource_0, resource_1, resource_2, transaction_id, mode'', 
                end_event=''sqlserver.lock_released'', 
                end_matching_columns=''database_id, resource_0, resource_1, resource_2, transaction_id, mode'',
        respond_to_memory_pressure=1)
    WITH (max_dispatch_latency = 1 seconds)'
    
    EXEC (@sql)
    -- 
    -- Create the metadata for the event session
    -- Start the event session
    --
    ALTER EVENT SESSION FindBlockers ON SERVER
    STATE = START
    
  2. Após a execução de uma carga de trabalho no servidor, emita as instruções a seguir no Editor de Consultas para descobrir se ainda há consultas mantendo bloqueios.

    --
    -- The pair matching targets report current unpaired events using 
    -- the sys.dm_xe_session_targets dynamic management view (DMV)
    -- in XML format.
    -- The following query retrieves the data from the DMV and stores
    -- key data in a temporary table to speed subsequent access and
    -- retrieval.
    --
    SELECT 
    objlocks.value('(action/value)[5]', 'int')
            AS session_id,
        objlocks.value('(data/value)[5]', 'int') 
            AS database_id,
        objlocks.value('(data/text)[1]', 'nvarchar(50)' ) 
            AS resource_type,
        objlocks.value('(data/value)[9]', 'bigint') 
            AS resource_0,
        objlocks.value('(data/value)[10]', 'bigint') 
            AS resource_1,
        objlocks.value('(data/value)[11]', 'bigint') 
            AS resource_2,
        objlocks.value('(data/text)[2]', 'nvarchar(50)') 
            AS mode,
        objlocks.value('(action/value)[1]', 'varchar(MAX)') 
            AS sql_text,
        CAST(objlocks.value('(action/value)[4]', 'varchar(MAX)') AS xml) 
            AS plan_handle,    
        CAST(objlocks.value('(action/value)[3]', 'varchar(MAX)') AS xml) 
            AS tsql_stack
    INTO #unmatched_locks
    FROM (
        SELECT CAST(xest.target_data as xml) 
            lockinfo
        FROM sys.dm_xe_session_targets xest
        JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
        WHERE xest.target_name = 'pair_matching' AND xes.name = 'FindBlockers'
    ) heldlocks
    CROSS APPLY lockinfo.nodes('//event[@name="lock_acquired"]') AS T(objlocks)
    --
    -- Join the data acquired from the pairing target with other 
    -- DMVs to return provide additional information about blockers
    --
    SELECT ul.*
        FROM #unmatched_locks ul
        INNER JOIN sys.dm_tran_locks tl ON ul.database_id = tl.resource_database_id AND ul.resource_type = tl.resource_type
        WHERE resource_0 IS NOT NULL
        AND session_id IN 
            (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0)
        AND tl.request_status='wait'
        AND REPLACE(ul.mode, 'LCK_M_', '' ) = tl.request_mode
    
    
  3. Depois de identificar os problemas, descarte todas as tabelas temporárias e a sessão de evento.

    DROP TABLE #unmatched_locks
    DROP EVENT SESSION FindBlockers ON SERVER