Verstehen und Beheben von Problemen durch Blockierungen in SQL Server

Gilt für: SQL Server Azure SQL Managed Instance

Ursprüngliche KB-Nummer: 224453

Zusammenfassung

Das Blockieren ist in jeder sperrbasierten relationalen Datenbank normal, aber durch dauerhaftes oder langes Blockieren wird der Durchsatz beeinträchtigt und führt zu Zeitüberschreitungen der Anwendung. In diesem Artikel wird beschrieben, was in SQL Server blockiert wird, wie Daten aus dynamischen Verwaltungsansichten (DYNAMIC Management Views, DMVs) und erweiterten Ereignissen erfasst und wie der Kopfblocker identifiziert wird. Anschließend werden die am häufigsten verwendeten Blockierungsszenarien und deren Auflösungen erläutert.

In diesem Artikel bezieht sich der Begriffsverbindung auf eine einzelne angemeldete Sitzung der Datenbank. Jede Verbindung wird als Sitzungs-ID (SPID) oder session_id in vielen DMVs angezeigt. Jede SPID wird häufig als "Prozess" bezeichnet, ist aber kein separater Betriebssystemprozess. Es sind die serverseitigen Ressourcen und Datenstrukturen, die eine Clientverbindung bereitstellen. Eine einzelne Clientanwendung kann eine oder mehrere Verbindungen haben. Aus SQL Server Perspektive werden Verbindungen von vielen Clients und viele Verbindungen von einem Client gleich behandelt. Eine Verbindung kann eine andere Verbindung blockieren, unabhängig vom Quellclient.

Was blockiert

Blockierungen sind ein unvermeidbares und entwurfsbedingtes Merkmal von Managementsystemen für relationale Datenbanken (Relational Database Management Systems, RDBMSs) mit sperrbasierter Parallelität. Wie bereits erwähnt, erfolgt in SQL Server das Blockieren, wenn eine Sitzung eine Sperre für eine bestimmte Ressource hält und eine zweite SPID versucht, einen konkurrierenden Sperrtyp für dieselbe Ressource zu erlangen. In der Regel ist der Zeitrahmen, in dem die erste SPID die Ressource sperrt, sehr klein. Wenn die Sitzung, die Besitzer der Ressource ist, die Sperre aufhebt, kann die zweite Verbindung die Ressource ihrerseits sperren und die Verarbeitung fortsetzen. Das hier beschriebene Blockieren ist ein normales Phänomen, das im Laufe eines Tages viele Male auftreten kann, ohne dass sich dies auf die Systemleistung auswirkt.

Dauer und Transaktionskontext einer Abfrage bestimmen, wie lange die Sperren aufrechterhalten werden. Daraus ergeben sich die Auswirkungen auf andere Abfragen. Wenn die Abfrage nicht innerhalb einer Transaktion ausgeführt wird (und keine Sperrhinweise verwendet werden), werden die Sperren für SELECT-Anweisungen nur zu dem Zeitpunkt auf einer Ressource gehalten, zu dem sie tatsächlich gelesen wird, nicht während der Abfrage. Bei INSERT-, UPDATE- und DELETE-Anweisungen bleiben die Sperren während der gesamten Abfrage erhalten. Dies geschieht aus Gründen der Datenkonsistenz und um bei Bedarf einen Rollback der Abfrage zu ermöglichen.

Bei Abfragen, die innerhalb einer Transaktion ausgeführt werden, bestimmen der Abfragetyp, die Transaktionsisolationsstufe und ob Sperrhinweise in der Abfrage verwendet werden, die Dauer, für die die Sperren aufbewahrt werden. Beschreibungen von Sperren, Sperrhinweisen und Isolationsstufen für Transaktionen finden Sie in den folgenden Artikeln:

• Sperren in der Datenbank-Engine
• Anpassen von Sperren und Zeilenversionsverwaltung
• Sperrmodi
• Kompatibilität von Sperren
• Zeilenversionsbasierte Isolationsstufen in der Datenbank-Engine
• Transaktionen

Wenn das Sperren und Blockieren an dem Punkt beibehalten wird, an dem sie sich negativ auf die Systemleistung auswirken, ergeben sie sich aus einem der folgenden Gründe:

• Eine SPID hält eine Gruppe von Ressourcen für einen längeren Zeitraum fest, bevor sie freigegeben werden. Diese Art von Blockierung löst sich mit der Zeit von selbst, kann aber zu Leistungsbeeinträchtigungen führen.

• Eine SPID hält Sperren für eine Gruppe von Ressourcen und gibt sie niemals frei. Diese Art von Blockierung löst sich nicht von selbst und verhindert für unbegrenzte Zeit den Zugriff auf die betroffenen Ressourcen.

Im ersten Szenario kann sich die Situation sehr schnell ändern, weil verschiedene SPIDs im Lauf der Zeit Blockierungen verschiedener Ressourcen verursachen. Dies erschwert die Problemlösung. In solchen Situationen ist die Problembehandlung mit SQL Server Management Studio schwierig, da sich ein Problem kaum auf einzelne Abfragen eingrenzen lässt. Im Gegensatz dazu führt die zweite Situation zu einem konsistenten Zustand, der einfacher zu diagnostizieren ist.

Anwendungen und Blockierungen

Wenn ein Blockierungsproblem auftritt, konzentrieren Sie sich möglicherweise auf serverseitige Optimierungs- und Plattformprobleme. Wenn Sie jedoch nur auf die Datenbank achten, kann dies nicht zu einer Lösung führen. Es kann Zeit und Energie aufnehmen, die Sie besser bei der Prüfung der Clientanwendung und der übermittelten Abfragen direkt ausführen können. Unabhängig davon, welche Sichtbarkeitsebene die Anwendung bezüglich der von ihr ausgeführten Datenbankaufrufe verfügbar macht, erfordert ein Blockierungsproblem häufig sowohl die Überprüfung der genauen SQL-Anweisungen, die die Anwendung übermittelt, als auch das genaue Verhalten der Anwendung bezüglich Abfrageabbruch, Verbindungsverwaltung, Abrufen aller Ergebniszeilen usw. Wenn das Entwicklungstool keine explizite Kontrolle über die Verbindungsverwaltung, Abfrageabbruch, Abfragetimeout, Ergebnisabrufe usw. zulässt, können Sie möglicherweise keine Blockierungsprobleme beheben. Untersuchen Sie dieses Potenzial genau, bevor Sie ein Anwendungsentwicklungstool für SQL Server auswählen, insbesondere für leistungsempfindliche OLTP-Umgebungen.

