Buscar los objetos que han obtenido más bloqueos

Se aplica a: SQL Server Azure SQL Database

A menudo, los administradores de bases de datos necesitan identificar el origen de bloqueos que reducen el rendimiento de la base de datos.

Por ejemplo, suponga que está supervisando posibles cuellos de botella en el servidor de producción. Sospecha que podría haber recursos muy disputados y desearía saber cuántos bloqueos se han realizado en esos objetos. Una vez identificados los objetos que se bloquean con más con frecuencia, se pueden tomar medidas para optimizar el acceso a dichos objetos.

Para hacerlo, utilice el Editor de consultas de SQL Server Management Studio.

Para buscar los objetos que han obtenido más bloqueos

1. En el Editor de consultas, emita las instrucciones siguientes.

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

Nota:

El ejemplo de código de Transact-SQL anterior se ejecuta en SQL Server local, pero es posible que no se ejecute bien en Azure SQL Database. Las partes principales del ejemplo implican directamente eventos, como ADD EVENT sqlserver.lock_acquired funcionando también en Azure SQL Database. Sin embargo, los elementos preliminares, como sys.server_event_sessions, se deben editar en sus homólogos de Azure SQL Database, como sys.database_event_sessions, para que se ejecute el ejemplo. Para obtener más información acerca de estas pequeñas diferencias entre SQL Server local y Azure SQL Database, consulte los siguientes artículos:

• Eventos extendidos en Base de datos SQL de Azure
• Objetos del sistema que admiten Eventos extendidos

Una vez finalizadas las instrucciones del script de Transact-SQL anterior, en la pestaña Resultados del Editor de consultas se mostrarán las columnas siguientes:

• nombre
• object_id
• lock_count

Consulte también

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)