SQL Server uwielbia blokady.
SQL Server domyślnie używa „pesymistycznego” blokowania – Twoje zapytania są naturalnie defensywne. Może to prowadzić do problemów z blokadami, które uniemożliwiają skalowanie SQL Server.
Darmowa klasa szkoleniowa wideo – zanim zaczniesz rozwiązywać problemy z blokadami i deadlockami, zacznij od How to Think Like the Engine. Jest to 90-minutowy kurs w szybkim tempie, który wyjaśnia, jak działają wewnętrzne indeksy klastrowane i nieklastrowane, co jest niezbędne do zrozumienia, jak indeksowanie wpływa na blokowanie. Zajęcia są bezpłatne – ponieważ wierzymy, że każdy profesjonalista SQL Server powinien rozpocząć swoje szkolenie właśnie tutaj. To naprawdę jest tak ważne – dobre rzeczy.
Narzędzia do diagnozowania współzawodnictwa blokad
„Czy mam teraz oczekiwania na blokady?” Użyj sp_BlitzFirst®, aby zdiagnozować, czy oczekiwanie na blokady ma obecnie wpływ na Twój SQL Server.
„Które tabele mają najgorsze problemy z blokadami?” Uruchom sp_BlitzIndex® i poszukaj „Agresywnych zaburzeń indeksów”, aby zidentyfikować, które tabele miały duże ilości oczekiwania na blokady (dla tabel i indeksów aktualnie znajdujących się w pamięci).
„Jak nadać sens raportowi zablokowanych procesów?” Po przechwyceniu raportu zablokowanych procesów SQL Server (skrypty poniżej), Michael J Swart’s Blocked Process Report Viewer jest darmowym narzędziem, które pomaga w interpretacji wyników.
„Co jeśli moja aplikacja nie wyłapuje błędów deadlock?” Amit Banerjee z zespołu CSS firmy Microsoft pomaga odpytywać sesję System Health SQL Servera w zdarzeniach rozszerzonych w celu uzyskania szczegółowych informacji (SQL Server 2008+).
Lock Related Performance Counters
Te liczniki wydajności mogą być bardzo przydatne do skonfigurowania opartego na licznikach perf Alert SQL Server Agent, tak aby SQL Server mógł powiadomić Cię, gdy blokady przekroczą ustawione przez Ciebie progi.
- Są one wymienione najpierw OBJECT, a następnie COUNTER
- SQL Server: Statystyki ogólne – Procesy zablokowane
- SerwerSQL: Locks – Czas oczekiwania na blokadę (ms)
- SerwerSQL: Locks – Number of Deadlocks/sec
Dowiedz się więcej o licznikach perf w SQL Server tutaj.
Przeczytaj artykuły o blokowaniu, poziomach izolacji i deadlockach
Przeczytaj wprowadzenie do blokowania dwufazowego i deadlocków od Jeremiaha.
Dowiedz się od Kendry, jak możesz testować zmiany w kodzie, aby dowiedzieć się, jakie blokady są wymagane.
Jeremiasz uczy, jak znajdować i zapobiegać martwym blokadom.
Dowiedz się od Kendry, jak zaimplementować optymistyczne blokowanie w SQL Server.
Dowiedz się więcej o poziomach izolacji w SQL Server – sprawdź naszą stronę z wyczerpującymi odnośnikami i referencjami.
Skrypty do wykorzystania raportu o zablokowanych procesach
Raport o zablokowanych procesach” istnieje od dawna w SQL Server – i nadal jest przydatnym narzędziem. Domyślnie w SQL Server, detektor deadlock budzi się co 5 sekund, aby sprawdzić, czy twoje zapytania są w „śmiertelnym uścisku”. Możesz włączyć opcję, która prosi SQL Server o użycie tego zasobu do sprawdzenia długotrwałego blokowania i wydania raportu.
Ostrzeżenie: Powinieneś skonfigurować raport o zablokowanym procesie, aby był wydawany tylko dla wartości pięciu sekund lub wyższych. Microsoft ostrzega, że jeśli ustawisz to od 1 do 4, możesz spowodować ciągłe działanie detektora martwych punktów i obniżyć wydajność.
Aby użyć raportu o zablokowanych procesach, musisz skonfigurować śledzenie, które szuka zdarzenia „Raport o zablokowanym procesie” w sekcji „Błędy i ostrzeżenia”. Zalecamy używanie śledzenia po stronie serwera – i oczywiście uruchamianie go tylko wtedy, gdy jest potrzebne.
Chcesz zobaczyć demo tego rozwiązania? Obejrzyj je tutaj.
Skrypty w tej sekcji są próbkami typowych sposobów wykonywania tych zadań – musisz je przetestować i sprawdzić odpowiednio dla swojego środowiska, i uruchomić na własne ryzyko.
Krok 1: Włącz raport zablokowanych procesów. Pozwoli to na wyszukanie wszelkich blokad trwających 20 sekund lub dłużej.
|
1
2
3
4
5
6
7
. 8
9
10
11
12
13
14
15
|
–upewnij się, że nie masz żadnych oczekujących zmian
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
|
Krok 2: Skonfiguruj ślad, aby przechwycić raport o zablokowanym procesie. Uruchom go jako ślad po stronie serwera.
- Powinien on zawierać jedno zdarzenie: Errors and Warnings -> Blocked Process Report.
- Potrzebuje tylko dwóch kolumn: Text, spid
Ten przykładowy ślad po stronie serwera będzie działał przez pięć minut i automatycznie się zatrzyma. Musisz zmienić ścieżkę do poprawnego katalogu, do którego SQL Server ma uprawnienia do zapisu.
|
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
|
— Utworzone przez: SQL Server 2012 Profiler
— Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
——— Dodano tutaj funkcję:
set @DateTime = DATEADD(mi,5,getdate()); /* Uruchom na pięć minut */
set @maxfilesize = 5
— Proszę zastąpić tekst InsertFileNameHere, odpowiednią
— nazwą pliku poprzedzoną ścieżką, np.g., c:\MyFolder\MyTrace. Rozszerzenie .trc
— zostanie dołączone do nazwy pliku automatycznie. Jeśli zapisujesz z
— zdalnego serwera na dysk lokalny, użyj ścieżki UNC i upewnij się, że serwer ma
— dostęp do zapisu w twoim udziale sieciowym
———–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
— Ustaw filtry
declare @intfilter int
declare @bigintfilter bigint
— Ustaw status śledzenia na start
exec sp_trace_setstatus @TraceID, 1
— wyświetl identyfikator śladu dla przyszłych odniesień
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
|
Krok 3: Spójrz na uruchomione ślady i uzyskaj id śladu:
|
1
2
|
SELECT * from sys.traces;
GO
|
Zatrzymaj ślad, w razie potrzeby
|
1
2
3
4
5
|
-.-Wprowadzić prawidłowy traceid z powyższego zapytania
EXEC sp_trace_setstatus @traceid =2, @status = 0; — zatrzymanie śladu (zakładając, że jest to trace ID 2)
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2; — zamknięcie śladu (zakładając, że jest to trace ID 2)
GO
|
Krok 4: Odczytanie danych raportu o zablokowanym procesie.
W tej próbce wykorzystujemy darmową przeglądarkę raportów zablokowanych procesów autorstwa Michaela J Swarta.
|
1
2
3
|
exec dbo.sp_blocked_process_report_viewer
@Source=’S:TracesBlockedProcessReportDemo.trc’;
GO
|
Krok 5: Wyczyść!
Nie zapomnij o tym kroku.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
–upewnij się, że twój ślad zniknął
SELECT * from sys.traces;
GO
–Wyłącz raport zablokowanych procesów, gdy go nie używasz.
–Upewnij się, że nie masz żadnych oczekujących zmian
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
exec sp_configure 'blocked process threshold (s)’, 0;
GO
RECONFIGURE
GO
exec sp_configure 'próg zablokowanego procesu (s)’;
GO
|
.