Achten Sie während der Entwurfs- und Erstellungsphase von Datenbank und Anwendung auf die Datenbankleistung. Bewerten Sie insbesondere den Ressourcenverbrauch, die Isolationsebene und die Länge des Transaktionspfads für jede Abfrage. Jede Abfrage und jede Transaktion sollte so schlank wie möglich sein. Üben Sie eine gute Verbindungsverwaltungsdisziplin aus. Ohne dies scheint die Anwendung bei geringer Anzahl von Benutzern eine akzeptable Leistung zu haben, aber die Leistung kann erheblich beeinträchtigt werden, da die Anzahl der Benutzer nach oben skaliert wird.

Mit dem richtigen Anwendungs- und Abfrageentwurf kann SQL Server viele Tausende gleichzeitiger Benutzer auf einem einzelnen Server unterstützen, mit geringem Blockieren.

Problembehandlung bei Blockierungen

Unabhängig davon, in welcher Sperrsituation Sie sich befinden, ist die Methodik für die Problembehandlung bei der Sperrung identisch. Diese logischen Trennungen diktieren den Rest der Zusammensetzung dieses Artikels. Das Konzept besteht darin, den Anfang der Blockierung zu finden und zu ermitteln, was die Abfrage tut und warum sie blockiert wird. Nachdem Sie die problematische Abfrage identifiziert haben (d. h., was Sperren für den längeren Zeitraum hält), besteht der nächste Schritt darin, zu analysieren und zu bestimmen, warum die Blockierung stattfindet. Nachdem Sie den Grund verstanden haben, können Sie Änderungen vornehmen, indem Sie die Abfrage und die Transaktion neu entwerfen.

Schritte bei der Problembehandlung:

1. Identifizieren der Hauptblockierungssitzung (Kopfblocker).

2. Suchen Sie die Abfrage und Transaktion, die die Blockierung verursacht (was Sperrungen für einen längeren Zeitraum enthält).

3. Analysieren und verstehen Sie, warum die längere Blockierung auftritt.

4. Beheben Sie das Blockierungsproblem, indem Sie die Abfrage und Transaktion neu entwerfen.

Im Folgenden wird erläutert, wie Sie die wesentliche blockierende Sitzung mithilfe einer geeigneten Datenerfassung genau lokalisieren.

Sammeln von Informationen zur Blockierung

Um der Schwierigkeit bei der Behandlung von Blockierungsproblemen entgegenzuwirken, kann ein Datenbankadministrator SQL-Skripte verwenden, die den Status des Sperrens und Blockierens für SQL Server ständig überwachen. Um diese Daten zu sammeln, gibt es zwei komplementäre Methoden.

Die erste Methode besteht darin, dynamische Verwaltungsobjekte (Dynamic Management Objects, DMOs) abzufragen und die Ergebnisse im Laufe der Zeit für den Vergleich zu speichern. Einige der in diesem Artikel beschriebenen Objekte sind dynamische Verwaltungssichten (Dynamic Management Views, DMVs), einige sind dynamische Verwaltungsfunktionen (Dynamic Management Functions, DMFs).

Die zweite Methode besteht darin, erweiterte Ereignisse (XEvents) oder SQL Profiler-Ablaufverfolgungen zu verwenden, um zu erfassen, was ausgeführt wird. Da SQL Trace und SQL Server Profiler veraltet sind, konzentriert sich dieses Handbuch zur Problembehandlung auf XEvents.

Sammeln von Informationen aus DMVs

Um die Blockierung zu beheben, verwenden Sie DMVs, um die SPID (Sitzungs-ID) am Kopf der Blockierungskette und der SQL-Anweisung zu identifizieren. Suchen Sie nach Opfer-SPIDs, die blockiert sind. Wenn eine SPID von einem anderen SPID blockiert wird, untersuchen Sie die SPID, die die Ressource besitzt (die blockierende SPID). Ist dieser Besitzer SPID ebenfalls blockiert? Sie können der Kette folgen, um den Kopfblocker zu finden und dann zu untersuchen, warum die Sperre erhalten bleibt.

Wenden Sie hierzu eine der folgenden Methoden an:

• Klicken Sie im Objekt-Explorer von SQL Server Management Studio (SSMS) mit der rechten Maustaste auf das Serverobjekt der obersten Ebene, erweitern Sie Berichte, erweitern Sie Standardberichte, und wählen Sie dann Aktivität – Alle blockierenden Transaktionen aus. Dieser Bericht zeigt aktuelle Transaktionen an der Spitze einer Blockierungskette. Wenn Sie die Transaktion erweitern, werden im Bericht die Transaktionen angezeigt, die von der Haupttransaktion blockiert werden. In diesem Bericht werden auch die blockierende SQL-Anweisung und die blockierte SQL-Anweisung angezeigt.

• Öffnen Sie den Aktivitätsmonitor in SSMS, und verweisen Sie auf die Spalte "Blockiert von ". Weitere Informationen finden Sie unter Activity Monitor.

Ausführlichere abfragebasierte Methoden sind auch mit DMVs verfügbar:

• Die sp_who Befehle und sp_who2 Befehle sind ältere Befehle, die alle aktuellen Sitzungen anzeigen. Der DMV sys.dm_exec_sessions gibt weitere Daten in einem Resultset zurück, das einfacher abzufragen und zu filtern ist. sys.dm_exec_sessions ist auch ein wesentlicher Bestandteil anderer Abfragen.

