SQL Server älskar låsningar.
SQL Server använder ”pessimistisk” låsning som standard – dina frågor är naturligtvis defensiva. Detta kan leda till blockeringsproblem som hindrar dig från att skala SQL Server.
Gratis videokurs – innan du löser problem med blockering och låsningar kan du börja med How to Think Like the Engine. Det är en snabb 90-minuterskurs som förklarar hur klustrade och icke-klustrade indexinterna funktioner fungerar, och det är viktigt för att förstå hur indexering påverkar blockeringen. Det är gratis – eftersom vi anser att alla SQL Server-proffs bör börja sin utbildning här. Det är verkligen så viktigt – bra grejer.
Verktyg för att diagnostisera Lock Contention
”Do I have lock waits right now?” Använd sp_BlitzFirst® för att diagnostisera om låsningar för närvarande påverkar din SQL Server.
”Vilka tabeller har de värsta problemen med blockering?” Kör sp_BlitzIndex® och leta efter ”Aggressive Index Disorder” för att identifiera vilka tabeller som har haft stora mängder låsningar (för tabeller och index som för närvarande finns i minnet).
”Hur kan jag förstå rapporten Blocked Process Report?” När du fångar SQL Server Blocked Process Report (skript nedan) är Michael J Swarts Blocked Process Report Viewer ett gratis verktyg som hjälper dig att tolka resultaten.
”Vad händer om mitt program inte fångar upp deadlockfel?” Amit Banerjee från Microsofts CSS-team hjälper dig att fråga SQL Servers utökade händelser System Health-session för detaljer (SQL Server 2008+).
Lockrelaterade prestandacräknare
Dessa prestandacräknare kan vara mycket användbara för att konfigurera en perf counter-baserad SQL Server Agent Alert så att SQL Server kan meddela dig när blockeringen passerar de tröskelvärden som du har ställt in.
- De här listas OBJEKT först, sedan COUNTER
- SQL Server: General Statistics – Processes Blocked
- SQL Server: General Statistics – Processes Blocked
- SQL Server: Locks – Lock Wait Time (ms)
- SQL Server: Lås – Antal låsningar/sek
Lär dig mer om perfräknare i SQL Server här.
Läs artiklar om låsning, isoleringsnivåer och låsningar
Läs en introduktion till låsning i två faser och låsningar från Jeremiah.
Lär dig hur du kan testa kodändringar för att ta reda på vilka låsningar som krävs av Kendra.
Jeremiah lär dig hur du hittar och förhindrar låsningar.
Lär dig av Kendra hur du implementerar optimistisk låsning i SQL Server.
Läs mer om isoleringsnivåer i SQL Server – kolla in vår sida med omfattande länkar och referenser.
Skripter för att använda Blocked Process Report
Den ”Blocked Process Report” har funnits länge i SQL Server- och det är fortfarande ett användbart verktyg. Som standard i SQL Server vaknar deadlockdetektorn var 5:e sekund för att kontrollera om dina frågor befinner sig i en ”dödlig omfamning”. Du kan aktivera ett alternativ som ber SQL Server att använda den här resursen för att kontrollera långtidsblockering och utfärda en rapport.
VARNING: Du bör endast konfigurera rapporten om blockerad process så att den utfärdas för värden på fem sekunder eller högre. Microsoft varnar för att om du ställer in detta från 1-4, kan du orsaka att dödblocksdetektorn körs kontinuerligt och dödar din prestanda.
För att använda rapporten om blockerad process måste du konfigurera en spårning som letar efter händelsen ”Blocked Process Report” (rapport om blockerad process) under ”Errors and Warnings” (fel och varningar). Vi rekommenderar att du använder en spårning på serversidan – och naturligtvis bara kör den när du behöver den.
Vill du se en demo av detta? Se en här.
Skripten i det här avsnittet är exempel på vanliga sätt att utföra dessa uppgifter – du måste testa och granska dem för din miljö och köra på egen risk.
Steg 1: Aktivera rapporten om blockerad process. Detta kommer att leta efter alla blockeringar som tar 20 sekunder eller längre.
|
1
2
3
4
5
6
7
8
8
. 9
10
11
12
13
14
15
|
–Kontrollera att du inte har några pågående ändringar
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
|
Steg 2: Sätt upp en spårning för att fånga upp rapporten om den blockerade processen. Kör den som en spårning på serversidan.
- Den ska innehålla en händelse: Den ska innehålla en enda händelse: Errors and Warnings -> Blocked Process Report.
- Den behöver bara två kolumner: Text, spid
Detta exempel på spårning på serversidan kommer att köras i fem minuter och stoppas automatiskt. Du måste ändra sökvägen till en giltig katalog som SQL Server har behörighet att skriva till.
|
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
|
— Skapad av: SQL Server 2012 Profiler
— Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
——— lagt till en funktion här:
set @DateTime = DATEADD(mi,5,getdate()); /* Kör i fem minuter */
set @maxfilesize = 5
— Ersätt texten InsertFileNameHere med ett lämpligt
— filnamn med en sökväg före, t.ex.g., c:\MyFolder\MyTrace. .trc-tillägget
— kommer automatiskt att läggas till filnamnet. Om du skriver från
— fjärrserver till en lokal enhet, använd UNC-sökväg och se till att servern har
— skrivbehörighet till din nätverksdelning
———–Sätt mitt filnamn här:
exec @rc = sp_trace_create @TraceID output, 0, N’S:\Traces\BlockedProcessReportDemo’, @maxfilesize, @Datetime
if (@rc != 0) goto error
— Klientsidan Fil och tabell kan inte scriptas
— Ställ in händelserna
deklarera @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
— Ställ in filtren
deklarera @intfilter int
deklarera @bigintfilter bigint
— Ställ in spårningsstatus till start
exec sp_trace_setstatus @TraceID, 1
— Visa spår-ID för framtida referenser
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
|
Steg 3: Titta på körda spår och få fram spår-id:
|
1
2
|
SELECT * from sys.traces;
GO
|
Stoppa ett spår, om det behövs
|
1
2
3
4
5
|
–Sätt in rätt traceid från frågan ovan
EXEC sp_trace_setstatus @traceid =2, @status = 0; — Stoppa spårningen (förutsatt att det är spår-ID 2)
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2; — Stäng spårningen (förutsatt att det är spår-ID 2)
GO
|
Steg 4: Läs av uppgifterna i rapporten om den blockerade processen.
I det här exemplet använder vi Michael J Swarts kostnadsfria visningsprogram för rapporter om blockerade processer.
|
1
2
3
|
exec dbo.sp_blocked_process_report_viewer
@Source=’S:\Traces\BlockedProcessReportDemo.trc’;
GO
|
Steg 5: Städa upp!
Glöm inte det här steget.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
–Säkerställ att ditt spår är borta
SELECT * from sys.traces;
GO
–Släck rapporten om blockerade processer när du inte använder den.
–Säkerställ att du inte har några pågående ändringar
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
exec sp_configure ’blocked process threshold (s)’, 0;
GO
RECONFIGURERA
GO
exec sp_configure ’blocked process threshold (s)’;
GO
|
