Suchen der Objekte, die über die meisten Sperren verfügen

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank

Datenbankadministratoren müssen oft die Quelle von Sperren identifizieren, die die Datenbankleistung beeinträchtigen.

Sie überwachen z. B. den Produktionsserver auf alle möglichen Engpässe. Sie erwarten einen starken Wettbewerb um die Ressourcen und würden daher gern ermitteln, wie viele Sperren für diese Objekte gelten. Nachdem die am häufigsten gesperrten Objekte ermittelt sind, können Sie Schritte zur Optimierung des Zugangs auf die im Wettbewerb befindlichen Objekte ergreifen.

Um dies zu tun, verwenden Sie Abfrage-Editor in SQL Server Management Studio.

So suchen Sie die Objekte, die über die meisten Sperren verfügen

  1. Führen Sie im Abfrage-Editor die folgenden Anweisungen aus.

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

Hinweis

Das vorstehende Transact-SQL-Codebeispiel wird lokal auf SQL Server ausgeführt, kann aber möglicherweise nicht ganz auf Azure SQL-Datenbank ausgeführt werden. Die Kernabschnitte des Beispiels, die Ereignisse direkt einbeziehen, wie z. B. ADD EVENT sqlserver.lock_acquired, arbeiten auch an Azure SQL-Datenbank. Vorläufige Elemente wie sys.server_event_sessions müssen jedoch für die Azure SQL-Datenbank-Pendats wie sys.database_event_sessions angepasst werden, damit das Beispiel ausgeführt werden kann. Weitere Informationen zu diesen kleinen Unterschieden zwischen einer lokalen SQL Server-Instanz und Azure SQL-Datenbank finden Sie in den folgenden Artikeln:

Nach Abschluss der Anweisungen im vorangehenden Transact-SQL-Skript werden auf der Registerkarte Ergebnisse des Abfrage-Editors die folgenden Spalten angezeigt:

  • name
  • object_id
  • lock_count

Weitere Informationen

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)