Nu este un secret pentru nimeni că atunci când iau în considerare problemele de configurare a unui server SQL legate de creșterea productivității, majoritatea specialiștilor IT optează pentru creșterea hardware-ului. Dar este întotdeauna justificat acest lucru? Au fost deja folosite toate metodele de configurare a serverului? Se știe că lucrul cu parametrii de configurare și modificarea valorilor implicite ale acestora poate îmbunătăți performanța și alte caracteristici ale unui anumit sistem. Printre aceste opțiuni de configurare SQL, există o opțiune care are multe întrebări asociate, această opțiune este Grad maxim de paralelism (DOP) - așa că vom vorbi despre asta.
Opțiunea Maximum Degree of Parallelism (DOP) determină numărul de fire pe care SQL Server poate paraleliza o interogare și indică numărul de procesoare server utilizate. Acest parametru are o valoare implicită de 0 – gradul maxim de paralelism. De exemplu, dacă aveți 24 de nuclee, atunci valoarea „gradului maxim de paralelism” va fi egală cu 24, iar optimizatorul, dacă consideră necesar, poate folosi toate procesoarele pentru a executa o singură instrucțiune, adică cererea va fi paralelizate în 24 de fire. Acest lucru este bun pentru majoritatea cazurilor, dar nu pentru toată lumea. De asemenea, nu este întotdeauna bine să folosiți valoarea implicită a acestui parametru. Configurarea acestui parametru poate fi necesară, de exemplu, în următoarea situație: să presupunem că avem o aplicație în care toți angajații introduc informații despre tranzacțiile zilnice și, la o anumită perioadă de timp, fiecare dintre utilizatori rulează o interogare care construiește un raportează toate tranzacțiile utilizatorului pentru o anumită perioadă de timp. Desigur, dacă perioada de timp este lungă, această solicitare va dura mult timp pentru a se finaliza și, cu DOP instalat în mod implicit, va ocupa toate procesoarele disponibile, ceea ce va afecta în mod natural munca altor utilizatori. Prin urmare, prin modificarea valorii DOP, putem crește timpul de răspuns al serverului SQL pentru alți utilizatori fără a modifica interogarea în sine.
MS recomandă setarea valorii după cum urmează:
Setarea parametrului la TSQL în întregime pentru server:
EXEC sp_configure "gradul maxim de paralelism", 4; reconfigura
De asemenea, puteți seta această valoare pentru o anumită interogare TSQL:
UTILIZAȚI AdventureWorks2008R2; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS TotalFROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (MAXDOP 2); GO
În acest exemplu, indicația maxdop modifică valoarea implicită a parametrului gradul maxim de paralelism la 2. Puteți vizualiza setarea curentă astfel:
EXEC sp_configure "Show Advanced",1; RECONFIGURAȚI; EXEC sp_configure „gradul maxim de paralelism”
Acum să vedem cum această valoare afectează viteza de execuție a interogării. Pentru ca interogarea de test scrisă mai sus să fie executată mai mult timp, îi vom adăuga o altă selecție. Cererea va avea următoarea formă:
< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty
Pe mașina mea de testare, valoarea „gradului maxim de paralelism” este setată la 0. MSSQL rulează pe o mașină cu un procesor cu 4 nuclee. Am efectuat o serie de experimente cu diferite valori MAXDOP: egale cu 1 – fără paralelizare a interogărilor; egal cu 2 - folosind doar 2 nuclee; egal cu 4 – folosind toate și niciun indiciu pentru a determina opțiunea care utilizează continuarea implicită. Pentru a obține statistici de execuție, trebuie să includeți opțiunea SET STATISTICS TIME ON în interogare și, de asemenea, să activați butonul de afișare a planului de interogare în Management studio. Pentru a face o medie a rezultatelor, am rulat fiecare interogare într-o buclă de 3 ori. Rezultatele pot fi văzute mai jos:
SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 1); SQL Server Execution Times: CPU time = 45942 ms, elapsed time = 46118 ms. SQL Server Execution Times: CPU time = 45926 ms, elapsed time = 46006 ms. SQL Server Execution Times: CPU time = 45506 ms, elapsed time = 45653 ms.
Planul de interogare arată că atunci când a fost instalat indiciu (MAXDOP 1), interogarea a fost executată fără paralelizare. Timp mediu de execuție a interogării 45925,66 ms
SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 2); SQL Server Execution Times: CPU time = 51684 ms, elapsed time = 28983 ms. SQL Server Execution Times: CPU time = 51060 ms, elapsed time = 26165 ms. SQL Server Execution Times: CPU time = 50903 ms, elapsed time = 26015 ms.
La instalarea hint-ului (MAXDOP 2), cererea a fost executată în paralel pe 2 cpu, acest lucru se poate vedea în Numărul de execuție din planul de execuție a interogării. Timp mediu de execuție a interogării 27054,33 ms
SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 4); SQL Server Execution Times: CPU time = 82275 ms, elapsed time = 23133 ms. SQL Server Execution Times: CPU time = 83788 ms, elapsed time = 23846 ms. SQL Server Execution Times: CPU time = 53571 ms, elapsed time = 27227 ms.
La instalarea hint-ului (MAXDOP 4), cererea a fost executată în paralel pe 4 cpu. Timp mediu de execuție a interogării 24735,33 ms
SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty SQL Server Execution Times: CPU time = 85816 ms, elapsed time = 23190 ms. SQL Server Execution Times: CPU time = 85800 ms, elapsed time = 23307 ms. SQL Server Execution Times: CPU time = 58515 ms, elapsed time = 26575 ms.
cererea a fost executata in paralel, tot 4 cpu. Timp mediu de execuție a interogării 24357,33 ms
link-uri: http://support.microsoft.com/kb/2023536
Gradul maxim de paralelism (DOP) este o opțiune suplimentară de configurare SQL Server care a făcut obiectul multor întrebări și publicații. În această postare pe blog, autorul speră să ofere o oarecare claritate cu privire la ceea ce face această opțiune și cum ar trebui utilizată.
În primul rând, autorul ar dori să clarifice orice îndoială că opțiunea listată stabilește câte procesoare poate folosi SQL Server atunci când deservește mai multe conexiuni (sau utilizatori) - nu este așa! Dacă SQL Server are acces la patru procesoare inactive și este configurat să folosească toate cele patru procesoare, va folosi toate cele patru procesoare, indiferent de gradul maxim de paralelism.
Deci, ce face această opțiune? Această opțiune setează numărul maxim de procesoare pe care SQL Server le poate folosi pentru o singură interogare. Dacă o interogare către SQL Server trebuie să returneze o cantitate mare de date (multe înregistrări), uneori are sens să o paralelizezi, împărțind-o în mai multe interogări mici, fiecare dintre acestea va returna propriul subset de rânduri. Astfel, SQL Server poate folosi mai multe procesoare și, prin urmare, pe sistemele multiprocesor, un număr mare de înregistrări ale unei întregi interogări pot fi returnate mai rapid decât pe un sistem cu un singur procesor.
Există multe criterii care trebuie luate în considerare înainte ca SQL Server să invoce „Intra Query Parallelism” (împărțirea unei interogări în mai multe fire) și nu are rost să le detaliezi aici. Le puteți găsi în BOL căutând „Grad de paralelism”. Se spune că decizia de paralelizare se bazează pe disponibilitatea memoriei la procesor și, mai ales, pe disponibilitatea procesoarelor în sine.
Deci, de ce ar trebui să luăm în considerare utilizarea acestei opțiuni - deoarece lăsarea acesteia la valoarea implicită (SQL Server ia propria decizie cu privire la paralelizare) poate avea uneori efecte nedorite. Aceste efecte arată cam așa:
- Interogările paralele rulează mai lent.
- Timpii de execuție a interogărilor pot deveni nedeterminiști, ceea ce poate enerva utilizatorii. Timpul de execuție se poate modifica deoarece:
- Interogarea poate uneori să se paralelizeze și alteori nu.
- O solicitare poate fi blocată printr-o solicitare paralelă dacă procesoarele au fost supraîncărcate anterior de lucru.
Înainte de a continua, autorul ar dori să sublinieze că nu este nevoie în mod special de a se scufunda în organizarea internă a paralelismului. Dacă sunteți interesat de acest lucru, puteți citi articolul „Parallel Query Processing” din Books on Line, care descrie aceste informații mai detaliat. Autorul consideră că există doar două lucruri importante care merită știute despre organizarea internă a concurenței:
- Interogările paralele pot genera mai multe fire decât cele specificate în opțiunea „Grad maxim de paralelism”. DOP 4 poate genera mai mult de douăsprezece fire, patru pentru interogare și fire suplimentare utilizate pentru sortări, fluxuri, agregate și ansambluri etc.
- Solicitările paralele pot determina SPID-uri diferite să aștepte cu tipul de așteptare CXPACKET sau 0X0200. Acesta poate fi folosit pentru a găsi acele SPID-uri care sunt într-o stare de așteptare în timpul operațiunilor paralele și au un waittype în sysproceses: CXPACKET. Pentru a ușura această sarcină, autorul sugerează utilizarea procedurii stocate disponibilă pe blogul său: track_waitstats.
Și așa „Interogarea poate fi mai lentă când este paralelizată” de ce?
- Dacă sistemul are un debit foarte scăzut al subsistemelor de disc, atunci când se analizează o solicitare, descompunerea acesteia poate dura mai mult decât fără paralelism.
- Poate exista o distorsiune a datelor sau o blocare a intervalelor de date pentru procesor cauzată de un alt proces utilizat în paralel și lansat ulterior etc.
- Dacă nu există un index pe predicat, rezultă o scanare a tabelului. Operațiunea paralelă în cadrul unei interogări poate ascunde faptul că interogarea s-ar fi finalizat mult mai repede cu un plan de execuție secvențial și indexul corect.
Efectele paralelismului menționate mai sus ar trebui să vă facă în mod natural să credeți că mecanica internă a paralelizării interogărilor nu este potrivită pentru utilizare în aplicațiile OLTP. Acestea sunt aplicații pentru care modificarea timpilor de execuție a interogărilor poate fi enervantă pentru utilizatori și pentru care un server care deservește mulți utilizatori concurenți este puțin probabil să aleagă un plan de execuție paralelă din cauza profilului inerent al sarcinii de lucru a procesorului acestor aplicații.
Prin urmare, dacă urmează să utilizați paralelismul, atunci cel mai probabil veți avea nevoie de el pentru sarcini de recuperare a datelor (depozit de date), suport decizional sau sisteme de raportare, unde nu există multe interogări, dar sunt destul de grele și sunt executate pe un sistem puternic. server cu o cantitate mare de memorie RAM.
Dacă decideți să utilizați paralelismul, ce valoare ar trebui să setați pentru DOP? O bună practică pentru acest mecanism este că, dacă aveți 8 procesoare, setați DOP = 4 și cel mai probabil aceasta va fi setarea optimă. Cu toate acestea, nu există nicio garanție că va funcționa în acest fel. Singura modalitate de a fi sigur este să testați diferite valori pentru DOP. În plus, autorul a dorit să ofere sfatul său empiric să nu seteze niciodată acest număr la mai mult de jumătate din numărul de procesoare disponibile. Dacă autorul ar avea mai puțin de șase procesoare, ar seta DOP la 1, ceea ce pur și simplu dezactivează paralelizarea. El ar putea face o excepție dacă ar avea o bază de date care acceptă doar un singur proces utilizator (unele tehnologii de recuperare a datelor sau sarcini de raportare), caz în care, ca excepție, ar fi posibil să setați DOP la 0 (valoarea implicită), care permite SQL Server însuși să decidă dacă să paralelizeze o interogare.
Înainte de a termina articolul, autorul a ținut să vă avertizeze că crearea indexului paralel depinde de numărul pe care îl setați pentru DOP. Aceasta înseamnă că este posibil să doriți să-l modificați în timp ce indexurile sunt create sau recreate pentru a îmbunătăți performanța acestei operațiuni și, desigur, puteți utiliza indicația MAXDOP în interogare, care vă permite să suprascrieți valoarea setată în configurație și puteți să fie utilizat în timpul orelor de vârf.
În cele din urmă, interogarea dvs. poate încetini atunci când este paralelizată din cauza erorilor, așa că asigurați-vă că serverul dvs. are cel mai recent pachet de servicii instalat.
REATE proc track_waitstats@num_samples int = 10
,@delaynum int = 1
,@delaytype nvarchar ( 10 )="minute"
LA FEL DE
-- T. Davidson
-- Această procedură stocată este furnizată =CA ESTE= fără garanții,
-- și nu conferă drepturi.
-- Utilizarea mostrelor de script incluse se supune termenilor
-- specificat la http://www.microsoft.com/info/cpyright.htm
-- @num_samples este numărul de ori pentru a captura waitstats,
-- implicit este de 10 ori. intervalul implicit de întârziere este de 1 minut
-- delaynum este intervalul de întârziere. delaytype specifică dacă
-- intervalul de întârziere este de minute sau secunde
-- creați tabel waitstats dacă nu există, altfel trunchiați
nu pune la baza
dacă nu există (selectați 1
din sysobjects unde name = "waitstats" )
creați tabel waitstats ( varchar ( 80
),
cereri numerice ( 20
,1
),
numeric ( 20
,1
),
numeric ( 20
,1
),
acum datetime default getdate())
altfel trunchiați tabelele waitstats
dbcc sqlperf (waitstats,clear) -- ștergeți waitstats
declara @i int
,@delay varchar ( 8
)
,@dt varchar ( 3
)
,@now datetime
,@totalwait numeric ( 20
,1
)
,@endtime datetime
,@begintime datetime
,@hr int
,@min int
,@sec int
selectați @i = 1
selectați @dt = minuscul majusculei (@delaytype)
când „minute” apoi „m”
când „minut” apoi „m”
când „min” apoi „m”
când "mm" atunci "m"
când "mi" atunci "m"
când "m" atunci "m"
când „secunde” apoi „s”
când „a doua” apoi „s”
când „sec” apoi „s”
când „ss” apoi „s”
când „s” apoi „s”
else @delaytype
Sfârşit
dacă @dt nu este în ("s", "m")
ÎNCEPE
imprimare „Vă rugăm să furnizați tipul de întârziere, de exemplu, secunde sau minute”
întoarcere
Sfârşit
dacă @dt = "s"
ÎNCEPE
selectați @sec = @delaynum % 60
select @min = cast ((@delaynum / 60
) ca int )
selectează @hr = cast ((@min / 60
) ca int )
selectați @min = @min % 60
Sfârşit
dacă @dt = "m"
ÎNCEPE
selectați @sec = 0
selectați @min = @delaynum % 60
selectează @hr = cast ((@delaynum / 60
) ca int )
Sfârşit
selectează @delay = right("0" + convert(varchar( 2
),@HR), 2
) + ":"
+
2
),@min), 2
) + ":"
+
+ dreapta("0" +convert(varchar( 2
),@sec), 2
)
dacă @hr > 23
sau @min > 59
sau @sec > 59
ÎNCEPE
Selectați „hh:mm:ss timpul de întârziere nu poate > 23:59:59”
selectați „interval de întârziere și tastați:” + convert (varchar ( 10
)
,@delaynum) + "," + @delaytype + " se convertește în "
+ @întârziere
întoarcere
Sfârşit
in timp ce eu<= @num_samples)
ÎNCEPE
inserați în waitstats (, solicitări,
,)
exec ("dbcc sqlperf(waitstats)")
selectați @i = @i + 1
așteptați întârziere @întârziere
Sfârşit
Creați un raport privind așteptările
executa get_waitstats
--//--//--//--//--//--//--//--//--//-//--//--//--//--//--//--//--//--/
CREATE proc get_waitstats
LA FEL DE
-- Această procedură stocată este furnizată =CA ESTE= fără garanții și
-- Nu se referă la niciun drept.
-- Utilizarea mostrelor de script incluse se supune termenilor specificati
-- la http://www.microsoft.com/info/cpyright.htm
--
-- acest proces va crea un raport waitstats listând tipurile de așteptare până la
-- procent
-- poate fi rulat când track_waitstats se execută
nu pune la baza
declara @now datetime
,@totalwait numeric ( 20
,1
)
,@endtime datetime
,@begintime datetime
,@hr int
,@min int
,@sec int
selectați @now=max (acum),@begintime=min (acum),@endtime=max (acum)
din waitstats unde = „Total”
Scădeți waitfor, sleep și resource_queue din Total
selectați @totalwait = sum() + 1
de la waitstats
unde nu în ("WAITFOR", "SLEEP" ,"RESOURCE_QUEUE"
, „Total” , „***total***” ) și acum = @acum
Inserați totaluri ajustate, clasificați în procente descrescătoare
ștergeți waitstats unde = "***total***" și acum = @now
inserați în waitstats selectați „***total***”
,0
,@totalwait
,@totalwait
,@acum
Selectați
,
,procent = turnat ( 100
*/@totalwait ca numeric ( 20
,1
))
de la waitstats
unde nu se află în ("WAITFOR" , "SLEEP" , "RESOURCE_QUEUE" , "Total" )
iar acum = @acum
ordonati in procente desc
Acest post se va concentra doar pe MS SQL Server. Dacă intenționați să vă „încercați norocul” în utilizarea 1C cu Oracle, DB2, Postrgre, aceste informații vă vor fi inutile. Dar trebuie să înțelegeți că în 1C există, în primul rând, specialiști în MS SQL server. Datorită eforturilor IBM, apar și specialiști DB2. Vă puteți certa mult timp dacă acest SGBD este bun sau rău, un lucru este important, 1C funcționează cel mai „fără” cu serverul MS SQL. Judecând după cele mai recente rapoarte din „front”, lucrul cu DB2 a devenit mai mult sau mai puțin decent. Deși personal am avut experiență în configurarea 1C pentru a funcționa cu DB2 în versiunea 8.1, totul nu a fost cumva foarte bun. În orice caz, alegerea unui alt SGBD trebuie să fie clar justificată - fie prin capabilități care nu sunt disponibile în MS SQL (cluster cu echilibrare de încărcare, Grid etc.), fie prin finanțe (Oracle a fost deja achiziționat), fie prin platformă (totul este pe Linux).
Deci, în ordine, ce trebuie făcut cu MS SQL Server:
1) Setați dimensiunea minimă și maximă a memoriei. Minimul este jumătate din memoria sistemului. Maxim - memorie de sistem fără 2 GB. Acest lucru se face prin Management Studio - în proprietățile serverului:
2) Dacă prioritatea nu este setată în fila „Procesoare”, trebuie să o setați
3) Setați gradul maxim de paralelism la 1.
4) Porniți SQL Server Agent, configurați Database Mail - nu este nimic dificil acolo, nu o voi descrie în detaliu.
5) Stabilirea planurilor de servicii:
Sunt comune:
a) Actualizare statistică - la fiecare 2 ore
b) DBCC FREEPROCCACHE - la fiecare 2 ore
Pentru fiecare bază:
a) Backup complet
b) Backup diferențial
c) Defragmentarea indicilor – în fiecare zi
d) Refacerea indicilor - noaptea in weekend
e) Verificarea integritatii bazei de date - o data pe luna noaptea in weekend
6) Recomand să setați modelul de recuperare ca Simplu pentru fiecare bază de date (în proprietăți). Dacă nu aveți un sistem 24/7 și mai puțin de 1000 de utilizatori pe bază, nu există un cluster de failover și nu ați semnat un SLA în care vă angajați să restaurați datele cu o acuratețe de până la o secundă (și nu de la timpul ultimului backup) în cazul defecțiunii oricărui echipament, această recomandare ar fi rezonabilă. În caz contrar, foarte curând te vei gândi îndelung și frenetic la ce să faci cu jurnalul de Tranzaction.
7) Eliminați baza de date tempdb din bazele de date obișnuite pe un alt disc - chiar dacă aceasta înseamnă reconfigurarea matricei RAID și reducerea performanței acesteia. În caz contrar, 1 utilizator va putea paraliza munca tuturor celorlalți. Dacă aveți Hardware Accelerator în loc de un hard disk, atunci bineînțeles că nu îl puteți separa și pune tempdb pe el, dar asta numai dacă aveți unul
8) instalați un fel de instrument de monitorizare - de exemplu, îmi place spotlight http://www.quest.com/spotlight-on-sql-server-enterprise/
9) Testează-te cu analizorul de bune practici de la Microsoft - http://www.microsoft.com/download/en/details.aspx?id=15289 - un instrument minunat care ajută nu numai la setări, ci și la rezolvarea multor probleme .
Acum, pe scurt, de ce am făcut toate acestea:
1) Memoria. Valoarea minimă vă va proteja pur și simplu de „eșecuri” atunci când serverul SQL, dintr-un motiv cunoscut doar de acesta, nu folosește toată memoria disponibilă. Trebuie să mănânce totul! Valoarea maximă vă va proteja de schimb dacă același optimizator de utilizare a memoriei serverului SQL decide că nu poate face nimic altceva...
3) Un punct foarte important - IHMO trebuie setat la 1 în toate sistemele de tranzacții. În primul rând, acest lucru previne unele blocări între diferite procese care încearcă să îndeplinească o cerere și, în consecință, ne protejează de unele erori „ciudate”. În al doilea rând... 1 cerere „killer” poate prelua toate resursele serverului, ceea ce este oarecum nedrept în raport cu alți utilizatori ai sistemului. Parametrul în sine determină câte nuclee de procesor pot procesa 1 cerere.
5) Statisticile și ștergerea cache-ului procedural sunt bine cunoscute, dar adesea uităm de reindexare. Între timp, această procedură este destul de importantă, mai ales pe măsură ce volumul bazei de date crește, importanța acesteia crește. Uneori, până la 60% din performanță se pierde din cauza fragmentării indexului.
7) Dacă aveți un Hardware Accelerator sau doar 2 discuri cu viteze de acces diferite, aș recomanda, de asemenea, să vă gândiți la alocarea unor grupuri de fișiere în bazele de date și la împărțirea tabelelor individuale în diferite matrice de discuri cu timpi de acces diferiți. La urma urmei, trebuie să recunoașteți că RN „mărfuri în depozite” și directorul „Depozitarea informațiilor suplimentare” sunt 2 obiecte ale căror cerințe de stocare sunt fundamental diferite. Nu este necesar să stocați toate fișierele și imaginile din baza de date într-o matrice rapidă - o puteți separa într-una separată, care nu este atât de rapidă, dar unde există mult spațiu (și apoi nu vă fie teamă pentru a încărca o grămadă de fișiere în baza de date, apropo).
În această scurtă notă, aș dori să vorbesc puțin despre complexitatea setărilor de paralelism în Microsoft SQL Server. Mulți dintre voi sunteți conștienți de opțiunea Max Degree od Parallelism, care este prezentă în SQL Server de foarte mult timp. În mod implicit, este setat la 0, ceea ce înseamnă că SQL Server însuși va alege gradul optim de paralelism, adică numărul de procesoare/thread-uri folosite pentru a executa o instrucțiune. Acum nu mă voi opri și discuta la ce valoare este mai bine să setați această opțiune - acesta este un subiect pentru o notă separată. Mă voi uita doar la modul în care valoarea acestei opțiuni afectează execuția interogărilor. De exemplu, în figura de mai jos, această opțiune este setată la 1, ceea ce înseamnă că planurile paralele pentru toate interogările sunt dezactivate în mod implicit.
Această opțiune este disponibilă și pentru vizualizare folosind următoarea comandă T-SQL:
Într-adevăr, orice plan de interogare va fi secvenţial în mod implicit. De exemplu:
Cu toate acestea, dezvoltatorul și orice utilizator au în continuare posibilitatea de a influența acest lucru folosind indicii. Pentru a face acest lucru, trebuie doar să specificați gradul de paralelism dorit și este generat planul de interogare dorit, de exemplu:
Și dacă ne uităm la această interogare prin vizualizarea sys.dm_exec_query_profiles, vom vedea că este de fapt executată în 10 fire.
Astfel, rămâne o gaură secretă în sistem pe care dezvoltatorii și utilizatorii o pot folosi pentru a „accelera” (aici am pus-o între ghilimele intenționat, deoarece un grad ridicat de paralelism nu duce întotdeauna la o scădere a timpului de execuție a interogărilor) lor. interogări prin creşterea gradului de paralelism . Dar, în acest fel, ei pot pur și simplu „ucide” serverul rulând multe solicitări paralele necontrolate în același timp. Ce putem face în privința asta? Aici ne vine în ajutor Resource Governor, un sistem foarte puternic și complet subestimat, care vă permite să distribuiți foarte flexibil resursele între diferite grupuri de utilizatori. Din nou, nu mă voi opri acum asupra modului în care funcționează și ce capacități are. Voi intra în detaliu despre modul în care setările sale limită de concurență îl afectează. Să aruncăm mai întâi o privire la setările implicite:
Din nou vedem că implicit opțiunea este setată la 0 și decizia privind alegerea gradului maxim este lăsată la SQL Server. Acum să vedem ce se întâmplă dacă schimb această valoare la 5. Atenție, sub nicio formă nu faceți astfel de setări pe un sistem real, deoarece Nici măcar nu am definit funcția de clasificare pentru Resource Governor și schimb grupul implicit. Dar pentru a testa și înțelege cum funcționează totul în mod specific acum în exemplul meu, acest lucru este suficient. Deci limitez gradul maxim de paralelism pentru toată lumea la 5 fire. Permiteți-mi să vă reamintesc că opțiunea Gradul maxim de paralelism, la care ne-am uitat mai devreme este încă setată la valoarea 1. Dacă ne uităm acum la planul de execuție al interogării noastre originale, atunci implicit va fi secvenţial, iar cu opţiunea maxdop 10 va fi paralelă. Dar, dacă rulăm un plan paralel, vom vedea ceva interesant.
Acum cererea noastră este executată în doar 5 fire, în ciuda faptului că opțiunea maxdop are valoarea 10. Și, dacă specificați opțiunea maxdop 4 pentru cerere, aceasta va fi executată în 4 fire (opțiunea din Resource Governor este setată la 5). În acest caz, indiciu maxdop mai mică decât setarea Resource Governor, deci nu este impusă nicio limitare suplimentară. Nu voi mai da un exemplu în acest sens.
Astfel, Resource Governor este un instrument mai puternic care limitează de fapt gradul maxim de paralelism pentru interogări, iar acest grad poate fi setat diferit pentru diferite grupuri de utilizatori. În acest caz, opțiunea Gradul maxim de paralelism continuă să funcționeze și își aduce contribuția (sau confundă ușor administratorii, dezvoltatorii și utilizatorii atunci când lucrează împreună cu Resource Governor). În plus, opțiunile de setare a valorilor acestor 2 parametri sunt limitate doar de imaginația ta, dar este important să reții doar două lucruri: Gradul maxim de paralelismși indiciu maxdop pentru o cerere, afectează ce plan va fi generat, numărul maxim de fire de execuție care va fi posibil pentru această solicitare, iar Guvernatorul resurselor limitează și mai mult cererea de sus în timpul execuției.
Parametrul „gradul maxim de paralelism” specifică numărul maxim de fire de execuție pe care SQL Server poate paraleliza o interogare. În mod implicit, această setare este zero, care utilizează numărul de procesoare de pe server. De exemplu, dacă aveți 24 de nuclee, valoarea reală a „gradului maxim de paralelism” va fi 24, iar optimizatorul, dacă consideră necesar, poate paraleliza interogarea în 24 de fire. În general, acest lucru este bun, dar nu întotdeauna. De asemenea, nu este întotdeauna bine să folosiți valoarea implicită a acestui parametru.
Acum să vedem de ce acest lucru nu este bine. Voi da un exemplu din practica mea. Există o interogare care, în teorie, ar trebui să folosească un anumit index și la început se întâmplă acest lucru. Se lansează o interogare care caută indexul și returnează datele necesare. Totul e bine. Apoi, pe măsură ce baza de date crește, în tabel sunt adăugate din ce în ce mai multe înregistrări, iar la un moment dat optimizatorul își dă seama că este posibil să execute interogarea mai rapid: „De ce ar trebui să efectuez o căutare pe index dacă am 24 de nuclee? Aceasta înseamnă că pot scana indexul grupat în 24 de fire și pot obține datele de care am nevoie mai repede!” Pentru această interogare specială, aceasta este bună - rulează mai repede. Dar este rău pentru toți ceilalți, pentru că... sunt nevoiți să aștepte ca resursele procesorului să le fie alocate. Și în sistemele cu un număr mare de interogări care se execută simultan, o astfel de paralelizare este mai probabil să fie proastă decât bună. Și în loc să îmbunătățească productivitatea, aceasta se înrăutățește doar. Până de curând, am rezolvat această problemă setând indicația MAXDOP în depozitele care nu mi-au plăcut. Dar acum am găsit o recomandare specifică Microsoft și am aplicat-o pe serverele mele. Recomandări pentru alegerea valorii optime pentru „gradul maxim de paralelism” sunt aici:Recomandări și orientări pentru opțiunea de configurare „gradul maxim de paralelism”. . Citez aceasta recomandare:
Pentru serverele SQL Server 2008 R2, SQL Server 2008 și SQL Server 2005, utilizați următorul ghid: a. Pentru serverele care au opt sau mai puține procesoare, utilizați următoarea configurație în care N este egal cu numărul de procesoare: gradul maxim de paralelism = 0 la N. b. Pentru serverele care folosesc mai mult de opt procesoare, utilizați următoarea configurație: grad maxim de paralelism = 8. c. Pentru serverele care au configurat NUMA, gradul maxim de paralelism nu trebuie să depășească numărul de procesoare care sunt atribuite fiecărui nod NUMA cu valoarea maximă limitată la 8. Acest lucru va crește probabilitatea ca toate firele paralele ale unei interogări să fie localizate într-un NUMA Node și evitați căutările costisitoare de date ale nodurilor de la distanță. d. Pentru serverele care au activat hyper-threading, valoarea maximă a gradului de paralelism nu trebuie să depășească numărul de procesoare fizice.
Rezultă că pentru sistemele cu mai mult de 8 procesoare, se recomandă setarea „gradului maxim de paralelism” = 8. Urmează o altă explicație, care spune că 8 este o recomandare generală. Și în sistemele în care numărul de cereri care se execută simultan este mic, este logic să setați o valoare mai mare, iar în sistemele cu un număr mare de solicitări concurente, este logic să setați o valoare mai mică. Și atunci când alegeți un anumit parametru, trebuie să vă uitați la impactul acestuia asupra sistemului și să-l testați pe anumite solicitări.