• Wenn Sie bereits eine bestimmte Sitzung identifiziert haben, verwenden DBCC INPUTBUFFER(<session_id>) Sie die letzte Anweisung, die eine Sitzung übermittelt hat. Ähnliche Ergebnisse werden von der sys.dm_exec_input_buffer dynamischen Verwaltungsfunktion (Dynamic Management Function, DMF) in einem Resultset zurückgegeben, das einfacher abzufragen und zu filtern ist, wobei die session_id und die request_id bereitgestellt werden. Mit folgender Anweisung können Sie beispielsweise die letzte von „session_id 66“ und „request_id 0“ übermittelte Abfrage zurückgeben:

SELECT * FROM sys.dm_exec_input_buffer (66,0);

• Verweisen Sie auf die sys.dm_exec_requests, und referenzieren Sie die Spalte blocking_session_id. Wenn blocking_session_id = 0, wird eine Sitzung nicht blockiert. Listet sys.dm_exec_requests zwar nur anforderungen auf, die derzeit ausgeführt werden, sys.dm_exec_sessions listen jedoch alle Verbindungen auf (aktiv oder nicht). In der nächsten Abfrage können wir auf dieser allgemeinen Verknüpfung zwischen sys.dm_exec_requests und sys.dm_exec_sessions aufbauen. Beachten Sie, dass die Abfrage aktiv mit SQL Server ausgeführt werden muss, um von sys.dm_exec_requestsdieser zurückgegeben werden zu können.

• Führen Sie diese Beispielabfrage aus, um mithilfe der DMVs sys.dm_exec_sql_text oder sys.dm_exec_input_buffer die aktiv ausgeführten Abfragen und ihren zugehörigen aktuellen SQL-Batchtext oder Eingabepuffertext zu finden. Wenn die Daten, die von der Spalte text von sys.dm_exec_sql_text zurückgegeben werden, NULL sind, wird die Abfrage derzeit nicht ausgeführt. In diesem Fall enthält die Spalte event_info von sys.dm_exec_input_buffer die letzte Befehlszeichenfolge, die an die SQL-Engine übergeben wurde. Mit dieser Abfrage können darüber hinaus Sitzungen identifiziert werden, die andere Sitzungen blockieren. Dabei wird eine nach Sitzungs-ID (session_id) aufgeschlüsselte Liste mit blockierten Sitzungs-IDs (session_ids) zurückgegeben.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

• Führen Sie diese vom Microsoft-Support bereitgestellte ausführlichere Beispielabfrage aus, um den Kopfteil einer blockierenden Kette mit mehreren Sitzungen zu identifizieren, einschließlich des Abfragetexts der Sitzungen, die an einer blockierenden Kette beteiligt sind.

WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

• Verwenden Sie einen anderen Satz von DMVs zum Anzeigen aktueller offener Transaktionen, einschließlich sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_connections und sys.dm_exec_sql_text, um lang laufende oder nicht abgeschlossene Transaktionen abzufangen. Weitere DMVs im Zusammenhang mit der Nachverfolgung von Transaktionen finden Sie unter DMVs für Transaktionen.

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];

• Verweisen Sie auf sys.dm_os_waiting_tasks, die sich auf der Thread-/Aufgabenebene von SQL Server befindet. Dieser DMV gibt Informationen darüber zurück, welche SQL-wait_type die Anforderung derzeit auftritt. Ebenso wie sys.dm_exec_requests gibt sys.dm_os_waiting_tasks nur aktive Anforderungen zurück.

• Verwenden Sie die DMV sys.dm_tran_locks, um detaillierte Informationen zu den Sperren zu erhalten, die von Abfragen platziert wurden. Dieser DMV kann große Datenmengen in einer SQL Server-Produktionsinstanz zurückgeben und ist nützlich, um zu diagnostizieren, welche Sperren derzeit gehalten werden.

Aufgrund des INNER JOIN-Vorgangs in sys.dm_os_waiting_tasks, beschränkt die folgende Abfrage die Ausgabe von sys.dm_tran_locks auf aktuell blockierte Anforderungen, ihren Wartestatus und ihre Sperren:

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';

Mit DMVs werden durch Speichern der Abfrageergebnisse über einen längeren Zeitraum Datenpunkte bereitgestellt, mit denen Sie Blockierungen in einem bestimmten Zeitintervall überprüfen können, um persistente Blockierungen oder Trends zu ermitteln. Das beste Tool für CSS zur Fehlerbehebung bei solchen Problemen ist der PSSDiag-Datensammler. Dieses Tool verwendet die „SQL Server Perf Stats“, um Ergebnisse von DMVs, auf die oben verwiesen wird, im Laufe der Zeit zu sammeln. Da sich dieses Tool ständig weiterentwickelt, überprüfen Sie die neueste öffentliche Version von DiagManager auf GitHub.

Sammeln von Informationen aus erweiterten Ereignissen

Zusätzlich zu den vorherigen Informationen müssen Sie häufig eine Ablaufverfolgung der Aktivitäten auf dem Server erfassen, um ein Blockierungsproblem in SQL Server gründlich zu untersuchen. Wenn beispielsweise eine Sitzung mehrere Anweisungen innerhalb einer Transaktion ausführt, wird nur die letzte Anweisung dargestellt, die die Sitzung übermittelt. Eine der früheren Aussagen kann jedoch der Grund dafür sein, dass sperren noch gehalten werden. Mit einer Ablaufverfolgung können Sie alle Befehle anzeigen, die eine Sitzung innerhalb der aktuellen Transaktion ausführt.

Mithilfe von zwei Methoden können Sie Ablaufverfolgungen in SQL Server erfassen: Erweiterte Ereignisse (XEvents) und Profiler-Ablaufverfolgungen. SQL-Ablaufverfolgungen, die den SQL Server Profiler verwenden, sind jedoch veraltet. XEvents ist die neuere, überlegene Ablaufverfolgungsplattform, die mehr Vielseitigkeit und weniger Auswirkungen auf das beobachtete System bietet. Seine Schnittstelle ist in SSMS integriert.

