Compartir a través de


Determinar las consultas que retienen bloqueos

Se aplica a: SQL Server Azure SQL Database

A menudo, los administradores de bases de datos necesitan identificar el origen de bloqueos que reducen el rendimiento de la base de datos.

Por ejemplo: está sospechando que el bajo rendimiento del servidor puede ser debido a los bloqueos. Al consultar sys.dm_exec_requests, encuentra varias sesiones en un modo suspendido y con un tipo de espera que indica que un bloqueo es el recurso por el que se está esperando.

Al realizar la consulta sys.dm_tran_locks los resultados muestran que existen muchos bloqueos pendientes, pero las sesiones a las que se permitió usar los bloqueos no tienen ninguna solicitud activa que aparezca en sys.dm_exec_requests.

Este ejemplo demuestra un método para determinar la consulta que obtiene el bloqueo, el plan de la consulta y la pila de Transact-SQL en el momento de obtención del bloqueo. Este ejemplo también muestra cómo el destino del emparejamiento se utiliza en una sesión de Extended Events.

Para realizar esta tarea debe usar el Editor de consultas de SQL Server Management Studio y llevar a cabo el siguiente procedimiento.

Nota:

En este ejemplo se utiliza la base de datos de AdventureWorks.

Cómo determinar las consultas que mantienen bloqueos

  1. En el Editor de consultas, emita las instrucciones siguientes.

    -- 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. Después de la ejecución de una carga de trabajo en el servidor, emita las instrucciones siguientes en el Editor de consultas para buscar consultas que aún mantengan bloqueos.

    --  
    -- 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. Después de identificar los problemas, quite cualquier tabla temporal y la sesión de evento.

    DROP TABLE #unmatched_locks  
    DROP EVENT SESSION FindBlockers ON SERVER  
    

Nota:

Los ejemplos de código de Transact-SQL anteriores se ejecutan en SQL Server local, pero es posible que no se ejecuten bien en Azure SQL Database. Las partes principales del ejemplo implican directamente eventos, como ADD EVENT sqlserver.lock_acquired funcionando también en Azure SQL Database. Sin embargo, los elementos preliminares, como sys.server_event_sessions, se deben editar en sus homólogos de Azure SQL Database, como sys.database_event_sessions, para que se ejecute el ejemplo. Para obtener más información acerca de estas pequeñas diferencias entre SQL Server local y Azure SQL Database, consulte los siguientes artículos:

Consulte también

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)