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

적용 대상: SQL Server Azure SQL Database

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

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

이렇게 하려면 SQL Server Management Studio에서 Query Editor를 사용합니다.

가장 잠금이 많은 개체 찾기

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 같은 예비 항목을 sys.database_event_sessions 같은 Azure SQL Database 대응 항목으로 편집해야 합니다. SQL Server 온-프레미스와 Azure SQL Database 간의 이러한 사소한 차이점에 대한 자세한 내용은 다음 문서를 참조하세요.

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