Cercare gli oggetti con il maggior numero di blocchi acquisiti

Si applica a: SQL Server, Database SQL di Azure

Gli amministratori di database hanno spesso la necessità di individuare l'origine dei blocchi che hanno effetti negativi sulle prestazioni del database.

Si supponga ad esempio di eseguire il monitoraggio del server di produzione per individuare eventuali colli di bottiglia. Si ritiene che potrebbero essere presenti risorse contese e si desidera conoscere il numero di blocchi acquisiti su tali oggetti. Una volta identificati gli oggetti bloccati con maggiore frequenza, è possibile effettuare i passaggi che consentono di ottimizzare l'accesso agli oggetti contesi.

Per fare ciò, utilizzare l'editor di query di SQL Server Management Studio.

Per cercare gli oggetti con il maggior numero di blocchi

1. Nell'editor di query eseguire le istruzioni indicate di seguito.

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

L'esempio di codice Transact-SQL precedente viene eseguito in SQL Server locale, ma potrebbe non essere eseguito in database SQL di Azure. Anche le parti della memoria centrale dell'esempio che coinvolgono direttamente eventi, come ADD EVENT sqlserver.lock_acquired funzionano anche su database SQL di Azure. Tuttavia, gli elementi preliminari, ad esempio sys.server_event_sessions, devono essere modificati nelle relative controparti del database SQL di Azure, ad esempio sys.database_event_sessions per l'esecuzione dell'esempio. Per altre informazioni su queste differenze minime tra SQL Server locale e il database SQL di Azure, vedere gli articoli seguenti:

• Eventi estesi nel database SQL di Azure
• Oggetti di sistema che supportano Eventi estesi

Al termine delle istruzioni nello script Transact-SQL precedente, nella scheda Risultati dell'editor di query vengono visualizzate le colonne seguenti:

• name
• object_id
• lock_count

Vedi anche

CREARE LA SESSIONE DI EVENTI (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)