In Objekt-Explorer finden Sie unter dem Menü für XEvent Profiler vordefinierte erweiterte Ereignissitzungen, die in SSMS gestartet werden können. Weitere Informationen finden Sie unter XEvent Profiler. Sie können auch eigene benutzerdefinierte erweiterte Ereignissitzungen in SSMS erstellen. Weitere Informationen finden Sie im Assistenten für neue erweiterte Ereignisse. Bei der Problembehandlung von Blockierungsproblemen erfassen Sie in der Regel Folgendes:

• Kategoriefehler:
  • Aufmerksamkeit
  • Blocked_process_report**
  • Error_reported (Kanaladministrator)
  • Exchange_spill
  • Execution_warning

**Um den Schwellenwert und die Häufigkeit zu konfigurieren, mit der Berichte über blockierte Prozesse erstellt werden, verwenden Sie den Befehl „sp_configure“, um die Option für den Schwellenwert für blockierte Prozesse zu konfigurieren, der in Sekunden angegeben werden kann. Standardmäßig werden für blockierte Prozesse keine Berichte erstellt.

• Kategorie „Warnungen“:

  • Hash_warning
  • Missing_column_statistics
  • Missing_join_predicate
  • Sort_warning

• Kategorie „Ausführung“:

  • Rpc_completed
  • Rpc_starting
  • Sql_batch_completed
  • Sql_batch_starting

• Kategoriesperre

  • Lock_deadlock

• Kategoriesitzung

  • Existing_connection
  • Anmelden
  • Logout

Identifizieren und Beheben von häufigen Blockierungsszenarien

Indem Sie die vorstehenden Informationen untersuchen, können Sie die Ursache der meisten Blockierungsprobleme ermitteln. Im restlichen Artikel wird erläutert, wie Sie diese Informationen verwenden, um einige gängige Blockierungsszenarien zu identifizieren und zu beheben. In dieser Diskussion wird davon ausgegangen, dass Sie die Blockierungsskripts (referenziert früher) verwenden, um Informationen zu den blockierten SPIDs zu erfassen und Anwendungsaktivitäten mithilfe einer XEvent-Sitzung zu erfassen.

Analysieren von Daten zur Blockierung

• Untersuchen Sie die Ausgabe der DMVs sys.dm_exec_requests und sys.dm_exec_sessions, und verwenden Sie dabei blocking_these und session_id, um den Anfang der blockierenden Ketten zu finden. Diese Ausgabe identifiziert eindeutig, welche Anforderungen blockiert werden und welche blockieren. Untersuchen Sie die blockierten und blockierenden Sitzungen genauer. Gibt es gemeinsame oder Stammelemente in der blockierenden Kette? Wahrscheinlich verwenden diese gemeinsam eine Tabelle, und mindestens eine der an einer blockierenden Kette beteiligten Sitzungen führt einen Schreibvorgang aus.

