SQL Server elsker låse.
SQL Server bruger “pessimistisk” låsning som standard – dine forespørgsler er naturligvis defensive. Dette kan føre til blokeringsproblemer, der forhindrer dig i at skalere SQL Server.
Gratis videotræningsklasse – før du løser problemer med blokering og deadlocks, skal du starte med How to Think Like the Engine. Det er et hurtigt 90-minutters kursus, der forklarer, hvordan internals i clustered og nonclustered indeks fungerer, og det er afgørende for at forstå, hvordan indeksering påvirker blokering. Det er gratis – fordi vi mener, at alle SQL Server-professionelle bør starte deres uddannelse her. Det er virkelig så vigtigt – godt stof.
Tools to Diagnose Lock Contention
“Do I have lock waits right now?” Brug sp_BlitzFirst® til at diagnosticere, om lock waits i øjeblikket påvirker din SQL Server.
“Hvilke tabeller har de værste blokeringsproblemer?” Kør sp_BlitzIndex® og kig efter “Aggressive Index Disorder” for at identificere, hvilke tabeller der har haft store mængder af lock waits (for tabeller og indekser, der i øjeblikket er i hukommelsen).
“Hvordan får jeg mening ud af Blocked Process Report?” Når du opfanger SQL Server Blocked Process Report (scripts nedenfor), er Michael J Swarts Blocked Process Report Viewer et gratis værktøj, der hjælper dig med at fortolke resultaterne.
“Hvad hvis min applikation ikke opfanger deadlockfejl?” Amit Banerjee fra Microsofts CSS-team hjælper dig med at forespørge SQL Server’s udvidede hændelser System Health-session for detaljer (SQL Server 2008+).
Lock-relaterede præstationstællere
Disse præstationstællere kan være meget nyttige til at konfigurere en perf-tællerbaseret SQL Server Agent Alert, så SQL Server kan give dig besked, når blokering passerer de tærskelværdier, du har indstillet.
- Disse er anført OBJECT først, derefter COUNTER
- SQL Server: General Statistics – Processes Blocked
- SQL Server: General Statistics – Processes Blocked
- SQL Server: Locks – Lock Wait Time (ms)
- SQL Server: Locks – Number of Deadlocks/sec
Læs om flere perf-tællere i SQL Server her.
Læs artikler om låsning, isoleringsniveauer og deadlocks
Læs en introduktion til tofaselåsning og deadlocks fra Jeremiah.
Læs fra Kendra, hvordan du kan teste kodeændringer for at finde ud af, hvilke låse der er nødvendige fra Kendra.
Jeremiah lærer dig, hvordan du finder og forebygger deadlocks.
Lær hvordan du implementerer optimistisk låsning i SQL Server fra Kendra.
Læs mere om isolationsniveauer i SQL Server – se vores side med omfattende links og referencer.
Skripter til brug af Blocked Process Report
Den “Blocked Process Report” har eksisteret længe i SQL Server- og det er stadig et nyttigt værktøj. Som standard i SQL Server vågner deadlock-detektoren hvert 5. sekund for at kontrollere, om dine forespørgsler er i en “dødbringende omfavnelse”. Du kan aktivere en indstilling, der beder SQL Server om at bruge denne ressource til at kontrollere for langvarig blokering og udstede en rapport.
VARNELSE: Du bør kun konfigurere rapporten om blokeret proces til at blive udstedt for værdier på fem sekunder eller højere. Microsoft advarer om, at hvis du indstiller dette fra 1-4, kan du få deadlockdetektoren til at køre kontinuerligt og dræbe din ydeevne.
For at bruge rapporten om blokeret proces skal du konfigurere en sporing, der leder efter hændelsen “Rapport om blokeret proces” under “Fejl og advarsler”. Vi anbefaler at bruge en sporing på serversiden – og selvfølgelig kun køre dette, når du har brug for det.
Vil du se en demo af dette? Se en her.
Skripter i dette afsnit er eksempler på almindelige måder at udføre disse opgaver på – du skal teste og gennemgå dem i overensstemmelse hermed for dit miljø og køre på egen risiko.
Skridt 1: Slå rapporten om blokeret proces til. Dette vil kigge efter enhver blokering, der tager 20 sekunder eller længere.
|
1
2
3
3
4
5
6
7
8
9
10
11
12
13
14
15
|
–Sørg for, at du ikke har nogen afventende ændringer
SELECT *
FROM sys.konfigurationer
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
GO
|
Stræk 2: Opsæt en sporing for at registrere rapporten om den blokerede proces. Kør den som en sporing på serversiden.
- Den skal indeholde én hændelse: Den skal indeholde en enkelt hændelse: Fejl og advarsler -> Rapport om blokeret proces.
- Den har kun brug for to kolonner: Text, spid
Denne eksempel på server side trace vil køre i fem minutter og automatisk stoppe. Du skal ændre stien til en gyldig mappe, som SQL Server har tilladelse til at skrive til.
|
1
2
3
3
4
5
6
7
8
9
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
|
— Oprettet af: SQL Server 2012 Profiler
— Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
——— Tilføjede en funktion her:
set @DateTime = DATEADD(mi,5,getdate())); /* Kør i fem minutter */
set @maxfilesize = 5
— Erstat venligst teksten InsertFileNameHere, med et passende
— filnavn med en sti foran, f.eks.g., c:\MyFolder\MyTrace. .trc-udvidelsen
— vil automatisk blive føjet til filnavnet. Hvis du skriver fra
— fjernserver til lokalt drev, skal du bruge UNC-sti og sikre dig, at serveren har
— skriveadgang til dit netværksaktie
———–Sæt mit filnavn her:
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, 1, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
— Indstil filtrene
declare @intfilter int
declare @bigintfilter bigint
— Indstil sporingsstatus til start
exec sp_trace_setstatus @TraceID, 1
— Vis trace id for fremtidige referencer
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
|
Stræk 3: Kig på kørende spor og få trace id:
|
1
2
|
SELECT * from sys.traces;
GO
|
Stop et spor, om nødvendigt
|
1
2
3
4
4
5
|
–Indsæt det korrekte traceid fra forespørgslen ovenfor
EXEC sp_trace_setstatus @traceid =2, @status = 0; — Stop trace (antager, at det er trace ID 2)
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2; — Luk trace (antager, at det er trace ID 2)
GO
|
Stræk 4: Læs dataene i rapporten om den blokerede proces.
I denne prøve bruger vi den gratis visning af blokeret procesrapport af Michael J Swart.
|
1
2
3
|
exec dbo.sp_blocked_process_report_viewer
@Source=’S:\Traces\BlockedProcessReportDemo.trc’;
GO
|
Stræk 5: Ryd op!
Glem ikke dette trin.
|
1
2
3
4
4
5
6
7
8
8
9
10
11
12
13
14
15
16
17
18
|
–Sørg for, at din sporing er væk
SELECT * from sys.traces;
GO
–Sluk for rapporten om blokerede processer, når du ikke bruger den.
–Sørg for, at du ikke har nogen afventende ændringer
SELECT *
FROM sys.konfigurationer
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
|
