SQL Server liebt Sperren.
SQL Server verwendet standardmäßig „pessimistische“ Sperren – Ihre Abfragen sind natürlich defensiv. Dies kann zu Blockierungsproblemen führen, die Sie daran hindern, SQL Server zu skalieren.
Kostenloser Videoschulungskurs – bevor Sie Blockierungen und Deadlocks beheben, beginnen Sie mit How to Think Like the Engine. In diesem rasanten 90-minütigen Kurs wird die Funktionsweise von geclusterten und nicht geclusterten Indizes erklärt, was für das Verständnis der Auswirkungen der Indizierung auf Blockierungen von entscheidender Bedeutung ist. Der Kurs ist kostenlos – denn wir sind der Meinung, dass jeder SQL Server-Experte seine Ausbildung hier beginnen sollte. Es ist wirklich so wichtig – gutes Material.
Tools zur Diagnose von Sperrkonflikten
„Habe ich im Moment Sperrwartezeiten?“ Verwenden Sie sp_BlitzFirst®, um zu diagnostizieren, ob Ihr SQL Server derzeit durch Lock Waits beeinträchtigt wird.
„Welche Tabellen haben die größten Blockierungsprobleme?“ Führen Sie sp_BlitzIndex® aus und suchen Sie nach „Aggressive Index Disorder“, um festzustellen, bei welchen Tabellen es zu einer hohen Anzahl von Lock Waits gekommen ist (für Tabellen und Indizes, die sich derzeit im Speicher befinden).
„Wie kann ich den Blocked Process Report sinnvoll nutzen?“ Wenn Sie den SQL Server Blocked Process Report (Skripte unten) erfassen, ist der Blocked Process Report Viewer von Michael J. Swart ein kostenloses Tool, das Ihnen bei der Interpretation der Ergebnisse hilft.
„Was ist, wenn meine Anwendung Deadlock-Fehler nicht erkennt?“ Amit Banerjee vom CSS-Team von Microsoft hilft Ihnen bei der Abfrage von SQL Server’s erweiterten Ereignissen System Health Session für Details (SQL Server 2008+).
Lock Related Performance Counters
Diese Leistungszähler können sehr nützlich sein, um einen Perf-Counter basierten SQL Server Agent Alert zu konfigurieren, so dass SQL Server Sie benachrichtigen kann, wenn die Blockierung die von Ihnen festgelegten Schwellenwerte überschreitet.
- Diese sind zuerst OBJECT, dann COUNTER
- SQL Server aufgeführt: Allgemeine Statistik – Blockierte Prozesse
- SQL Server: Sperren – Sperrwartezeit (ms)
- SQL Server: Locks – Number of Deadlocks/sec
Erfahren Sie hier mehr über Perf-Counter in SQL Server.
Lesen Sie Artikel über Locking, Isolation Levels und Deadlocks
Lesen Sie eine Einführung in Two-Phase Locking und Deadlocks von Jeremiah.
Lernen Sie von Kendra, wie Sie Codeänderungen testen können, um herauszufinden, welche Sperren erforderlich sind.
Jeremiah zeigt Ihnen, wie Sie Deadlocks finden und verhindern können.
Lernen Sie von Kendra, wie man optimistisches Sperren in SQL Server implementiert.
Lesen Sie mehr über Isolationsebenen in SQL Server – sehen Sie sich unsere Seite mit umfassenden Links und Referenzen an.
Skripte zur Verwendung des Blocked Process Report
Den „Blocked Process Report“ gibt es schon lange in SQL Server – und er ist immer noch ein nützliches Werkzeug. Standardmäßig wacht der Deadlock-Detektor in SQL Server alle 5 Sekunden auf, um zu prüfen, ob sich Ihre Abfragen in einer „tödlichen Umarmung“ befinden. Sie können eine Option aktivieren, die SQL Server auffordert, diese Ressource zu verwenden, um auf langfristige Blockierungen zu prüfen und einen Bericht auszugeben.
WARNUNG: Sie sollten den Bericht über blockierte Prozesse nur so konfigurieren, dass er für Werte von fünf Sekunden oder mehr ausgegeben wird. Microsoft warnt davor, dass bei einer Einstellung von 1-4 der Deadlock-Detektor ununterbrochen laufen und die Leistung beeinträchtigen könnte.
Um den Bericht über blockierte Prozesse zu verwenden, müssen Sie einen Trace konfigurieren, der unter „Errors and Warnings“ nach dem Ereignis „Blocked Process Report“ sucht. Wir empfehlen, einen serverseitigen Trace zu verwenden – und diesen natürlich nur dann auszuführen, wenn Sie ihn benötigen.
Wollen Sie eine Demo dazu sehen? Schauen Sie sich hier eine an.
Die Skripte in diesem Abschnitt sind Beispiele für gängige Methoden zur Durchführung dieser Aufgaben – Sie müssen sie entsprechend für Ihre Umgebung testen und überprüfen und auf eigenes Risiko ausführen.
Schritt 1: Aktivieren Sie den Bericht über blockierte Prozesse. Damit wird nach allen blockierten Prozessen gesucht, die 20 Sekunden oder länger dauern.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
–Stellen Sie sicher, dass Sie keine ausstehenden Änderungen haben
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
exec sp_configure ’show advanced options‘, 1;
GO
RECONFIGURE
GO
exec sp_configure ‚blocked process threshold (s)‘, 20;
GO
RECONFIGURE
GO
|
Schritt 2: Richten Sie einen Trace ein, um den Bericht über den blockierten Prozess zu erfassen. Führen Sie es als serverseitigen Trace aus.
- Es sollte ein Ereignis enthalten: Errors and Warnings -> Blocked Process Report.
- Es braucht nur zwei Spalten: Text, spid
Dieses Beispiel für einen serverseitigen Trace wird fünf Minuten lang ausgeführt und dann automatisch beendet. Sie müssen den Pfad in ein gültiges Verzeichnis ändern, für das SQL Server Schreibrechte besitzt.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
— Erstellt von: SQL Server 2012 Profiler
— Erstellen einer Warteschlange
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
———Hier wurde eine Funktion hinzugefügt:
set @DateTime = DATEADD(mi,5,getdate()); /* Fünf Minuten laufen lassen */
set @maxfilesize = 5
— Bitte ersetzen Sie den Text InsertFileNameHere, durch einen entsprechenden
— Dateinamen mit vorangestelltem Pfad, z.g., c:\MyFolder\MyTrace. Die Erweiterung .trc
— wird automatisch an den Dateinamen angehängt. Wenn Sie von
— einem entfernten Server auf ein lokales Laufwerk schreiben, verwenden Sie bitte einen UNC-Pfad und stellen Sie sicher, dass der Server
— Schreibzugriff auf Ihre Netzwerkfreigabe hat
———–Set my filename here:
exec @rc = sp_trace_create @TraceID output, 0, N’S:\Traces\BlockedProcessReportDemo‘, @maxfilesize, @Datetime
if (@rc != 0) goto error
— Client side File and Table cannot be scripted
— Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
— Setzen der Filter
declare @intfilter int
declare @bigintfilter bigint
— Setzen des Trace-Status auf start
exec sp_trace_setstatus @TraceID, 1
— Trace-ID für zukünftige Referenzen anzeigen
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
|
Schritt 3: Schauen Sie sich die laufenden Traces an und ermitteln Sie die Trace-ID:
|
1
2
|
SELECT * from sys.Traces;
GO
|
Stop a trace, falls erforderlich
|
1
2
3
4
5
|
–Geben Sie die richtige Traceid aus der obigen Abfrage ein
EXEC sp_trace_setstatus @traceid =2, @status = 0;
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2; — Trace beenden (vorausgesetzt, es ist die Trace-ID 2)
GO
|
Schritt 4: Lesen Sie die Daten des Berichts über den blockierten Prozess.
In diesem Beispiel verwenden wir den kostenlosen Blocked Process Report Viewer von Michael J Swart.
|
1
2
3
|
exec dbo.sp_blocked_process_report_viewer
@Source=’S:\Traces\BlockedProcessReportDemo.trc‘;
GO
|
Schritt 5: Aufräumen!
Diesen Schritt nicht vergessen.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
–Stellen Sie sicher, dass Ihr Trace weg ist
SELECT * from sys.traces;
GO
–Schalten Sie den Bericht über blockierte Prozesse aus, wenn Sie ihn nicht verwenden
–Stellen Sie sicher, dass Sie keine ausstehenden Änderungen haben
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
exec sp_configure ‚blocked process threshold (s)‘, 0;
GO
RECONFIGURE
GO
exec sp_configure ‚blocked process threshold (s)‘;
GO
|
