ロックを保持しているクエリの特定Determine Which Queries Are Holding Locks

データベース管理者は、データベース パフォーマンスを低下させているロックのソースを特定しなければならないことがよくあります。Database administrators often need to identify the source of locks that are hindering database performance.

たとえば、サーバーのパフォーマンスの問題が、ブロックに起因する可能性があるとします。For example, you suspect that a performance issue on your server could be caused by blocking. sys.dm_exec_requests に対してクエリを実行すると、複数のセッションが中断モードになっており、待機の種類から、ロックが待機対象のリソースとなっていることがわかりました。When you query the sys.dm_exec_requests, you find several sessions in a suspended mode with a wait type indicating that a lock is the resource being waited on.

sys.dm_tran_locks に対してクエリを実行した結果、未解決のロックが多数存在しているにもかかわらず、ロックが許可されたセッションの sys.dm_exec_requests にアクティブな要求がないことがわかりました。You query sys.dm_tran_locks and the results show that there are many locks outstanding, but the sessions that were granted the locks do not have any active requests showing in sys.dm_exec_requests.

次の例では、ロックが取得されたときのクエリ、クエリのプラン、および Transact-SQLTransact-SQL スタックを特定する方法を示します。This example demonstrates a method of determining what query took the lock, the plan of the query, and the Transact-SQLTransact-SQL stack at the time the lock was taken. さらに、拡張イベント セッションでのペアリング ターゲットの使用法も示します。This example also illustrates how the pairing target is used in an Extended Events session.

この作業には、 SQL Server Management StudioSQL Server Management Studio のクエリ エディターを使用した次の手順の実行も含まれます。Accomplishing this task involves using Query Editor in SQL Server Management StudioSQL Server Management Studio to carry out the following procedure.

注意

この例では、AdventureWorks データベースを使用します。This example uses the AdventureWorks database.

ロックを保持しているクエリを特定するにはTo determine which queries are holding locks

  1. クエリ エディターで、次のステートメントを実行します。In Query Editor, issue the following statements.

    -- 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. サーバーでワークロードを実行したら、クエリ エディターで次のステートメントを実行して、ロックを保持しているクエリを見つけます。After execution of a workload on the server, issue the following statements in Query Editor to find queries still holding locks.

    --  
    -- 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. 問題を特定したら、一時テーブルとイベント セッションを削除します。After identifying the issues, drop any temporary tables and the event session.

    DROP TABLE #unmatched_locks  
    DROP EVENT SESSION FindBlockers ON SERVER  
    

関連項目See Also

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