A SQL Server szereti a zárolásokat.
A SQL Server alapértelmezés szerint “pesszimista” zárolást használ – a lekérdezések természetesen védekeznek. Ez blokkolási problémákhoz vezethet, amelyek megakadályozzák az SQL Server skálázását.
Ingyenes videós tanfolyam – a blokkolás és a holtpontok hibaelhárítása előtt kezdje a Hogyan gondolkodjunk úgy, mint a motor? Ez egy gyors tempójú, 90 perces tanfolyam, amely elmagyarázza, hogyan működnek a fürtözött és a nem fürtözött indexek belső részei, és ez létfontosságú annak megértéséhez, hogy az indexelés hogyan befolyásolja a blokkolást. Ingyenes – mert úgy gondoljuk, hogy minden SQL Server szakembernek itt kellene kezdenie a képzést. Ez tényleg ennyire fontos – jó anyag.
Szerszámok a zárolási viták diagnosztizálásához
“Vannak-e most zárolási várakozások?” Az sp_BlitzFirst® segítségével diagnosztizálhatja, hogy a zárolási várakozások jelenleg hatással vannak-e az SQL Serverére.
“Mely tábláknál vannak a legsúlyosabb zárolási problémák?” Futtassa az sp_BlitzIndex® programot, és keresse az “agresszív indexzavarokat”, hogy azonosítsa, mely tábláknál volt nagy mennyiségű zárolási várakozás (a jelenleg memóriában lévő táblák és indexek esetében).
“Hogyan értelmezzem a blokkolt folyamatok jelentését?” Ha rögzíti az SQL Server blokkolt folyamatok jelentését (az alábbi szkriptek), Michael J Swart Blocked Process Report Viewer nevű ingyenes eszköze segít az eredmények értelmezésében.
“Mi van, ha az alkalmazásom nem észleli a holtponti hibákat?” Amit Banerjee a Microsoft CSS csapatából segít lekérdezni az SQL Server kiterjesztett események System Health munkamenetét a részletekért (SQL Server 2008+).
Lock Related Performance Counters
Ezek a teljesítményszámlálók nagyon hasznosak lehetnek egy perf számláló alapú SQL Server Agent Alert konfigurálásához, hogy az SQL Server értesíteni tudja Önt, ha a blokkolás átlépi az Ön által beállított küszöbértékeket.
- Ezek először OBJECT, majd COUNTER
- SQL Server: Általános statisztikák – Blokkolt folyamatok
- SQL Server: Locks – Lock Wait Time (ms)
- SQL Server: Locks – Lock Wait Time (ms)
- SQL Server: Locks – Number of Deadlocks/sec
Tudjon meg többet az SQL Server perf számlálóiról itt.
Read Articles about Locking, Isolation Levels, and Deadlocks
Read an introduction to two-phase locking and deadlocks from Jeremiah.
Tudja meg Kendrától, hogyan tesztelheti a kódváltozásokat, hogy megtudja, milyen zárakra van szükség.
Jeremiah megtanítja, hogyan találja meg és előzze meg a holtpontokat.
Tanulja meg Kendrától, hogyan valósíthatja meg az optimista zárolást az SQL Serverben.
Többet megtudhat az SQL Server izolációs szintjeiről – tekintse meg az átfogó linkeket és hivatkozásokat tartalmazó oldalunkat.
Skriptek a blokkolt folyamatjelentés használatához
A “blokkolt folyamatjelentés” már régóta létezik az SQL Serverben – és még mindig hasznos eszköz. Alapértelmezés szerint az SQL Serverben a holtpontérzékelő 5 másodpercenként felébred, hogy ellenőrizze, hogy a lekérdezések “holtpontos ölelésben” vannak-e. Bekapcsolhat egy olyan opciót, amely arra kéri az SQL Servert, hogy használja ezt az erőforrást a hosszú távú blokkolás ellenőrzésére, és adjon ki jelentést.
FIGYELMEZTETÉS: A blokkolt folyamatok jelentését csak öt másodperces vagy annál nagyobb értékek esetén kell beállítani. A Microsoft arra figyelmeztet, hogy ha ezt 1-4-től állítja be, akkor a holtpontérzékelő folyamatos futását okozhatja, és megölheti a teljesítményt.
A blokkolt folyamatjelentés használatához olyan nyomkövetést kell konfigurálnia, amely a “Hibák és figyelmeztetések” alatt keresi a “Blokkolt folyamatjelentés” eseményt. Javasoljuk, hogy szerveroldali nyomkövetést használjon – és természetesen csak akkor futtassa, amikor szüksége van rá.
Megnézne egy demót erről? Nézzen meg egyet itt.
Az ebben a részben található szkriptek a feladatok elvégzésének általános módjainak mintái – a saját környezetének megfelelően kell tesztelnie és felülvizsgálnia, és saját felelősségére kell futtatnia.
1. lépés: Kapcsolja be a blokkolt folyamatok jelentését. Ez minden olyan blokkolást keres, amely 20 másodpercet vagy annál hosszabb időt vesz igénybe.
|
1
2
3
4
5
6
7
. 8
9
10
11
12
13
14
15
|
–Győződjön meg róla, hogy nincsenek függőben lévő módosítások
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
|
2. lépés: Állítson be egy nyomkövetést a blokkolt folyamatjelentés rögzítésére. Futtassa azt szerveroldali nyomkövetésként.
- Egy eseményt kell tartalmaznia: Hibák és figyelmeztetések -> Blokkolt folyamat jelentés.
- Csak két oszlopra van szüksége:
Ez a minta szerveroldali nyomkövetés öt percig fog futni, majd automatikusan leáll. Az elérési utat olyan érvényes könyvtárra kell módosítani, amelybe az SQL Server írási jogosultsággal rendelkezik.
|
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
|
— Létrehozta: SQL Server 2012 Profiler
— Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
———Added a function here:
set @DateTime = DATEADD(mi,5,getdate()); /* Futtatás öt percig */
set @maxfilesize = 5
— Please replace the text InsertFileNameHere, with an appropriate
— filename prefixed by a path, e.g., c:\MyFolder\MyTrace. A .trc kiterjesztés
— automatikusan hozzá lesz csatolva a fájlnévhez. Ha
— távoli szerverről ír a helyi meghajtóra, használjon UNC elérési utat, és győződjön meg róla, hogy a szerver rendelkezik
— írási hozzáféréssel a hálózati megosztáshoz
———–A fájlnevemet itt állítsa be:
exec @rc = sp_trace_create @TraceID output, 0, N’S:\Traces\BlockedProcessReportDemo’, @maxfilesize, @Datetime
if (@rc != 0) goto error
— A kliensoldali fájl és táblázat nem szkriptelhető
— Az események beállítása
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
— A szűrők beállítása
declare @intfilter int
declare @bigintfilter bigint
— A nyomkövetés állapotának beállítása start
exec sp_trace_setstatus @TraceID, 1
— nyomkövetési azonosító megjelenítése a későbbi hivatkozásokhoz
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
|
3. lépés: Nézd meg a futó nyomvonalakat és szerezd meg a nyomvonal azonosítóját:
|
1
2
|
SELECT * from sys.traces;
GO
|
Egy nyomkövetés leállítása, ha szükséges
|
1
2
3
4
5
|
–A fenti lekérdezésből származó helyes traceid beillesztése
EXEC sp_trace_setstatus @traceid =2, @status = 0; — a nyomkövetés leállítása (feltételezve, hogy ez a nyomkövetési azonosító 2)
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2; — a nyomkövetés lezárása (feltételezve, hogy ez a nyomkövetési azonosító 2)
GO
|
4. lépés: Olvassa be a blokkolt folyamatjelentés adatait.
Ebben a mintában a Michael J Swart által készített ingyenes blokkolt folyamatjelentést megjelenítő programot használjuk.
|
1
2
3
|
exec dbo.sp_blocked_process_report_viewer
@Source=’S:\Traces\BlockedProcessReportDemo.trc’;
GO
|
5. lépés: Tisztítás!
Ne felejtsük el ezt a lépést!
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
–Győződjön meg róla, hogy a nyomvonal eltűnt
SELECT * from sys.traces;
GO
–Kikapcsolja a blokkolt folyamatok jelentését, ha nem használja.
–Győződjön meg róla, hogy nincsenek függőben lévő módosítások
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
|
.
