SQL Server rakastaa lukituksia.
SQL Server käyttää oletusarvoisesti ”pessimististä” lukitusta – kyselyt ovat luonnollisesti puolustavia. Tämä voi johtaa esto-ongelmiin, jotka estävät SQL Serverin skaalautumisen.
Vapaa videokoulutuskurssi – ennen estojen ja umpisolmujen vianmääritystä aloita How to Think Like the Engine. Se on nopeatempoinen 90 minuutin kurssi, jossa selitetään, miten klusteroidut ja ei-klusteroidut indeksin sisäiset toiminnot toimivat, ja se on elintärkeää sen ymmärtämiseksi, miten indeksointi vaikuttaa estämiseen. Se on ilmainen – koska uskomme, että jokaisen SQL Server -ammattilaisen pitäisi aloittaa koulutuksensa tästä. Se on todella niin tärkeää – hyvää tavaraa.
Työkalut lukituskiistojen diagnosointiin
”Onko minulla lukitusodotuksia juuri nyt?” Diagnosoi sp_BlitzFirst®:n avulla, vaikuttavatko lukko-odotukset tällä hetkellä SQL-palvelimeesi.
”Missä taulukoissa on pahimmat esto-ongelmat?” Suorita sp_BlitzIndex® ja etsi ”Aggressive Index Disorder” (Aggressiivinen indeksihäiriö) selvittääksesi, missä taulukoissa on ollut paljon lukitusodotuksia (tällä hetkellä muistissa olevissa taulukoissa ja indekseissä).
”Miten Blocked Process Report (Estyneiden prosessien raportti) on ymmärrettävissä?” Kun kaappaat SQL Server Blocked Process Report -raportin (skriptit alla), Michael J Swartin Blocked Process Report Viewer on ilmainen työkalu, joka auttaa tulkitsemaan tuloksia.
”Entä jos sovellukseni ei havaitse lukkiutumisvirheitä?” Amit Banerjee Microsoftin CSS-tiimistä auttaa sinua kysymään SQL Serverin laajennettujen tapahtumien System Health -istunnon yksityiskohtia (SQL Server 2008+).
Lockiin liittyvät suorituskykylaskurit
Nämä suorituskykylaskurit voivat olla erittäin hyödyllisiä perf-laskuriin perustuvan SQL Server Agent -hälytyksen konfiguroinnissa niin, että SQL Server voi ilmoittaa, kun lukkiutumiset ohittavat asettamasi raja-arvot.
- Neuvot luetellaan OBJECT-listalla ensimmäisenä, ja vasta sen jälkeen COUNTER-listalla
- SQL Server: General Statistics – Processes Blocked
- SQL Server: Locks – Lock Wait Time (ms)
- SQL Server: Locks – Number of Deadlocks/sec
Lue lisää SQL Serverin perf-laskureita täältä.
Lue artikkeleita lukituksesta, eristystasoista ja umpisuluista
Lue Jeremian johdanto kaksivaiheiseen lukitukseen ja umpisuluihin.
Opi Kendralta, miten voit testata koodimuutoksia saadaksesi selville, mitä lukituksia tarvitaan.
Jeremiah opettaa, miten umpikujia voidaan löytää ja estää.
Oppaa Kendralta, miten toteutat optimistisen lukituksen SQL Serverissä.
Lue lisää SQL Serverin eristystasoista – tutustu kattavien linkkien ja viitteiden sivuun.
Skriptit, joilla voit käyttää estettyjen prosessien raporttia
”Estettyjen prosessien raportti” on ollut käytössä SQL Serverissä jo kauan – ja se on edelleen hyödyllinen työkalu. Oletusarvoisesti SQL Serverissä umpikujailmaisin herää 5 sekunnin välein tarkistamaan, ovatko kyselyt ”umpikujassa”. Voit ottaa käyttöön vaihtoehdon, joka pyytää SQL Serveriä käyttämään tätä resurssia pitkäkestoisen lukkiutumisen tarkistamiseen ja raportin antamiseen.
VAROITUS: Sinun tulisi määrittää lukkiutuneen prosessin raportti annettavaksi vain viiden sekunnin tai sitä suuremmista arvoista. Microsoft varoittaa, että jos asetat tämän arvosta 1-4, saatat aiheuttaa sen, että lukkiutumisen tunnistin toimii jatkuvasti ja tappaa suorituskyvyn.
Käyttääksesi estetyn prosessin raporttia sinun on määritettävä jäljitys, joka etsii ”Virheet ja varoitukset” -kohdasta ”Estetyn prosessin raportti” -tapahtumaa. Suosittelemme käyttämään palvelinpuolen jäljitystä – ja tietysti ajamaan sen vain silloin, kun tarvitset sitä.
Haluatko nähdä demon tästä? Katso sellainen täältä.
Tässä osiossa olevat skriptit ovat esimerkkejä yleisistä tavoista tehdä nämä tehtävät – sinun on testattava ja tarkistettava ne vastaavasti omaan ympäristöösi ja suoritettava omalla vastuullasi.
Vaihe 1: Ota estetyn prosessin raportti käyttöön. Tämä etsii kaikki 20 sekuntia tai pidempään kestävät estot.
|
1
2
3
4
5
6
7
-Varmista, ettei sinulla ole vireillä olevia muutoksia 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
|
GO RECONFIGURE
Vaihe2: Määritä jäljitys estetyn prosessiraportin kaappaamiseksi. Suorita se palvelinpuolen jäljityksenä.
- Sen tulisi sisältää yksi tapahtuma: Errors and Warnings -> Blocked Process Report.
- Se tarvitsee vain kaksi saraketta:
Tämä esimerkki palvelinpuolen jäljityksestä toimii viisi minuuttia ja pysähtyy automaattisesti. Polku on muutettava kelvolliseen hakemistoon, johon SQL Serverillä on oikeus kirjoittaa.
|
1
2
3
4
5
5
6
7
8
9
23
24
25
26
27
28
29
30
31
32
33
34
35
– Luonut: SQL Server 2012 Profiler — Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
———Lisättiin funktio tähän:
set @DateTime = DATEADD(mi,5,getdate()); /* Suorita viiden minuutin ajan */
set @maxfilesize = 5
— Ole hyvä ja korvaa teksti InsertFileNameHere, sopivalla
— tiedostonimellä, jonka edessä on polku, esim.g., c:\MyFolder\MyTrace. .trc-pääte
— liitetään tiedostonimeen automaattisesti. Jos kirjoitat
— etäpalvelimelta paikalliselle asemalle, käytä UNC-polkua ja varmista, että palvelimella on
— kirjoitusoikeus verkkojakajaan
———–Set my filename here:
exec @rc = sp_trace_create @TraceID output, 0, N’S:\Traces\BlockedProcessReportDemo’, @maxfilesize, @Datetime
if (@rc != 0) goto error
— Asiakaspuolen tiedostoa ja taulukkoa ei voi skriptata
— Aseta tapahtumat
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
— Aseta suodattimet
declare @intfilter int
declare @bigintfilter bigint
— Aseta jäljityksen tilaksi start
exec sp_trace_setstatus @TraceID, 1
— näytä jäljen id tulevia viittauksia varten
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
|
Vaihe 3: Katso käynnissä olevia jälkiä ja hae jäljen id:
|
1
2
|
SELECT * from sys.traces;
GO
|
Pysäyttää jäljen, tarvittaessa
|
1
2
3
4
4
5
|
–Kytke oikea traceid yllä olevasta kyselystä
EXEC sp_trace_setstatus @traceid =2, @status = 0; — pysäytä jälki (olettaen, että se on jälkitunniste 2)
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2; — sulje jälki (olettaen, että se on jälkitunniste 2)
GO
|
Vaihe 4: Lue estetyn prosessin raporttitiedot.
Tässä esimerkissä käytämme Michael J Swartin ilmaista blocked process report viewer -ohjelmaa.
|
1
2
3
|
exec dbo.sp_blocked_process_report_viewer
@Source=’S:\Traces\BlockedProcessReportDemo.trc’;
GO
|
Vaihe 5: Siivoa!”
Älkää unohtako tätä vaihetta.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
–Varmista, että jälki on poissa
SELECT * from sys.traces;
GO
–Kytke estyneiden prosessien raportti pois päältä, kun et käytä sitä.
–Tarkista, ettei sinulla ole vireillä olevia muutoksia
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
|
