Trouver les objets comportant le plus de verrous

S’applique à : SQL Server Azure SQL Database

Les administrateurs de base de données ont souvent besoin d’identifier la source des verrous qui entravent les performances d’une base de données.

Par exemple, vous surveillez votre serveur de production pour identifier les goulots d'étranglement possibles. Vous soupçonnez que certaines ressources sont l’objet d’une concurrence très élevée, et aimeriez connaître le nombre de verrous posés sur ces objets. Une fois que les objets les plus fréquemment verrouillés ont été identifiés, des mesures peuvent être prises pour optimiser l'accès aux objets convoités.

Pour ce faire, utilisez l’éditeur de requête dans SQL Server Management Studio.

Pour trouver les objets comportant le plus de verrous

1. Dans l'éditeur de requêtes, émettez les instructions suivantes.

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

Remarque

L’exemple de code Transact-SQL précédent s’exécute sur SQL Server local, mais peut ne pas s’exécuter complètement sur la base de données Azure SQL. Les parties principales de l’exemple impliquant directement des événements, tels que ADD EVENT sqlserver.lock_acquired, fonctionnent également sur Azure SQL Database. Toutefois, les éléments préliminaires, tels que sys.server_event_sessions, doivent être modifiés en leurs équivalents Azure SQL Database comme sys.database_event_sessions pour que l’exemple s’exécute. Pour plus d’informations sur ces différences mineures entre SQL Server localement et Azure SQL Database, consultez les articles suivants :

• Événements étendus dans une base de données SQL Azure
• Objets système qui prennent en charge Extended Events

Une fois que les instructions du script Transact-SQL précédent sont exécutées, l’onglet Résultats de l’éditeur de requêtes affiche les colonnes suivantes :

• name
• object_id
• lock_count

Voir aussi

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)