de: Ahmad Yaseen | actualizat: 2018-03-30 | Comentarii (3) / Related :mai mult > optimizarea interogării
Free MSSQLTips Webinar: indexare pentru performanță: strategii de interogare pentru a maximiza indici SQL Server
puține lucruri influențează viteza și performanța de instrucțiuni SQL în SQL Server la fel de mult ca și indici. Această sesiune vă va ajuta să înțelegeți elementele de bază ale performanței interogării în raport cu indicii din Baza de date de producție. Vă vom învăța cum să determinați dacă utilizați strategii bune de indexare și impactul direct pe care strategia dvs. de indexare îl are asupra interogărilor.
problemă
știu că SQL Server poate rula interogări în paralel, dar cum pot spune că o interogareutilizează un plan de execuție paralel și cum pot obține informații despre numărul de CPU-uri utilizate atunci când o interogare se execută în paralel? Consultați acest sfat pentru a afla cum.
soluție
conceptul de paralelism provine din împărțirea unei sarcini mari în sarcini mai mici, unde fiecare sarcină mică este atribuită unei anumite persoane sau procesor în cazul unei interogări SQLServer, pentru a realiza o parte din sarcina principală. În cele din urmă, rezultatele parțialeluate din fiecare sarcină mică vor fi combinate împreună într-un singur rezultat final. Vă puteți imagina cât de repede se poate face acest lucru și câștigul de performanță pe care îl puteți obține de la rularea mai multor sarcini seriale în același timp în paralel, comparativ cu rularea unei sarcini mari în serie!
înainte de a trece prin sfat, este benefic să înțelegeți o serie de termeni tehnici legați de conceptul de paralelism în SQL Server:
- Planificatorul este procesorul fizic sau logiceste responsabil pentru programarea execuției firelor SQL Server.
- lucrătorul este firul care este legat de un planificator pentruefectuați o sarcină specifică.
- gradul de paralelism este numărul de lucrători, saunumărul de procesatori, care sunt atribuite pentru planul paralel pentru a realiza sarcina lucrătorului.
- gradul maxim de paralelism (MAXDOP) este o opțiune la nivel de server,bază de date sau interogare care este utilizată pentru a limita numărul de procesoripe care planul paralel îl poate utiliza. Valoarea implicită a MAXDOP este 0, în caremotorul serverului MySQL poate utiliza toate procesoarele disponibile, până la 64, în interogarea parallelexecution. Setarea opțiunii MAXDOP la 1 va împiedica utilizarea mai multor procesoareîn executarea interogării, ceea ce înseamnă că motorul SQL Server va utiliza un serialplan pentru a executa interogarea. Opțiunea MAXDOP poate avea o valoare de până la 32767, undemotorul SQL Server va utiliza toate procesoarele de server disponibile înexecuția planului paralel dacă valoarea MAXDOP depășește numărul de procesoaredisponibil în server. Dacă serverul SQL este instalat pe un singur procesorserver, valoarea MAXDOP va fi ignorată.
- sarcina este o mică lucrare care este atribuită unui lucrător specific.
- contextul de execuție este limita în care fiecare singletask rulează în interior.
- furnizorul de pagini paralele este o parte a motorului SQL ServerStorage care distribuie seturile de date solicitate de interogare în cadrul lucrătorilor participanți.
- schimbul este componenta care va conecta diferitele contexte de execuție implicate în planul paralel de interogare împreună, pentru a obține rezultatul final.
decizia de a folosi un plan paralel pentru a executa interogarea sau nu depinde demai mulți factori. De exemplu, SQL Server ar trebui să fie instalat pe un multi-procesorserver, numărul solicitat de fire ar trebui să fie disponibile pentru a fi îndeplinite, Themaximum grad de paralelism opțiune nu este setat la 1 și costuldin interogare depășește pragul de cost configurat anterior pentru Parallelismvalue.
scopul acestui sfat, este de a prelua informațiile filetelor de interogare paralele din planul de execuție real al interogării. Pentru a forța motorul SQL Server săexecuteze interogarea trimisă folosind un plan paralel, vom seta pragul de Cost pentru valoarea paralelismului la 0, pentru a ne asigura că în toate cazurile costul interogării va depăși pragul de Cost pentru valoarea paralelismului și vom păstra opțiunea de grad maxim de paralelism cu valoarea implicită 0, pentru a permite motorului SQL Server să utilizeze toate procesoarele disponibile în timpul executării interogării noastre, adică 4 procesoare în mymachine utilizate pentru această demonstrație.
pragul de Cost pentru valoarea paralelismului poate fi setat folosind SQL Server ManagementStudio, conectându-vă la instanța SQL Server, făcând clic dreapta pe numele instancename și alegând opțiunea Proprietăți. Din pagina avansată a proprietății Serveruluifereastră, derulați în jos până la secțiunea paralelism, de unde puteți suprascrie valora implicită a pragului de Cost pentru opțiunea paralelism, care este 5, sau Maxgradul de paralelism, dacă este necesar, așa cum se arată în captura de ecran de mai jos:
de asemenea, puteți suprascrie valoarea implicită a pragului de Cost pentru Parallelismusing sp_configure. Pentru a putea modifica pragul de Cost pentru valoarea paralelismului, ar trebui să utilizați sp_configure pentru a activa mai întâi opțiunea Afișare avansată, așa cum se arată în script-ul de mai jos:
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;GOEXEC sp_configure 'cost threshold for parallelism', 0;RECONFIGURE;GOEXEC sp_configure 'show advanced options', 0;RECONFIGURE;
Demo de plan de execuție paralel
serverul este acum configurat pentru demo. Să creăm un nou tabel utilizândcreați tabelul T-SQL după cum se arată mai jos:
USE MSSQLTipsDemoGOCREATE TABLE ParallelDemo( ID INT IDENTITY (1,1) PRIMARY KEY, FirstName NVARCHAR (200), LastName NVARCHAR (200), PhoneNumber VARCHAR(50), BirthDate DATETIME, Address NVARCHAR(MAX) )
odată ce tabelul este creat, vom umple tabelul cu înregistrări 100K folosindintroduceți în instrucțiunile de mai jos:
INSERT INTO ParallelDemo VALUES ('John','Horold','+96255889955','1987-01-08','Jordan - Amman - Mecca Street - Building 77')GO 50000 INSERT INTO ParallelDemo VALUES ('Michel','Anglo','+96255776655','1985-06-02','Jordan - Amman - Mecca Street - Building 74')GO 50000
acum vrem să activăm „includeți planul de execuție real” și apoirulați interogarea de selectare de mai jos:
SELECT , , , , , FROM .. WHERE Address LIKE '%AMM%' ORDER BY BirthDate desc
după ce se termină rularea, putem verifica planul de execuție generat deexecutarea interogării, veți vedea că motorul SQL Server decide să utilizeze un parallelplan pentru a executa interogarea, datorită faptului că costul acelei interogări depășește pragul de cost pentru valoarea paralelismului.
putem vedea mai jos că planul de execuție este un plan paralel, din cauza Paralelismoperator și, de asemenea, cercurile galbene cu două săgeți sub fiecare operator care executatcu paralelism.
planul de execuție nu este doar un grafic, ci conține informații valoroasedescrie procesul de execuție al interogării trimise. Pentru a vizualiza aceste informații, faceți clic dreapta pe nodul SELECT din planul de execuție și alegețiopțiunea proprietăți. Din fereastra Proprietăți, puteți vedeanumărul de procesoare care sunt utilizate pentru a procesa interogarea trimisă dingradul atributului paralelism, sub secțiunea Misc, așa cum se arată mai jos:
din aceeași fereastră, informațiile despre firele de interogare paralele sunt, de asemenea, afișate sub secțiunea ThreadStat. În acea secțiune, puteți verifica atributul ramurilor de interogare paralele, care arată numărul de căi de execuție concurente din planul de execuție a interogării, ID-ul nodului NUMA, numărul de fire paralele rezervate Numanodului specificat sub atributul ReservedThreads și numărul de fire utilizate în timpul executării interogării, sub atributul UsedThreads.
din fereastra de proprietăți, puteți deriva că motorul SQL Server a folosit 4procesoare pentru a executa interogarea. În plus, planul de execuție a interogării are o cale de execuție, în care 4 fire sunt rezervate și utilizate pentru a executa interogarea trimisă folosind un plan paralel.
aceleași informații pot fi derivate și din planul de execuție XML, căutând în secțiunea ThreadStat, așa cum se arată mai jos:
puteți, de asemenea, se arunca cu capul mai adânc pentru a verifica i/o și resursele CPU consumate de eachthread. Faceți clic dreapta pe nodul de scanare Index cluster din executionplan și alegeți Proprietăți. Din fereastra de proprietăți afișată, puteți extindenodul real de statistici I / O pentru a verifica numărul de citiri logice și fiziceoperațiuni efectuate de fiecare fir în timpul executării interogării. În plus, youpoate verifica, de asemenea, numărul de rânduri care sunt preluate din fiecare fir, timpul CPU-ulconsumate de fiecare fir și în cele din urmă timpul de execuție care este scurs de fiecare firpentru a-și îndeplini sarcina, așa cum se arată mai jos:
rezultatele se potrivesc cu fereastra SELECT node Properties, unde există 4 fire de lucru care au participat la executarea sarcinilor de interogare și un fir de coordonare care coordonează între firele de lucru.
luați în considerare faptul că informațiile anterioare pot fi preluate numai din planul de execuție real care este generat după executarea interogării. Dacă încercați să căutați acele informații din planul de execuție estimat, fără a executa interogarea, nu vor fi afișate informații pe măsură ce interogarea trebuie executată pentru a specifica numărul de procesoricare vor fi atribuite interogării de la procesoarele disponibile în timpul interogăriiexecuția așa cum se arată mai jos pentru planul de execuție estimat.
obținerea mai multor informații SQL Server paralelism
în timp ce interogarea se execută, puteți rula pur și simplu Comanda sp_who 2 cu ID-ul querysession pentru a obține informații despre firele care sunt utilizate pentru a executa submittedquery în timpul executării interogării, așa cum se arată mai jos:
în planul de execuție, fiecare operator în acest plan a avut un număr atribuit itand un planificator care este utilizat pentru a executa. Aceste informații pot fi preluate byquerying sys.vizualizarea Catalogului de sistem dm_os_tasks și alăturarea acestuia cu vizualizările catalogului de sistem SYS.dm_os_workersand SYS. dm_exec_query_profiles, ca în scriptul T-SQL de mai jos:
SELECT OSTSK.scheduler_id, qp.node_id, qp.physical_operator_nameFROM sys.dm_os_tasks OSTSKLEFT JOIN sys.dm_os_workers OSWRK on OSTSK.worker_address=OSWRK.worker_addressLEFT JOIN sys.dm_exec_query_profiles qp on OSWRK.task_address=qp.task_addressWHERE OSTSK.session_id=58ORDER BY scheduler_id, node_id;
executând scriptul anterior în timp ce executați instrucțiunea SELECT cu planul ActualExecution activat, rezultatul ne va arăta că cei trei operatori de plan: sortare,filtrare și Index Cluster scanează cu ID-ul fiecărui operator afișat lângă acesta. Aceasta este executată de patru ori, folosind cele patru programatoare. Paralelism operatorcu ID egal cu 0 va fi executat o dată de către al patrulea planificator pentru a conecta toate contextele de execuție împreună în ultimul pas, așa cum se arată mai jos:
mai jos am adăugat valorile nod care corespund cu rezultatele interogării de mai suscu planul de execuție pentru a ajuta la ilustrarea în continuare.
pașii următori
- consultați următoarele resurse:
- specificarea gradului maxim de paralelism în SQL Server pentru o interogare.
- ce setare MAXDOP trebuie utilizată pentru SQL Server.
- cum se forțează un Plan de execuție paralel în SQL Server 2016.
- SQL Server 2016 DBCC CHECKDB cu MAXDOP
Ultima actualizare: 2018-03-30
despre autor
Ahmad Yaseen este un DBA SQL Server cu o diplomă de licență în inginerie informatică, precum și experiență de dezvoltare.net.Vizualizați toate sfaturile mele
Resurse conexe
- mai multe sfaturi pentru dezvoltatorii de baze de date…
