가장 많은 잠금이 발생한 개체 찾기

적용 대상:SQL ServerAzure SQL Database

데이터베이스 관리자는 데이터베이스 성능을 저해하는 잠금의 원인을 식별해야 하는 경우가 많습니다.

예를 들어 프로덕션 서버에서 가능한 병목 상태를 모니터링합니다. 경쟁이 심한 리소스가 있다고 의심되어 이러한 개체에 대해 수행할 수 있는 잠금 수를 확인하려고 합니다. 가장 자주 잠긴 개체가 식별되면 경합된 개체에 대한 액세스를 최적화하기 위한 단계를 수행할 수 있습니다.

이렇게 하려면 SQL Server Management Studio에서 쿼리 편집기를 사용합니다.

잠금이 가장 많은 개체를 찾으려면

  1. 쿼리 편집기에서 다음 문을 실행합니다.

    -- Find objects in a particular database that have the most
    -- lock acquired. This sample uses AdventureWorksDW2022.
    -- 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('AdventureWorksDW2022');
    
    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 AdventureWorksDW2022;
    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 예비 항목을 Azure SQL Database sys.database_event_sessions 대응 항목으로 편집해야 합니다. SQL Server 온-프레미스와 Azure SQL Database 간의 이러한 사소한 차이점에 대한 자세한 내용은 다음 문서를 참조하세요.

앞의 Transact-SQL 스크립트가 끝나면 쿼리 편집기의 결과 탭에 다음 열이 표시됩니다.

  • 이름
  • 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)