Determinar quais consultas estão mantendo bloqueios

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do Azure

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 para determinar qual consulta efetuou o bloqueio, o plano da consulta e a pilha do 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çã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[@name="session_id"]/value)[1]', 'int')  
        AS session_id,  
    objlocks.value('(data[@name="database_id"]/value)[1]', 'int')   
        AS database_id,  
    objlocks.value('(data[@name="resource_type"]/text)[1]', 'nvarchar(50)' )   
        AS resource_type,  
    objlocks.value('(data[@name="resource_0"]/value)[1]', 'bigint')   
        AS resource_0,  
    objlocks.value('(data[@name="resource_1"]/value)[1]', 'bigint')   
        AS resource_1,  
    objlocks.value('(data[@name="resource_2"]/value)[1]', 'bigint')   
        AS resource_2,  
    objlocks.value('(data[@name="mode"]/text)[1]', 'nvarchar(50)')   
        AS mode,  
    objlocks.value('(action[@name="sql_text"]/value)[1]', 'varchar(MAX)')   
        AS sql_text,  
    CAST(objlocks.value('(action[@name="plan_handle"]/value)[1]', 'varchar(MAX)') AS xml)   
        AS plan_handle,      
    CAST(objlocks.value('(action[@name="tsql_stack"]/value)[1]', '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

Observação

Os exemplos de código do Transact-SQL precedentes são executados no SQL Server local, mas podem não ser bem executados no Banco de Dados SQL do Azure. As partes principais do exemplo que envolvem diretamente Eventos, como ADD EVENT sqlserver.lock_acquired, também funcionam no Banco de Dados SQL do Azure. Porém, os itens preliminares, como sys.server_event_sessions, devem ser editados em seus equivalentes do Banco de Dados SQL do Azure como sys.database_event_sessions para que o exemplo seja executado. Para obter mais informações sobre essas diferenças secundárias entre o SQL Server local versus o Banco de Dados SQL do Azure, confira os seguintes artigos:

Confira também

CREATE EVENT SESSION (Transact-SQL)
ALTER EVENT SESSION (Transact-SQL)
DROP EVENT SESSION (Transact-SQL)
sys.dm_xe_session_targets (Transact-SQL)
sys.dm_xe_sessions (Transact-SQL)