Partager via


Déterminer quelles requêtes détiennent des verrous

S’applique à : SQL Server Azure SQL Database

Les administrateurs de base de données ont souvent besoin d’identifier la source des verrous qui entravent les performances d’une base de données.

Par exemple, vous soupçonnez qu’un problème de performances sur votre serveur est provoqué par des verrous. Lorsque vous interrogez sys.dm_exec_requests, vous trouvez plusieurs sessions en mode suspendu avec un type d'attente qui indique que la ressource attendue est un verrou.

Vous interrogez sys.dm_tran_locks et les résultats indiquent que de nombreux verrous sont en attente, mais que les sessions auxquelles ces verrous ont été accordés ne font pas l’objet de requêtes actives dans sys.dm_exec_requests.

Cet exemple illustre une méthode permettant de déterminer quelle requête a pris le verrou, le plan de la requête et la pile Transact-SQL au moment où le verrou a été pris. Cet exemple illustre également comment la cible d'appariement est utilisée dans une session d'événements étendus.

L’utilisation de l'éditeur de requêtes dans SQL Server Management Studio est nécessaire pour effectuer la procédure suivante.

Remarque

Cet exemple utilise la base de données AdventureWorks.

Pour déterminer quelles requêtes détiennent des verrous

  1. Dans l'éditeur de requêtes, émettez les instructions suivantes.

    -- 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. Après exécution d'une charge de travail sur le serveur, émettez les instructions suivantes dans l’éditeur de requêtes pour déterminer quelles requêtes détiennent encore des verrous.

    --  
    -- 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. Après avoir identifié les problèmes, supprimez les éventuelles tables temporaires et la session d'événement.

    DROP TABLE #unmatched_locks  
    DROP EVENT SESSION FindBlockers ON SERVER  
    

Remarque

Les exemples de code Transact-SQL précédents s’exécutent sur SQL Server local, mais peuvent ne pas s’exécuter complètement sur la base de données Azure SQL. Les parties principales de l’exemple impliquant directement des événements, tels que ADD EVENT sqlserver.lock_acquired, fonctionnent également sur Azure SQL Database. Toutefois, les éléments préliminaires, tels que sys.server_event_sessions, doivent être modifiés en leurs équivalents Azure SQL Database comme sys.database_event_sessions pour que l’exemple s’exécute. Pour plus d’informations sur ces différences mineures entre SQL Server localement et Azure SQL Database, consultez les articles suivants :

Voir aussi

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)