Compartilhar via


Localizar os objetos que detêm a maioria dos bloqueios

Muitas vezes, os administradores de banco de dados precisam identificar a origem de bloqueios que estão obstruindo o desempenho do banco de dados.

Por exemplo, digamos que você esteja monitorando seu servidor de produção quanto a possíveis afunilamentos. Você suspeita que existem recursos altamente disputados e gostaria de saber quantos bloqueios esses objetos detêm. Uma vez identificados os objetos bloqueados com maior frequência, algumas medidas podem ser tomadas para otimizar o acesso aos objetos disputados.

Para fazer isso, use o Editor de Consultas em SQL Server Management Studio.

Para localizar os objetos que detêm a maioria dos bloqueios

  • No Editor de Consultas, emita as seguintes instruções:

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

Encerradas as instruções do procedimento, a guia Resultados do Editor de Consultas exibe as seguintes colunas:

  • name

  • object_id

  • lock_count

Consulte também

Referência

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)