Caracteristicile foarte importante ale optimizării SQL Server sunt asociate cu sistemul de blocare. Implementarea acestui subsistem în SQL Server provoacă plângeri justificate din partea specialiștilor, iar pentru multe sarcini practice este necesară blocarea manuală.
Există cinci nivele principale de blocare în SQL Server:
q blocarea nivelului bazei de date (DB). Aceste blocări sunt suprapuse automat pe orice bază de date la care este conectat utilizatorul. Practic, ele sunt concepute pentru a interzice executarea anumitor acțiuni, de exemplu, ștergerea datelor, cu bazele de date la care sunt conectați utilizatorii;
q blocarea nivelului obiectului (de exemplu, TAB). Astfel de blocări pot fi suprapuse pe o masă sau pe un index, atât atunci când se execută interogări normale, cât și atunci când se efectuează operații de service cu aceste obiecte;
q Blocarea nivelului de nivel (EXT). Asemenea blocări pot fi rareori văzute în SQL Server Management Studio sau în rezultatele unei proceduri stocate sp_lock. Acestea sunt utilizate numai pentru astfel de operațiuni de servicii, cum ar fi crearea de noi tabele, mărirea dimensiunii fișierelor bazei de date etc.
q blocarea la nivel de pagină (PAG). Asemenea încuietori sunt folosite foarte des de către SQL Server. Când sunt folosite, întreaga pagină de 8KB este blocată, cu toate intrările care se află în ea. Acest tip de blocare poate fi utilizat atât pentru paginile de date cât și pentru paginile index;
q nivel de scriere / blocare cheie (RID / KEY). Asemenea încuietori sunt suprapuse pe înregistrări separate. Blocările de tip RID sunt suprapuse pe înregistrările din tabele fără un index de cluster (heap), iar blocările KEY sunt scrise în înregistrările din tabelele pentru care este furnizat un index cluster.
Încălcările diferă și în funcție de tipuri (generale, exclusive, blocarea așteptărilor etc.), dar numai nivelul lor este important în scopuri de optimizare.
În mod implicit, SQL Server gestionează automat nivelul de blocare. Pentru cele mai multe operații, SQL Server încearcă mai întâi să utilizeze numai blocări la nivel de scriere. Dacă o solicitare de a citi sau de a modifica date se referă la un număr mare de înregistrări, apoi pentru a salva resursele, SQL Server poate decide să utilizeze blocări la nivel superior. Această creștere se numește escaladarea încuietorilor (blocaj).
Observăm câteva probleme tehnice legate de escaladarea blocărilor.
În cele mai multe situații, SQL Server încearcă inițial să utilizeze blocări la nivel de scriere sau la nivel de pagină. Ce nivel de încuietori de utilizat - înregistrări sau pagini, este determinat înainte de executarea interogării. Decizia de a escalada blocările SQL Server are două situații:
q când cererea încearcă să aplice mai mult de 5.000 de încuietori la nivel de înregistrare sau de pagină la un singur obiect (la un tabel sau un index). Valoarea 5000 de încuietori este luată din documentație, dar în practică, SQL Server uneori continuă să utilizeze încuietori la nivel de scriere sau la nivel de pagină și cu un număr de sute de mii de asemenea încuietori. În acest caz, SQL Server nu escaladează de la nivelul de scriere la nivelul paginii, dar încearcă imediat să se blocheze pe masă;
q când se termină locul din zona memoriei principale alocate pentru lucrul cu încuietori. Pentru fiecare blocare, SQL Server alocă 96 de octeți. Dimensiunea zonei de memorie pentru a fi utilizate cu SQL Server blochează implicit (parametrul set server de blocare. Egal cu 0) este ajustată în mod dinamic. Odată ce dimensiunea zonei ajunge la 40% din cantitatea totală de memorie utilizată de procesul de SQL Server (este limitată la sistemul de operare sau parametrul MAX SERVER memorie), SQL Server va încerca în mod automat pentru a face o escaladare de blocare. În cazul în care dimensiunea zona de memorie a ajuns la 60% din cantitatea de memorie, maximă disponibilă SQL Server. crearea de noi încuietori nu se realizează, iar clientul returnează eroare 1204 „în imposibilitatea de a aloca resurse de blocare“ (este imposibil de a plasa un resurse de blocare).
Dacă SQL Server nu a reușit să ridice nivelul de blocare (de exemplu, la nivelul tabelului există deja o blocare a unei alte tranzacții), încercările vor fi efectuate la fiecare 1250 de încuietori noi la nivel de înregistrare sau de pagină.
Monitorizarea escalărilor de blocare poate fi efectuată utilizând profilele selectând blocarea evenimentului: Escaladare.
În exterior, mecanismul de escaladare pare foarte logic, dar în practică apar probleme cu el:
q Când efectuați operații care trebuie efectuate cu un număr mare de înregistrări în tabel, SQL Server încearcă să utilizeze mai întâi încuietori la nivel de scriere. Ca urmare, o cantitate semnificativă de resurse de sistem este cheltuită pentru instalarea și ulterior eliminarea unor asemenea blocări. Acest lucru ar putea fi evitat dacă aplicați imediat nivelul necesar de încuietori (PAG sau TAB) pentru a efectua operația;
q A doua problemă mai importantă este că SQL Server aplică escaladarea blocărilor, inclusiv pe servere puternice cu care lucrează simultan un număr mare de utilizatori. O situație tipică arată astfel: există o masă mare în baza de date cu care utilizatorii lucrează în mod constant (să o numim tabelul principal). Datorită creșterii numărului de încuietori, numărul de intrări care blochează simultan utilizatorii este automat crescut, ceea ce duce la imposibilitatea accesării altor utilizatori. Astfel, atunci când ajungeți la un anumit număr de utilizatori care lucrează cu acest tabel este foarte împiedicat. Este deosebit de neplăcut faptul că înregistrările cu care utilizatorii nu lucrează efectiv sunt blocate: au lovit doar o pagină cu alte înregistrări care sunt deschise în acest moment.
Pentru a rezolva aceste probleme, în funcție de situație, puteți utiliza diferite metode:
q Prima caracteristică care vă permite să scăpați de încuietori atunci când solicitați citirea datelor - utilizați nivelul adecvat de izolare a tranzacțiilor. La operațiile de înregistrare, nu se impun blocări atunci când se utilizează nivele:
· CITIȚI COMUNITATEA. Când parametrul bazei de date READ_COMMINTED_SNAPSHOT este setat la ON;
Astfel de niveluri de izolare pot fi utilizate perfect pentru interogări cu informații statistice, atunci când precizia absolută a informațiilor poate fi sacrificată din motive de productivitate. Pentru rapoartele financiare, această metodă poate să nu fie adecvată;
o altă soluție radicală este de a muta baza de date în starea R EAD ONLY. În acest caz, la accesarea acestuia nu vor fi utilizate blocări, ceea ce poate da un câștig foarte mare în performanță și poate crește numărul de utilizatori care pot lucra simultan cu masa. Dar, din motive evidente, această soluție nu funcționează întotdeauna;
q Dacă trebuie să crească inițial nivelul de blocare, puteți folosi indicii NOLOCK. ROWLOCK. PAGLOCK și TABLOCK în interogări. Minus această abordare - nu este întotdeauna posibilă modificarea codului aplicației;
Pentru un indice nonclustered;
· Pentru un index cluster (aceleași setări vor fi aplicate simultan în tabel);
· Pentru o tabelă care nu are indici cluster (datele sunt în "heap"). Această posibilitate este nedocumentată, dar poate fi utilizată.
Folosind valoarea OFF pentru parametrul ALLOW_PAGE_LOCKS din această comandă, puteți scăpa de blocările la nivel de pagină, motivul principal pentru problemele legate de accesul simultan al utilizatorilor.
q Dacă problema, după părerea dvs., se poate datora spațiului insuficient pentru zona de blocare, puteți încerca să ajustați manual mărimea acestei zone. Pentru a face acest lucru, puteți utiliza parametrul de configurare al serverului de blocare. și, de asemenea, încercați să creșteți cantitatea de memorie RAM disponibilă pentru SQL Server. prin creșterea RAM-ului fizic pe server sau prin utilizarea parametrului MAX SERVER MEMORY;
o altă posibilitate radicală este de a folosi steagurile de urmărire. Steagul 1211 dezactivează pur și simplu orice escaladare a încuietorilor. Cu această opțiune, trebuie să fie foarte atent, t. Pentru a. Puteți reduce dramatic performanța de server și de a crește consumul de RAM. Mai mult decât atât, epuizarea RAM alocată de blocare, este posibil să primiți un indicator de eroare 1204. În al doilea rând - 1224. Se dezactivează escaladarea de blocare produs de metru (5000 încuietori la nivelul inferior), dar spațiul de epuizare în zona de memorie retrasă în conformitate cu blocare, escaladarea va continua să apară.
Dacă setați în același timp steagurile 1224 și 1211, se va acorda prioritate semnalului 1224.