SQL Server parametrs: "blocked process threshold"

Šis parametrs nosaka slieksni, kuru pārsniedzot, SQL Server ģenerē ziņojumu par bloķētiem resursiem. Pēc noklusējuma šis parametrs ir 0. Tas nozīmē, ka bloķēto resursu ziņojumi netiek ģenerēti (tātad, nevar sekot līdzi gadījumiem, kuros uz resursiem jāgaida pārāk ilgi).

Kad tas nepieciešams

Piemēram, vēlamies ielasīt datu rindu no tabulas "Klienti". Šī rinda ir mainīta kādas citas transakcijas ietvaros, kura turpina izpildīties. Lasīšanai ir jāgaida, kamēr datu mainīšanas transakcija ir izpildījusies vai ir atcelta. Šis gaidīšanas laiks teorētiski var būt bezgalīgs (gadījumā, ja otra transakcija turpinās bezgalīgi un nav mainīti citi uzstādījumi).
Konstatēt, ka šāda gaidīšana ieilgst, var būt problemātiski (ja vien nav foršie lietotāji, kas operatīvi ziņo par sistēmas "bremzēšanu"). Arī saprast, kas šo situāciju radījis, nemaz nav vienkārši (daudzi iemesli, ko šeit neuzskaitīšu). Izmantojot parametru "blocked process threshold" iespējams monitorēt brīžus, kad uz kādu resursu tiek gaidīts pārāk ilgi.

Izmantojot  "blocked process threshold", monitorēšanu šādām situācijām var veikt vismaz trijos veidos:

• Skatīties notikumus izmantojot SQL Server Profiler;
• Reģistrēt notikumus izmantojot SQL Server servera puses trasēšanu (mazāk resursu prasīgs iepriekšējais variants);
• Reģistrēt notikumus izmantojot Event Notifications (realizācija līdzīgi, kā aprakstīts Event Notification: Strupsaķeres (deadlock) reģistrešanai).

Konfigurēšana

Konfigurācija ir gluži kā ar citiem SQL Server konfigurācijas parametriem (msdn).
Lai uzliktu, ka bloķēto procesu ziņojums tiktu ģenerēts gadījumos, ja kāds process gaida ilgāk par 5 sekundēm, jāizpilda šāda komanda:

EXEC sys.sp_configure N'blocked process threshold (s)', N'5'
RECONFIGURE WITH OVERRIDE

Lai uzliktu noklusēto vērtību (ziņojums netiek ģenerēts):

EXEC sys.sp_configure N'blocked process threshold (s)', N'0'
RECONFIGURE WITH OVERRIDE

Rekomendācijas

Parametra "blocked process threshold" vērtība (msdn):

• Kā vienmēr, vairumā gadījumu noklusētie uzstādījumi ir labi. Tātad,  ja nav vajadzības monitorēt bloķētos procesu, parametra vērtību jāatstāj nemainīgu (= 0).
• Monitorējot SQL Server darbību produkcijas vidē, šo vērtību uzstādīt vismaz 5 (tātad, ģenerēt notikumu tikai tad, ja gaidīšanas laiks uz resursiem ir lielāks par piecām sekundēm). Vērtības, kas ir mazākas par 5, var radīt ātrdarbības problēmas.

Lietošanas piemērs

Ar Profiler palīdzību to var izdarīt tā:
1. Atveram profiler;
2. Pieslēdzamies serverim;
3. "Event Selection" daļā sameklējam notikumu "Blocked process report" (zem "Errors and Warnings"), atķeksējam to.
.. Sākam gaidīt .. Ja parametrs ir uzstādīts uz "0", tad šis notikums nekad netiks reģistrēts (skatīt augstāk, sadaļā "konfigurācija").

Lai simulētu šo notikumu testa nolūkiem, var darīt tā:
SSMS logā Nr 1 izpildīt komandu (transakcija tīšām paliek nepabeigta):

create table ##test
(
    i int
)
begin tran
insert into ##test values (1)

SSMS logā Nr 2 izpildīt komandu (šis gaidīs, līdz 1. logā tiks pabeigta transakcija):

select * from ##test

Profiler būs redzams bloķēšanas ziņojums (ar visu papildus nepieciešamo informāciju XML formā):