Encontre os objetos que têm mais fechaduras feitas

Aplica-se a:SQL ServerBase de Dados SQL do AzureAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Os administradores de bases de dados muitas vezes precisam de identificar a origem dos bloqueios que dificultam o desempenho da base de dados.

Por exemplo, está a monitorizar o seu servidor de produção à procura de possíveis gargalos. Suspeitas que possam existir recursos altamente contestados e gostarias de saber quantos bloqueios são usados nesses objetos. Uma vez identificados os objetos mais frequentemente bloqueados, podem ser tomadas medidas para otimizar o acesso aos objetos contestados.

Para isso, use o Editor do Power Query no SQL Server Management Studio.

Para encontrar os objetos que têm mais fechaduras

1. No Editor de Consultas, emita as seguintes instruções.

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

Após as instruções do script Transact-SQL anterior terminarem, o separador Resultados do Editor de Consultas apresenta as seguintes colunas:

• nome
• object_id
• contagem_de_bloqueios

Conteúdo relacionado

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