ロックの大半を取得しているオブジェクトを見つける

適用対象: はいSQL Server (サポートされているすべてのバージョン) はいAzure SQL データベース

データベース管理者は、データベース パフォーマンスを低下させているロックのソースを特定しなければならないことがよくあります。

たとえば、実稼動サーバーのボトルネックを監視しているとします。 競合の多いリソースがあるため、それらのオブジェクトで取得されているロックの数を調べようと思います。 最も頻繁にロックされているオブジェクトを特定できれば、競合するオブジェクトへのアクセスを最適化する手順を実行できます。

そのためには、 SQL Server Management Studioのクエリ エディターを使用します。

ロックの大半を取得しているオブジェクトを見つけるには

  1. クエリ エディターで、次のステートメントを実行します。

    -- Find objects in a particular database that have the most
    -- lock acquired. This sample uses AdventureWorksDW2012.
    -- Create the session and add an event and target.
    
    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LockCounts')
        DROP EVENT session LockCounts ON SERVER;
    GO
    DECLARE @dbid int;
    
    SELECT @dbid = db_id('AdventureWorksDW2012');
    
    DECLARE @sql nvarchar(1024);
    SET @sql = '
        CREATE event session LockCounts ON SERVER
            ADD EVENT sqlserver.lock_acquired (WHERE database_id ='
                + CAST(@dbid AS nvarchar) +')
            ADD TARGET package0.histogram(
                SET filtering_event_name=''sqlserver.lock_acquired'',
                    source_type=0, source=''resource_0'')';
    
    EXEC (@sql);
    GO
    ALTER EVENT session LockCounts ON SERVER
        STATE=start;
    GO
    -- Create a simple workload that takes locks.
    
    USE AdventureWorksDW2012;
    GO
    SELECT TOP 1 * FROM dbo.vAssocSeqLineItems;
    GO
    -- The histogram target output is available from the
    -- sys.dm_xe_session_targets dynamic management view in
    -- XML format.
    -- The following query joins the bucketizing target output with
    -- sys.objects to obtain the object names.
    
    SELECT name, object_id, lock_count
        FROM
        (
        SELECT objstats.value('.','bigint') AS lobject_id,
            objstats.value('@count', 'bigint') AS lock_count
            FROM (
                SELECT CAST(xest.target_data AS XML)
                    LockData
                FROM     sys.dm_xe_session_targets xest
                    JOIN sys.dm_xe_sessions        xes  ON xes.address = xest.event_session_address
                    JOIN sys.server_event_sessions ses  ON xes.name    = ses.name
                WHERE xest.target_name = 'histogram' AND xes.name = 'LockCounts'
                 ) Locks
            CROSS APPLY LockData.nodes('//HistogramTarget/Slot') AS T(objstats)
        ) LockedObjects
        INNER JOIN sys.objects o  ON LockedObjects.lobject_id = o.object_id
        WHERE o.type != 'S' AND o.type = 'U'
        ORDER BY lock_count desc;
    GO
    
    -- Stop the event session.
    
    ALTER EVENT SESSION LockCounts ON SERVER
        state=stop;
    GO
    

注意

上記の Transact-SQL コードの例は、オンプレミスの SQL Server で実行されますが、"Azure SQL Database では完全には実行されない" 可能性があります。 この例で、ADD EVENT sqlserver.lock_acquired など、イベントに直接関係している中核の部分は、Azure SQL Database でも機能します。 ただし、sys.server_event_sessions などの予備項目は、例を実行するために sys.database_event_sessions のように、対応する Azure SQL Database に編集する必要があります。 オンプレミスの SQL Server と Azure SQL Database のこれらの軽微な違いの詳細については、次の記事を参照してください。

上記の Transact-SQL スクリプトのステートメント実行が完了すると、クエリ エディターの [結果] タブに次の列が表示されます。

  • name
  • object_id
  • lock_count

参照

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