• Suchen Sie in der Ausgabe der DMVs sys.dm_exec_requests und sys.dm_exec_sessions nach Informationen zu den SPIDs am Anfang der blockierenden Kette. Suchen Sie nach den folgenden Spalten:

  • sys.dm_exec_requests.status

    Diese Spalte zeigt den Status einer bestimmten Anforderung an. In der Regel gibt ein Ruhestatus an, dass die SPID-Ausführung abgeschlossen wurde und darauf wartet, dass die Anwendung eine andere Abfrage oder einen anderen Batch sendet. Der Status „Ausführbar“ oder „Wird ausgeführt“ weist darauf hin, dass die SPID zurzeit eine Abfrage ausführt. In der folgenden Tabelle finden Sie kurze Erläuterungen der verschiedenen Statuswerte.

    Status	Bedeutung
    Hintergrund	Die SPID führt einen Hintergrundtask aus, z. B. eine Deadlockerkennung, einen Protokollwriter oder einen Prüfpunkt.
    Ruhezustand	Die SPID wird zurzeit nicht ausgeführt. Dies weist in der Regel darauf hin, dass die SPID auf einen Befehl der Anwendung wartet.
    Wird ausgeführt	Die SPID wird derzeit in einem Planer ausgeführt.
    Ausführbar	Die SPID befindet sich in der ausführbaren Warteschlange eines Planers und wartet auf die Zuteilung von Ausführungszeit.
    Ausgesetzt	Die SPID wartet auf eine Ressource, z. B. eine Sperre oder einen Latch.

  • sys.dm_exec_sessions.open_transaction_count

    In dieser Spalte wird die Anzahl der geöffneten Transaktionen in dieser Sitzung angezeigt. Wenn dieser Wert größer als 0 ist, befindet sich die SPID in einer geöffneten Transaktion und hält möglicherweise Sperren, die von einer Anweisung innerhalb der Transaktion erworben wurden. Die geöffnete Transaktion kann entweder durch eine aktuell aktive Anweisung oder durch eine Anweisungsanforderung erstellt werden, die in der Vergangenheit ausgeführt wurde und nicht mehr aktiv ist.

  • sys.dm_exec_requests.open_transaction_count

    In ähnlicher Weise zeigt diese Spalte die Anzahl der geöffneten Transaktionen in dieser Anforderung an. Wenn dieser Wert größer als 0 ist, befindet sich die SPID in einer geöffneten Transaktion und kann sperren, die von einer aktiven Anweisung innerhalb der Transaktion erworben wurden. Im Gegensatz dazu sys.dm_exec_sessions.open_transaction_countzeigt diese Spalte 0 an, wenn keine aktive Anforderung vorhanden ist.

  • sys.dm_exec_requests.wait_type, wait_time und last_wait_type

    Wenn dies sys.dm_exec_requests.wait_type NULL ist, wartet die Anforderung derzeit nicht auf etwas. Der last_wait_type Wert gibt den letzten wait_type Wert an, für den die Anforderung aufgetreten ist. Weitere Informationen zu sys.dm_os_wait_stats und eine Beschreibung der gängigsten Wartetypen finden Sie unter sys.dm_os_wait_stats. Mit dem wait_time-Wert kann ermittelt werden, ob die Anforderung fortgesetzt wird. Wenn eine Abfrage für die sys.dm_exec_requests Tabelle einen Wert in der Spalte zurückgibt, der wait_time kleiner als der wait_time Wert aus einer vorherigen Abfrage ist sys.dm_exec_requests, gibt diese Bedingung an, dass die vorherige Sperre erworben und freigegeben wurde und jetzt auf eine neue Sperre wartet (vorausgesetzt, nicht null wait_time). Sie können diese Bedingung überprüfen, indem Sie die wait_resource Zwischenausgabe sys.dm_exec_requests vergleichen, wodurch die Ressource angezeigt wird, für die die Anforderung wartet.

  • sys.dm_exec_requests.wait_resource

    Diese Spalte gibt die Ressource an, auf die eine blockierte Anforderung wartet. In der folgenden Tabelle sind gängige Formate für wait_resource sowie ihre Bedeutung aufgeführt:

    Resource	Format	Beispiel	Erklärung
    Tabelle	DatabaseID:ObjectID:IndexID	TAB: 5:261575970:1	In diesem Fall ist die Datenbank-ID 5 die pubs-Beispieldatenbank, object_id 261575970 die Titeltabelle und 1 der gruppierte Index.
    Seite	DatabaseID:FileID:PageID	SEITE: 5:1:104	In diesem Fall ist die Datenbank-ID 5 „pubs“, die Datei-ID 1 ist die primäre Datendatei, und Seite 104 ist eine Seite, die zur Titeltabelle gehört. Zum Identifizieren der „object_id“, zu der die Seite gehört, verwenden Sie die DMF sys.dm_db_page_info, und übergeben Sie die DatabaseID, FileId und PageId aus wait_resource.
    Schlüssel	DatabaseID:Hobt_id (Hashwert für Indexschlüssel)	SCHLÜSSEL: 5:72057594044284928 (3300a4f361aa)	In diesem Fall ist die Datenbank-ID 5 „pubs“, und die Hobt_ID 72057594044284928 entspricht der „index_id“ 2 für die „object_id“ 261575970 (Titeltabelle). Verwenden Sie die sys.partitions-Katalogansicht, um das hobt_id mit einem bestimmten index_id und object_id zu verknüpfen. Es gibt keine Möglichkeit, den Indexschlüsselhash in einen bestimmten Schlüsselwert aufzulösen.
    Zeile	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	In diesem Fall ist die Datenbank-ID 5 „pubs“, die Datei-ID 1 ist die primäre Datendatei, Seite 104 ist eine Seite, die zur Titeltabelle gehört, und Slot 3 gibt die Position der Zeile auf der Seite an.

  • sys.dm_tran_active_transactions: Die DMV sys.dm_tran_active_transactions enthält Daten zu offenen Transaktionen, die mit anderen DMVs verknüpft werden können, um ein vollständiges Bild der Transaktionen zu erhalten, die auf einen Commit oder Rollback warten. Verwenden Sie die folgende Abfrage, um Informationen zu offenen Transaktionen zurückzugeben, die mit anderen DMVs einschließlich sys.dm_tran_session_transactions verknüpft sind. Beachten Sie den aktuellen Zustand einer Transaktion, den Wert für transaction_begin_time sowie weitere situationsbezogene Daten, um zu bewerten, ob es sich möglicherweise um eine Blockierungsquelle handelt.

    SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                    WHEN 2 THEN 'Read-only transaction'
                                                    WHEN 3 THEN 'System transaction'
                                                    WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
                WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                WHEN 1 THEN 'The transaction has been initialized but has not started.'
                WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
                WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                WHEN 6 THEN 'The transaction has been committed.'
                WHEN 7 THEN 'The transaction is being rolled back.'
                WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
    FROM sys.dm_tran_active_transactions tat 
    INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
    INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
    LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;
    

  • Andere Spalten

    Die verbleibenden Spalten in sys.dm_exec_sessions und sys.dm_exec_request können ebenfalls Erkenntnisse zur Ursache des Problems bieten. Ihr Nutzen variiert je nach Art des Problems. Sie können z. B. feststellen, ob das Problem nur von bestimmten Clients (hostname) oder in bestimmten Netzwerkbibliotheken (client_interface_name) auftritt, wann der letzte von einer SPID übermittelte Batch last_request_start_time in sys.dm_exec_sessions war, wie lange eine Anforderung mithilfe von start_time in sys.dm_exec_requests ausgeführt wurde usw.

Häufige Blockierungsszenarien

In der folgenden Tabelle werden häufige Symptome ihren möglichen Ursachen zugeordnet.

Die Spalten wait_type, open_transaction_count und status beziehen sich auf Informationen, die von sys.dm_exec_request zurückgegeben werden, andere Spalten können von sys.dm_exec_sessions zurückgegeben werden. Die Spalte „Wird aufgelöst?“ gibt an, ob die Blockierung von selbst aufgelöst wird oder ob die Sitzung über den Befehl KILL beendet werden muss. Weitere Informationen finden Sie unter KILL (Transact-SQL).

Ausführliche Blockierungsszenarien

Scenario 1: Blockierung durch eine normal ausgeführte Abfrage mit langer Ausführungszeit

In diesem Szenario wurden für eine aktiv ausgeführte Abfrage Sperren abgerufen, und die Sperren werden nicht freigegeben (dies wird von der Transaktionsisolationsstufe beeinflusst). Daher warten andere Sitzungen auf die Sperren, bis sie freigegeben werden.

Lösung:

Optimieren Sie die Abfrage, um dieses Blockierungsproblem zu beheben. Diese Art von Blockierungsproblem kann ein Leistungsproblem sein. Behandeln Sie es als solche. Informationen zur Problembehandlung einer bestimmten langsam ausgeführten Abfrage finden Sie unter Problembehandlung bei langsam ausgeführten Abfragen in SQL Server. Weitere Informationen finden Sie unter Überwachen und Optimieren der Leistung.

