SQL Server houdt van locks.
SQL Server gebruikt standaard “pessimistic” locking- uw queries zijn van nature defensief. Dit kan leiden tot blokkeringsproblemen die u ervan weerhouden SQL Server te schalen.
Gratis videotraining – voordat u blokkeringen en deadlocks gaat oplossen, begint u met How to Think Like the Engine. Het is een snelle 90 minuten durende cursus die uitlegt hoe geclusterde en niet-geclusterde index internals werken, en dat is van vitaal belang om te begrijpen hoe indexering blokkering beïnvloedt. Het is gratis – omdat wij geloven dat iedere SQL Server professional hier zijn training moet beginnen. Het is echt zo belangrijk – goed spul.
Tools to Diagnose Lock Contention
“Do I have lock waits right now?” Gebruik sp_BlitzFirst® om vast te stellen of uw SQL Server momenteel last heeft van “lock waits”.
“Welke tabellen hebben de grootste blokkeringsproblemen?” Voer sp_BlitzIndex® uit en zoek naar “Aggressive Index Disorder” om vast te stellen welke tabellen grote hoeveelheden lock waits hebben gehad (voor tabellen en indexen die zich momenteel in het geheugen bevinden).
“Hoe maak ik gebruik van het Blocked Process Report?” Wanneer u het SQL Server Blocked Process Report (scripts hieronder) vastlegt, is Michael J Swart’s Blocked Process Report Viewer een gratis tool die u helpt de resultaten te interpreteren.
“Wat als mijn applicatie deadlock fouten niet opvangt?” Amit Banerjee van Microsoft’s CSS team helpt u bij het opvragen van SQL Server’s uitgebreide events System Health sessie voor details (SQL Server 2008+).
Lock Related Performance Counters
Deze performance counters kunnen zeer nuttig zijn om een op perf counter gebaseerde SQL Server Agent Alert te configureren, zodat SQL Server u op de hoogte kan stellen wanneer blokkering drempels passeert die u hebt ingesteld.
- Deze worden eerst OBJECT genoemd, dan COUNTER
- SQL Server: Algemene Statistieken – Geblokkeerde Processen
- SQL Server: Locks – Lock Wachttijd (ms)
- SQL Server: Locks – Number of Deadlocks/sec
Lees hier meer over perf counters in SQL Server.
Lees artikelen over Locking, Isolation Levels, en Deadlocks
Lees een introductie tot two-phase locking en deadlocks van Jeremiah.
Leer van Kendra hoe u codewijzigingen kunt testen om erachter te komen welke locks nodig zijn.
Jeremiah leert u hoe u deadlocks kunt vinden en voorkomen.
Leer van Kendra hoe u optimistic locking in SQL Server kunt implementeren.
Lees meer over isolatieniveaus in SQL Server – bekijk onze pagina met uitgebreide links en referenties.
Scripts om het Blocked Process Report te gebruiken
Het “Blocked Process Report” bestaat al een lange tijd in SQL Server- en het is nog steeds een nuttig hulpmiddel. Standaard in SQL Server wordt de deadlock detector elke 5 seconden wakker om te controleren of uw queries in een “dodelijke omhelzing” zitten. U kunt een optie aanzetten die SQL Server vraagt om deze bron te gebruiken om te controleren op langdurige blokkering, en een rapport uit te brengen.
WAARSCHUWING: U moet het geblokkeerde proces rapport alleen configureren om te worden uitgegeven voor waarden van vijf seconden of hoger. Microsoft waarschuwt dat als u dit instelt van 1-4, u de deadlock-detector continu zou kunnen laten draaien en uw prestaties om zeep zou kunnen helpen.
Om het Blocked Process Report te gebruiken, moet u een trace configureren die zoekt naar de “Blocked Process Report”-gebeurtenis onder “Errors and Warnings” (Fouten en waarschuwingen). Wij raden aan een server side trace te gebruiken – en deze natuurlijk alleen uit te voeren wanneer je hem nodig hebt.
Wil je hier een demo van zien? Bekijk er hier een.
De scripts in deze sectie zijn voorbeelden van veel voorkomende manieren om deze taken uit te voeren- u moet deze testen en beoordelen voor uw omgeving, en op eigen risico uitvoeren.
Stap 1: Zet het geblokkeerde proces rapport aan. Dit zal kijken voor elke blokkering duurt 20 seconden of langer.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
–Zorg ervoor dat u geen hangende wijzigingen hebt
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
exec sp_configure ’toon geavanceerde opties’, 1;
GO
RECONFIGURE
GO
exec sp_configure ‘blocked process threshold (s)’, 20;
GO
RECONFIGURE
GO
|
Step 2: Stel een trace op om het geblokkeerde procesverslag vast te leggen. Voer het uit als een server side trace.
- Het moet één event bevatten: Errors and Warnings -> Blocked Process Report.
- Het heeft maar twee kolommen nodig: Tekst, spid
Dit voorbeeld server side trace zal gedurende vijf minuten lopen en automatisch stoppen. U moet het pad veranderen naar een geldige directory waar SQL Server naar mag schrijven.
|
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
|
— Gemaakt door: SQL Server 2012 Profiler
– Maak een wachtrij
declareer @rc int
declareer @TraceID int
declareer @maxfilesize bigint
declareer @DateTime datetime
———Hier wordt een functie toegevoegd:
set @DateTime = DATEADD(mi,5,getdate()); /* Vijf minuten laten lopen */
set @maxfilesize = 5
–Vervang de tekst InsertFileNameHere, door een geschikte
–bestandsnaam voorafgegaan door een pad, bijv.g., c:mijn-map mijn-trace. De .trc extensie
— zal automatisch aan de bestandsnaam worden toegevoegd. Als u schrijft van een
– externe server naar een lokale schijf, gebruik dan UNC-pad en zorg ervoor dat de server
–schrijftoegang heeft tot uw netwerkshare
———–Set mijn bestandsnaam hier:
exec @rc = sp_trace_create @TraceID output, 0, N’S:\TracesBlockedProcessReportDemo’, @maxfilesize, @Datetime
if (@rc != 0) goto error
— Client side File and Table cannot be scripted
— Set the events
declareer @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
–Stel de Filters in
declareer @intfilter int
declareer @bigintfilter bigint
–Stel de trace status in op start
exec sp_trace_setstatus @TraceID, 1
— trace id weergeven voor toekomstige verwijzingen
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
ga
|
Stap 3: Kijk naar lopende traces en verkrijg het trace id:
|
1
2
|
SELECT * from sys.traces;
GO
|
Stop een tracering, indien nodig
|
1
2
3
4
5
|
–Plug het juiste traceid in uit de query hierboven
EXEC sp_trace_setstatus @traceid =2, @status =0; — stop trace (ervan uitgaande dat het trace ID 2 is)
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2; — sluit trace (ervan uitgaande dat het trace ID 2 is)
GO
|
Step 4: Lees de gegevens van het geblokkeerde procesrapport.
In dit voorbeeld gebruiken we de gratis viewer voor geblokkeerde procesrapporten van Michael J Swart.
|
1
2
3
|
exec dbo.sp_blocked_process_report_viewer
@Source=’S:\TracesBlockedProcessReportDemo.trc’;
GO
|
Stap 5: Opschonen!
Vergeet deze stap niet.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
–Zorg ervoor dat je spoor weg is
SELECT * from sys.traces;
GO
–Zorg ervoor dat het rapport over geblokkeerde processen wordt uitgeschakeld als u het niet gebruikt.
–Zorg ervoor dat u geen wijzigingen in behandeling hebt
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
|
