가장 많은 잠금이 발생한 개체 찾기Find the Objects That Have the Most Locks Taken on Them

이 항목 적용 대상: 예SQL Server예Azure SQL 데이터베이스없습니다Azure SQL 데이터 웨어하우스 없습니다 병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

데이터베이스 관리자는 종종 데이터베이스 성능을 저하시키는 잠금의 원인을 파악해야 합니다.Database administrators often need to identify the source of locks that are hindering database performance.

예를 들어 프로덕션 서버에서 병목 상태가 발생할 수 있는지 모니터링하고 있는데For example, you are monitoring your production server for any possible bottlenecks. 경쟁이 심한 리소스가 있다고 의심되어 이러한 개체에 대해 수행할 수 있는 잠금 수를 확인하려고 합니다.You suspect that there might be highly contested resources, and would like to know how many locks are taken on those objects. 가장 빈번하게 잠기는 개체가 식별되면 경쟁하는 개체에 대한 액세스를 최적화하는 조치를 취할 수 있습니다.Once the most frequently locked objects are identified, steps can be taken to optimize access to the contended objects.

이렇게 하려면 SQL Server Management StudioSQL Server Management Studio의 쿼리 편집기를 사용합니다.To do this, use Query Editor in SQL Server Management StudioSQL Server Management Studio.

가장 많은 잠금이 발생한 개체를 찾으려면To find the objects that have the most locks

  1. 쿼리 편집기에서 다음 문을 실행합니다.In Query Editor, issue the following statements.

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

    이 프로시저의 문이 끝나면 쿼리 편집기의 결과 탭에 다음 열이 표시됩니다.After the statements in this procedure finish, the Results tab of Query Editor displays the following columns:

  • namename

  • object_idobject_id

  • lock_countlock_count

참고 항목See Also

CREATE EVENT SESSION(Transact-SQL) CREATE EVENT SESSION (Transact-SQL)
ALTER EVENT SESSION(Transact-SQL) ALTER 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)
sys.server_event_sessions(Transact-SQL)sys.server_event_sessions (Transact-SQL)