Berichte, die von der Abfragespeicher (eingeführt in SQL Server 2016) in SSMS integriert wurden, sind auch ein äußerst empfohlenes und wertvolles Tool zum Identifizieren der kostspieligsten Abfragen und suboptimalen Ausführungspläne.

Wenn Sie eine lange Ausgeführte Abfrage blockieren, die andere Benutzer blockiert und sie nicht optimieren kann, sollten Sie sie von einer OLTP-Umgebung in ein dediziertes Berichtssystem verschieben. Sie können auch Always-On-Verfügbarkeitsgruppen verwenden, um ein schreibgeschütztes Replikat der Datenbank zu synchronisieren.

Szenario 2: Blockierung durch eine SPID im Ruhezustand, die eine nicht abgeschlossene Transaktion hat

Sie können diese Art der Blockierung häufig durch eine SPID identifizieren, die schlafen oder auf einen Befehl mit einer Transaktionsschachtelungsebene (@@TRANCOUNTopen_transaction_countvon sys.dm_exec_requests) größer als Null wartet. Diese Situation kann auftreten, wenn die Anwendung ein Abfragetimeout aufweist oder einen Abbruch ausgibt, ohne die erforderliche Anzahl von ROLLBACK- und/oder COMMIT-Anweisungen auszugeben. Wenn eine SPID ein Abfragetimeout oder einen Abbruch empfängt, beendet sie die aktuelle Abfrage und den Aktuellen Batch, führt aber kein automatisches Rollback oder Commit der Transaktion durch. Die Anwendung ist für diese Aktion verantwortlich, da SQL Server nicht davon ausgehen kann, dass eine gesamte Transaktion zurückgesetzt werden muss, da eine einzelne Abfrage abgebrochen wird. Das Abfragetimeout oder -abbruch wird als ATTENTION-Signalereignis für die SPID in der erweiterten Ereignissitzung angezeigt.

Um eine explizite Transaktion ohne Commit zu veranschaulichen, geben Sie die folgende Abfrage aus:

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

Führen Sie dann die folgende Abfrage im selben Fenster aus:

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

Die Ausgabe der zweiten Abfrage gibt an, dass die Anzahl der Transaktionen eins beträgt. Alle in der Transaktion erworbenen Sperren bleiben bis zum Commit oder Rollback der Transaktion erhalten. Wenn Anwendungen Transaktionen explizit öffnen und committen, könnte ein Kommunikations- oder sonstiger Fehler dazu führen, dass die Sitzung und die zugehörige Transaktion in einem offenen Zustand verbleiben.

Verwenden Sie das auf sys.dm_tran_active_transactions basierende Skript weiter oben in diesem Artikel, um Transaktionen in der Instanz zu identifizieren, für die derzeit noch kein Commit ausgeführt wurde.

Lösungen:

• Diese Klasse des Blockierungsproblems kann auch ein Leistungsproblem sein. Wenn Sie die Ausführungszeit der Abfrage verringern können, tritt möglicherweise kein Abfragetimeout oder -abbruch auf. Es ist wichtig, dass die Anwendung die Timeout- oder Abbruchszenarien verarbeiten kann, wenn sie auftreten, aber Sie können auch davon profitieren, die Leistung der Abfrage zu untersuchen.

• Anwendungen müssen Transaktionsschachtelungsebenen ordnungsgemäß verwalten, oder sie können ein Blockierungsproblem nach dem Abbruch der Abfrage auf diese Weise verursachen. Beachten Sie Folgendes:

  • Führen Sie im Fehlerhandler der Clientanwendung nach jedem Fehler IF @@TRANCOUNT > 0 ROLLBACK TRAN aus, auch wenn die Clientanwendung nicht anzeigt, dass eine Transaktion offen ist. Das Überprüfen auf geöffnete Transaktionen ist erforderlich, da eine gespeicherte Prozedur, die während des Batches aufgerufen wird, ohne das Wissen der Clientanwendung eine Transaktion gestartet haben könnte. Bestimmte Bedingungen – beispielsweise das Abbrechen einer Abfrage – verhindern die weitere Ausführung der Prozedur nach der aktuellen Anweisung. Daher wird dieser Rollbackcode in solchen Fällen auch dann nicht ausgeführt, wenn die Prozedur über eine Logik zum Überprüfen von IF @@ERROR <> 0 und Abbrechen der Transaktion verfügt.

  • Wenn Sie verbindungspooling in einer Anwendung verwenden, die die Verbindung öffnet und einige Abfragen ausführt, bevor Sie die Verbindung wieder mit dem Pool freigeben, z. B. eine webbasierte Anwendung, kann das vorübergehende Deaktivieren des Verbindungspools dazu beitragen, das Problem zu verringern, bis die Clientanwendung geändert wird, um die Fehler entsprechend zu behandeln. Durch das Deaktivieren des Verbindungspools führt das Freigeben der Verbindung zu einer physischen Verbindung der SQL Server Verbindung, was dazu führt, dass der Server alle geöffneten Transaktionen zurücksetzt.

  • Verwenden Sie SET XACT_ABORT ON für die Verbindung oder in allen gespeicherten Prozeduren, die Transaktionen beginnen und nach einem Fehler nicht bereinigen. Im Falle eines Laufzeitfehlers bricht diese Einstellung alle geöffneten Transaktionen ab und gibt die Steuerung an den Client zurück. Weitere Informationen finden Sie unter SET XACT_ABORT (Transact-SQL).

Szenario 3: Blockieren durch eine SPID, deren entsprechende Clientanwendung nicht alle Ergebniszeilen zum Abschluss abrufte

