SQL Server iubește blocajele.
SQL Server folosește în mod implicit blocarea „pesimistă” – interogările dvs. sunt în mod natural defensive. Acest lucru poate duce la probleme de blocare care vă împiedică să scalați SQL Server.
Curs de instruire video gratuit – înainte de a rezolva problema blocării și a blocajelor, începeți cu How to Think Like the Engine. Este un curs rapid de 90 de minute care explică modul în care funcționează internii de indexare grupată și neaglomerată, iar acest lucru este vital pentru a înțelege modul în care indexarea afectează blocarea. Este gratuit – pentru că noi credem că fiecare profesionist SQL Server ar trebui să-și înceapă formarea aici. Este într-adevăr atât de important – lucruri bune.
Tools to Diagnose Lock Contention
„Am așteptări de blocare chiar acum?” Utilizați sp_BlitzFirst® pentru a diagnostica dacă așteptările de blocare au în prezent un impact asupra SQL Server-ului dumneavoastră.
„Ce tabele au cele mai grave probleme de blocare?” Rulați sp_BlitzIndex® și căutați „Aggressive Index Disorder” pentru a identifica ce tabele au avut cantități mari de așteptări de blocare (pentru tabelele și indexurile aflate în prezent în memorie).
„Cum înțeleg Raportul proceselor blocate?” Atunci când capturați SQL Server Blocked Process Report (scripturile de mai jos), Blocked Process Report Viewer al lui Michael J Swart este un instrument gratuit care vă ajută să interpretați rezultatele.
„Ce se întâmplă dacă aplicația mea nu prinde erorile de blocaj?” Amit Banerjee de la echipa CSS a Microsoft vă ajută să interogați sesiunea System Health a evenimentelor extinse ale SQL Server pentru detalii (SQL Server 2008+).
Lock Related Performance Counters
Aceste contoare de performanță pot fi foarte utile pentru a configura o alertă a agentului SQL Server bazată pe un contor de perfomanță, astfel încât SQL Server să vă notifice atunci când blocajul depășește pragurile pe care le-ați setat.
- Acestea sunt enumerate mai întâi OBJECT, apoi COUNTER
- SQL Server: Statistici generale – Procese blocate
- SQL Server: Locks – Lock Wait Time (ms)
- SQL Server: Locks – Number of Deadlocks/sec
Învățați despre mai multe contoare de perfuzii în SQL Server aici.
Citiți articole despre blocare, niveluri de izolare și blocaje
Citiți o introducere la blocare în două faze și blocaje de la Jeremiah.
Învățați cum puteți testa modificările de cod pentru a afla ce blocaje sunt necesare de la Kendra.
Jeremiah vă învață cum să găsiți și să preveniți blocajele.
Învățați cum să implementați optimistic locking în SQL Server de la Kendra.
Cunoașteți mai multe despre nivelurile de izolare în SQL Server – consultați pagina noastră de linkuri și referințe cuprinzătoare.
Scripte pentru a utiliza Blocked Process Report
„Blocked Process Report” există de mult timp în SQL Server- și este încă un instrument util. În mod implicit în SQL Server, detectorul de blocaj se trezește la fiecare 5 secunde pentru a verifica dacă interogările dvs. se află într-o „îmbrățișare mortală”. Puteți activa o opțiune care cere SQL Server să folosească această resursă pentru a verifica dacă există blocaje pe termen lung și să emită un raport.
AVERTISMENT: Ar trebui să configurați raportul de proces blocat pentru a fi emis numai pentru valori de cinci secunde sau mai mari. Microsoft avertizează că, dacă setați acest lucru de la 1-4, ați putea face ca detectorul de blocaj să ruleze continuu și să vă ucidă performanța.
Pentru a utiliza raportul privind procesele blocate, trebuie să configurați o urmărire care să caute evenimentul „Blocked Process Report” (Raport privind procesele blocate) sub „Errors and Warnings” (Erori și avertismente). Vă recomandăm să folosiți o urmărire pe partea serverului – și, bineînțeles, să rulați acest lucru doar atunci când aveți nevoie de el.
Vreți să vedeți o demonstrație a acestui lucru? Urmăriți unul aici.
Scriptele din această secțiune sunt mostre de moduri obișnuite de a face aceste sarcini- trebuie să testați și să revizuiți în mod corespunzător pentru mediul dvs. și să le executați pe propriul risc.
Pasul 1: Activați raportul de proces blocat. Acest lucru va căuta orice blocaj care durează 20 de secunde sau mai mult.
|
1
2
3
4
5
6
7
7
. 8
9
10
11
12
13
14
15
|
-Asigură-te că nu ai nicio modificare în așteptare
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
GO
RECONFIGURE
GO
|
Etapa 2: Configurați o urmărire pentru a captura raportul procesului blocat. Rulați-l ca o urmărire de partea serverului.
- Ar trebui să conțină un eveniment: Errors and Warnings -> Blocked Process Report.
- Este nevoie doar de două coloane: Text, spid
Această mostră de urmărire pe server va funcționa timp de cinci minute și se va opri automat. Trebuie să modificați calea către un director valid în care SQL Server are permisiunea de a scrie.
|
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
|
— Creat de: SQL Server 2012 Profiler
— Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
———A adăugat o funcție aici:
set @DateTime = DATEADD(mi,5,getdate()); /* Se execută timp de cinci minute */
set @maxfilesize = 5
— Vă rugăm să înlocuiți textul InsertFileNameHere, cu un nume de fișier adecvat
— prefixat de o cale, de ex.g., c:\MyFolder\MyTrace. Extensia .trc
— va fi adăugată automat la numele de fișier. Dacă scrieți de pe
— serverul de la distanță pe unitatea locală, vă rugăm să folosiți calea UNC și să vă asigurați că serverul are
—- acces de scriere la partajarea în rețea
———–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
— Set the Filters
declare @intfilter int
declare @bigintfilter bigint
— Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
— afișează ID-ul de urmărire pentru referințe viitoare
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
|
Pasul 3: Uitați-vă la urmele care rulează și obțineți ID-ul urmei:
|
1
2
|
SELECT * from sys.traces;
GO
|
Încetează un traseu, dacă este necesar
|
1
2
3
4
5
|
-.-Introduceți traceidul corect din interogarea de mai sus
EXEC sp_trace_setstatus @traceid =2, @status = 0; — opriți urmărirea (presupunând că este trace ID 2)
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2; — închideți urmărirea (presupunând că este trace ID 2)
GO
|
Pasul 4: Citiți datele din raportul procesului blocat.
În acest eșantion folosim vizualizatorul gratuit de rapoarte de procese blocate al lui Michael J Swart.
|
1
2
3
|
exec dbo.sp_blocked_process_report_viewer
@Source=’S:\Traces\BlockedProcessReportDemo.trc’;
GO
|
Etapa 5: Curățați!
Nu uitați această etapă.
|
1
2
3
4
5
6
7
8
8
7
8
. 9
10
11
12
13
14
15
16
17
18
|
–asigurați-vă că urmele au dispărut
SELECT * from sys.traces;
GO
–Închideți raportul de procese blocate atunci când nu îl folosiți.
–Să vă asigurați că nu aveți nicio modificare în așteptare
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
|
.