Nach dem Senden einer Abfrage an den Server müssen alle Anwendungen sofort alle Ergebniszeilen vollständig abrufen. Wenn eine Anwendung nicht alle Ergebniszeilen abruft, kann sie Sperren für die Tabellen belassen, die andere Benutzer blockieren. Sollten Sie eine Anwendung nutzen, die SQL-Anweisungen transparent an den Server übermittelt, muss die Anwendung sämtliche Ergebniszeilen abrufen. Andernfalls (oder wenn die Anwendung nicht dafür konfiguriert werden kann) lässt sich das Blockierproblem möglicherweise nicht lösen. Um das Problem zu vermeiden, beschränken Sie schlecht verhaltene Anwendungen auf eine Berichterstellung oder eine Entscheidungsunterstützungsdatenbank, getrennt von der OLTP-Hauptdatenbank.

Lösung:

Schreiben Sie die Anwendung neu, um alle Zeilen des Ergebnisses zum Abschluss abzurufen. Dies schließt das Verwenden von OFFSET und FETCH in der ORDER BY-Klausel einer Abfrage nicht aus, um ein serverseitiges Paging auszuführen.

Szenario 4: Blockierung durch einen über Client/Server verteilten Deadlock

Im Gegensatz zu einem herkömmlichen Deadlock kann ein verteilter Deadlock nicht mithilfe des RDBMS-Sperr-Managers erkannt werden. Dies liegt daran, dass nur eine der am Deadlock beteiligten Ressourcen eine SQL Server-Sperre ist. Die andere Seite des Deadlocks befindet sich auf Clientanwendungsebene, über die SQL Server keine Kontrolle hat. Die folgenden beiden Abschnitte zeigen Beispiele dafür, wie dies geschehen kann und welche Möglichkeiten die Anwendung hat, dies zu vermeiden.

Beispiel A: Über Client/Server verteilter Deadlock mit einem einzigen Client-Thread

Wenn der Client über mehrere offene Verbindungen und einen einzelnen Ausführungsthread verfügt, kann der folgende verteilte Deadlock auftreten. Beachten Sie, dass sich der hier verwendete Begriff dbproc auf die Client-Verbindungsstruktur bezieht.

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

Im vorherigen Fall verfügt ein einzelner Clientanwendungsthread über zwei offene Verbindungen. Er sendet asynchron einen SQL-Vorgang auf dbproc1. Dies bedeutet, dass beim Anruf nicht gewartet wird, bis er zurückkehrt, bevor der Vorgang fortgesetzt wird. Die Anwendung sendet dann einen weiteren SQL-Vorgang auf dbproc2 und wartet auf die Ergebnisse, um mit der Verarbeitung der zurückgegebenen Daten zu beginnen. Wenn Daten beginnen, zurückzukommen (je nachdem, was dbproc zuerst antwortet – vorausgesetzt, dies ist dbproc1), verarbeitet sie, um alle daten zu schließen, die für diesen dbproc zurückgegeben werden. Sie ruft Ergebnisse von dbproc1 ab, bis SPID1 für eine von SPID2 gehaltene Sperre blockiert wird (da die beiden Abfragen asynchron auf dem Server ausgeführt werden). Zu diesem Zeitpunkt wartet dbproc1 auf unbestimmte Zeit auf weitere Daten. SPID2 ist nicht für eine Sperre blockiert, versucht aber, Daten an den Client dbproc2 zu senden. Dbproc2 wird jedoch auf der Anwendungsebene für dbproc1 effektiv blockiert, da der einzelne Ausführungsthread für die Anwendung von dbproc1 verwendet wird. Diese Situation führt zu einem Deadlock, der SQL Server nicht erkennen oder auflösen kann, da nur eine der beteiligten Ressourcen eine SQL Server Ressource ist.

Beispiel B: Verteilter Client/Server-Deadlock mit einem Thread pro Verbindung

Selbst wenn für jede Verbindung auf dem Client ein separater Thread vorhanden ist, kann eine Variante dieses verteilten Deadlocks weiterhin auftreten, wie im folgenden Beispiel gezeigt.

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

Dieser Fall ähnelt Beispiel A, außer dass dbproc2 und SPID2 eine SELECT-Anweisung mit der Absicht ausführen, eine zeilenweise Verarbeitung durchzuführen und jede Zeile über einen Puffer an dbproc1 für eine INSERT-, UPDATE- oder DELETE-Anweisung für dieselbe Tabelle zu übergeben. Schließlich wird SPID1 (Ausführen von INSERT, UPDATE oder DELETE) für eine Sperre blockiert, die von SPID2 gehalten wird (ausführen des SELECT). SPID2 schreibt eine Ergebniszeile in den Client dbproc2. Dbproc2 versucht dann, die Zeile in einem Puffer an dbproc1 zu übergeben, findet jedoch, dass dbproc1 ausgelastet ist (es wird nämlich blockiert durch das Warten darauf, dass SPID1 den aktuellen INSERT abschließt, der für SPID2 blockiert ist). An diesem Punkt wird dbproc2 auf Anwendungsebene durch dbproc1 blockiert, dessen SPID (SPID1) auf Datenbankebene durch SPID2 blockiert wird. Dies führt wiederum zu einem Deadlock, den SQL Server nicht erkennen oder auflösen kann, da nur eine der beteiligten Ressourcen eine SQL Server-Ressource ist.

Beide Beispiele A und B sind grundlegende Probleme, die Anwendungsentwickler beachten müssen. Sie müssen Anwendungen so codieren, dass sie diese Fälle angemessen behandeln.

Lösung:

Wenn Sie ein Abfragetimeout angeben, wenn der verteilte Deadlock auftritt, bricht das Timeout den Deadlock. Weitere Informationen zur Verwendung eines Abfragetimeouts finden Sie in der Dokumentation zum Verbindungsanbieter.

Szenario 5: Blockierung durch eine Sitzung in einem Rollbackstatus

Wenn Sie eine Datenänderungsabfrage außerhalb einer benutzerdefinierten Transaktion beenden oder abbrechen, wird die Abfrage zurückgesetzt. Dieses Rollback kann auch als Nebeneffekt der Verbindung der Clientnetzwerksitzung, des Neustarts des Clientcomputers oder der Anforderung, die als Deadlock-Opfer ausgewählt wird, auftreten. Häufig können Sie eine Datenänderungsabfrage nicht schneller zurücksetzen, als die Änderungen ursprünglich angewendet wurden. Wenn beispielsweise ein , oder INSERTUPDATE eine DELETEAnweisung eine Stunde lang ausgeführt wurde, kann es mindestens eine Stunde dauern, bis ein Rollback ausgeführt wird. Dieses Verhalten wird erwartet, da, wenn die Änderungen nicht zurückgesetzt werden, transaktionsale und physische Integrität in der Datenbank kompromittiert werden. Da dieses Rollback abgeschlossen sein muss, markiert SQL Server die Sitzung in einem KILL/ROLLBACK-Zustand, und Sie können die Sitzung nicht erneut beenden oder als Deadlock-Opfer auswählen. Sie können diesen Zustand häufig identifizieren, indem Sie die Ausgabe sys.dm_exec_requestsbeobachten, in der die command Spaltenberichte KILLED/ROLLBACK und die percent_complete Spalte den Fortschritt anzeigen.

Lösung:

Warten Sie, bis die Sitzung das Rollback der Änderungen abgeschlossen hat.

Wenn Sie die Instanz in der Mitte dieses Vorgangs herunterfahren, befindet sich die Datenbank beim Neustart im Wiederherstellungsmodus, und es kann nicht darauf zugegriffen werden, bis alle geöffneten Transaktionen verarbeitet werden. Die Wiederherstellung beim Start benötigt im Wesentlichen die gleiche Zeit pro Transaktion wie die Laufzeitwiederherstellung, und während dieses Zeitraums kann nicht auf die Datenbank zugegriffen werden. Daher ist es häufig kontraproduktiv, den Server zu zwingen, eine SPID in einem Rollbackzustand zu reparieren. In SQL Server 2019 mit aktivierter beschleunigter Datenbankwiederherstellung sollte diese Situation nicht auftreten.

Um diese Situation zu vermeiden, führen Sie in OLTP-Systemen in Zeiträumen mit sehr viel Aktivität keine umfangreichen Batchschreibvorgänge oder Indexerstellungs- oder Wartungsvorgänge aus. Führen Sie solche Vorgänge nach Möglichkeit in Zeiträumen mit geringer Aktivität aus.

Szenario 6: Blockierung durch eine verwaiste Verbindung

Dies ist ein häufiges Problemszenario und überlappt sich teilweise mit Szenario 2. Wenn die Clientanwendung beendet wird, die Clientarbeitsstation neu gestartet wird oder ein Batchabbruchfehler auftritt, bleibt möglicherweise eine Transaktion geöffnet. Diese Situation kann auftreten, wenn die Anwendung nicht das Rollback der Transaktion in den Blöcken CATCH oder FINALLY der Anwendung vornimmt, oder wenn sie diese Situation nicht anderweitig behandelt.

In diesem Szenario lässt die Anwendung die SQL-Transaktion offen, obwohl die Ausführung eines SQL-Batches abgebrochen wurde. Aus Sicht der SQL Server-Instanz scheint der Client weiterhin vorhanden zu sein, und alle erworbenen Sperren bleiben erhalten.

Um eine verwaiste Transaktion zu veranschaulichen, führen Sie die folgende Abfrage aus, die einen Batchabbruchfehler simuliert, indem Sie Daten in eine nicht vorhandene Tabelle einfügen:

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

Führen Sie dann die folgende Abfrage im selben Fenster aus:

SELECT @@TRANCOUNT;

Die Ausgabe der zweiten Abfrage gibt an, dass die Anzahl der Transaktionen eins beträgt. Alle in der Transaktion erworbenen Sperren bleiben bis zum Commit oder Rollback der Transaktion erhalten. Da der Batch bereits von der Abfrage abgebrochen wurde, kann die Anwendung, die sie ausführt, andere Abfragen in derselben Sitzung fortsetzen, ohne die noch geöffnete Transaktion zu bereinigen. Die Sperre wird so lange gehalten, bis die Sitzung beendet oder die SQL Server-Instanz neu gestartet wird.

Lösungen:

• Die beste Möglichkeit, diese Bedingung zu verhindern, besteht darin, die Behandlung von Anwendungsfehlern und Ausnahmen zu verbessern, insbesondere für unerwartete Beendigungen. Stellen Sie sicher, dass Sie einen Try-Catch-Finally-Block im Anwendungscode verwenden und die Transaktion im Falle einer Ausnahme zurücksetzen.
• Erwägen Sie die Verwendung von SET XACT_ABORT ON für die Sitzung oder in allen gespeicherten Prozeduren, die Transaktionen beginnen und nach einem Fehler nicht bereinigt werden. Bei einem Laufzeitfehler, der den Batch abbricht, setzt diese Einstellung automatisch alle geöffneten Transaktionen zurück und gibt die Steuerung an den Client zurück. Weitere Informationen finden Sie unter SET XACT_ABORT (Transact-SQL).
• Um eine verwaiste Verbindung einer Clientanwendung aufzulösen, die getrennt wurde, ohne die Ressourcen entsprechend zu bereinigen, können Sie die SPID mithilfe des Befehls KILL beenden. Weitere Informationen finden Sie unter KILL (Transact-SQL).

Der Befehl KILL akzeptiert den SPID-Wert als Eingabe. Um z. B. SPID 9 zu killen, führen Sie den folgenden Befehl aus:

KILL 99

Verwandte Inhalte

• Überwachen der Leistung mit dem Abfragespeicher
• Beheben von Blockierungsproblemen, die durch eine Sperreskalation in SQL Server verursacht werden
• Handbuch zu Transaktionssperren und Zeilenversionsverwaltung
• FESTLEGEN DER TRANSAKTIONSISOLATIONSEBENE
• Schnellstart: Erweiterte Ereignisse in SQL Server