Proiect Disertatie Acs Pasare V Alice Lavinia 92586 (1) [604705]
UNIVERSITATEA POLITEHNICA BUCUREȘTI
FACULTATEA DE AUTOMATICĂ ȘI CALCULATOARE
DEPARTAMENTUL CALCULATOARE
MASTER ADMINISTRAREA BAZELOR DE DATE
Îmbunătă țirea performanțelor cererilor pentru
baze de date Microsoft SQL Server
Coordonato r științific:
Prof. dr. ing. Florin Rădulescu
Student: [anonimizat]
2017
2
Aș dori să mulțumesc domnului profesor universitar Florin Rădulescu pentru sfaturile pe care mi le -a
dat și pen tru suportul necondiționat dăruit pentru a putea finaliza această lucrare cu succes. Am descoperit la
dumnealui pasiunea de a preda și abilitatea de a fi atât un coordonator științific de excepție, cât și un model de
urmat în cariera profesională, motiv pe ntru care doresc să urmez un doctorat în domeniul Calculatoare și
Tehnologia Informației.
Ca și concluzie, menționez faptul că sunt recunoscătoare pentru impactul motivațional oferit și pentru tot timpul
alocat îndrumării pentru a termina acest proiect de dizertație.
3
Abstract
𝐴𝑙𝑖𝑐𝑒 −𝐿𝑎𝑣𝑖𝑛𝑖𝑎 𝑃𝑎𝑠ă𝑟𝑒1
Carierea unui administrator de baze de date se focusează în principal pe fixarea
problemelor de performanță. Problemele de performanță se manifestă în diferite moduri. De
obicei, companii le încearcă în primă fază să rezolve problemele prin adăugarea mai multor
resurse sau schimbarea serverelor. Acestea rezolvă rar problemele de performanță.
Majoritatea problemelor de performanță s e rezolvă prin investigarea cod ului care a fos scris
pentru a accesa datele și prin indentificarea unor schimbări care se pot face pentru a
îmbunătăți performanța sau chiar să ne rezolve problema cu care ne confruntăm. De multe ori
un timp de răspuns al aplicației mare are ca și cauză o proiectare neeficientă a unei cereri
folosite și al cărei execuție pune presiune pe resursele hardware sau blochează tot sistemul.
Pentru a evita problemele de performanță este indicat să folosim sisteme de monitorizare a
cererilor care sunt executate în aplicația noastră.
Îmbunătățir ea performanței unei cereri este o călătorie și nu o destinație în sine.
Cuvinte cheie: Microsoft SQL Server, cereri , indecși, optimizare, performanță, SQL Server Profiler ,
Performance Monitor , plan de execuție
1Facultatea de Automatică și Calculatoare , Master Administrarea Bazelor de Date , Universitatea Politehnica din București ,e−mail alice .pasare@gmail .com
4
Lista figurilor
Figure 1 – Triunghi depanare ………………………….. ………………………….. ………………………….. ………………………….. .. 9
Figură 2 – Performance Monitor ………………………….. ………………………….. ………………………….. ……………………… 21
Figură 3 – Counteri ………………………….. ………………………….. ………………………….. ………………………….. …………… 22
Figură 4 – Adăugare counteri ………………………….. ………………………….. ………………………….. ………………………….. 22
Figură 5 – Cerere1 ………………………….. ………………………….. ………………………….. ………………………….. …………….. 22
Figură 6 – Cerere2 ………………………….. ………………………….. ………………………….. ………………………….. …………….. 23
Figură 7 – Raport ………………………….. ………………………….. ………………………….. ………………………….. ……………… 23
Figură 8 – Eveniment SQL Server Profiler ………………………….. ………………………….. ………………………….. ………. 24
Figură 9 – Trace ………………………….. ………………………….. ………………………….. ………………………….. ……………….. 24
Figură 10 – SQLBatchStarting ………………………….. ………………………….. ………………………….. ………………………… 24
Figură 11 – ShowPlan All ………………………….. ………………………….. ………………………….. ………………………….. ….. 25
Figură 12 – Corelare cu Data Collector Set ………………………….. ………………………….. ………………………….. ……….. 25
Figură 13 – Corelare informații ………………………….. ………………………….. ………………………….. ……………………….. 25
Figură 14 – Diagrama bazei de date ………………………….. ………………………….. ………………………….. …………………. 27
Figură 15 – SHOWPLAN_TEXT ………………………….. ………………………….. ………………………….. ……………………. 27
Figură 16 – Definiție vedere ………………………….. ………………………….. ………………………….. ………………………….. .. 28
Figură 17 – Index ………………………….. ………………………….. ………………………….. ………………………….. ……………… 28
Figură 18 – Rezultate SHOWPLAN_TEXT ………………………….. ………………………….. ………………………….. ……… 28
Figură 19 – SHOWPLAN_ALL ………………………….. ………………………….. ………………………….. ………………………. 29
Figură 20 – SHOWPLAN_ALL Rezultat (1) ………………………….. ………………………….. ………………………….. …….. 29
Figură 21 – SHOWPLAN_ALL Rezultat (2) ………………………….. ………………………….. ………………………….. …….. 29
Figură 22 – SHO WPLAN_XML ………………………….. ………………………….. ………………………….. …………………….. 29
Figură 23 – Afișare plan de execuție în format grafic ………………………….. ………………………….. …………………….. 30
Figură 24 – Afișare plan de execuție în format XML: Pas (1) ………………………….. ………………………….. ………….. 30
Figură 25 – Afișare plan de execuție în format XML: Pas (2) ………………………….. ………………………….. ………….. 30
Figură 26 – Indecși lipsă ………………………….. ………………………….. ………………………….. ………………………….. ……. 31
Figură 27 – Plan de execuție estimat generat din interfață ………………………….. ………………………….. ………………. 31
Figură 28 – STATISTICS PROFILE ………………………….. ………………………….. ………………………….. ……………….. 32
Figură 29 – Rezultat STATISTICS PROFILE ………………………….. ………………………….. ………………………….. …… 32
Figură 30 – STATISTICS XML ………………………….. ………………………….. ………………………….. ……………………… 32
Figură 31 – Rezultat STATISTICS XML ………………………….. ………………………….. ………………………….. …………. 32
Figură 32 – Plan de execuție actual generat din interfață ………………………….. ………………………….. …………………. 33
Figură 33 – Instrumentul Database Engine Tuning Advisor ………………………….. ………………………….. …………….. 33
Figură 34 – Întoarcerea tuturor coloanelor ………………………….. ………………………….. ………………………….. ………… 39
Figură 35 – Statisticile client ………………………….. ………………………….. ………………………….. ………………………….. . 39
Figură 36 – Statisticile client după folosirea clauzei WHERE ………………………….. ………………………….. ………….. 40
Figură 37 – Citiri logice operație SELECT ………………………….. ………………………….. ………………………….. ……….. 40
Figură 38 – Clauza ORDER BY ………………………….. ………………………….. ………………………….. ……………………… 41
Figură 39 – Folosire funcție în clauza WHERE ………………………….. ………………………….. ………………………….. …. 41
Figură 40 – Folosire COUNT(*) ………………………….. ………………………….. ………………………….. ……………………… 42
Figură 41 – Folosirea clauzei EXISTS ………………………….. ………………………….. ………………………….. ……………… 42
Figură 42 – Struc tură tabel Person ………………………….. ………………………….. ………………………….. …………………… 42
Figură 43 – Indexul cu valorile sortate ………………………….. ………………………….. ………………………….. ……………… 43
Figură 44 – Clauza DISTINCT ………………………….. ………………………….. ………………………….. ……………………….. 43
Figură 45 – Rezervare memorie pentru clauza DISTINCT ………………………….. ………………………….. ………………. 43
Figură 46 – Comparație UNION cu UNION ALL ………………………….. ………………………….. ………………………….. 44
Figură 47 – Rezervare memorie pentru clauza UNION ………………………….. ………………………….. …………………… 44
Figură 48 – Instrucțiune INSERT ………………………….. ………………………….. ………………………….. …………………….. 45
Figură 49 – Instrucțiune INSERT în blocul BEGIN TRAN – COMMIT TRAN ………………………….. ……………… 45
Figură 50 – Spațiu folosit în jurnalul de tranzacții ………………………….. ………………………….. ………………………….. 46
Figură 51 – Conversia tipului de date din nvarchar(2) în char (2) ………………………….. ………………………….. ……… 47
Figură 52 – Citiri logice atunci când folosim declanșatoare ………………………….. ………………………….. …………….. 48
5
Lista tabelelor
Tabel 1 – Indicatori de performanță ………………………….. ………………………….. ………………………….. …………………. 13
Tabel 2 – Opțiuni Database Engine Tuning Advisor ………………………….. ………………………….. ……………………….. 34
Tabel 3 – Valori citiri logice în cazul fără indecși ………………………….. ………………………….. ………………………….. 37
Tabel 4 – Valori citiri logice în cazul în care există un index normal ………………………….. ………………………….. .. 38
Tabel 5 – Valori citiri logice în cazul în care există un index de acoperire ………………………….. ……………………… 38
Tabel 6 – Număr pagini citite pentru fiecare caz de indexare ………………………….. ………………………….. …………… 39
6
Cuprins
1. Introducere ––––––––––– ––––––––––– ––––––––––– ––––––––– 7
1.1. Aspecte introductive ––––––––––– ––––––––––– ––––––––––– ––– 7
1.2. Motiv ația alegerii temei ––––––––––– ––––––––––– ––––––––––– –- 7
1.3. Obiectiv ––––––––––– ––––––––––– ––––––––––– –––––––– 8
1.4. Structura lucrării ––––––––––– ––––––––––– ––––––––––– ––––- 8
2. Aspecte teoretice ––––––––––– ––––––––––– ––––––––––– ––––––- 8
2.1. Prezentarea contextului actual ––––––––––– ––––––––––– –––––––––– 8
2.2. Activity Monitor ––––––––––– ––––––––––– ––––––––––– –––– 10
2.3. Performance Monitor ––––––––––– ––––––––––– ––––––––––– –– 10
2.4. Obiectele de performanță SQL Server ––––––––––– ––––––––––– –––––– 11
2.5. SQL Server Profiler ––––––––––– ––––––––––– ––––––––––– ––- 14
3. Ustensile de optimizare ––––––––––– ––––––––––– ––––––––––– ––– 15
3.1. Resource Governor ––––––––––– ––––––––––– ––––––––––– ––– 16
3.2. Data collector ––––––––––– ––––––––––– ––––––––––– ––––– 17
3.3. SQLNEXUS ––––––––––– ––––––––––– ––––––––––– –––––- 19
3.4. Database Engine Tuning Advisor ––––––––––– ––––––––––– –––––––– 20
4. Aspecte practice ––––––––––– ––––––––––– ––––––––––– –––––– 21
4.1. Analiza performanței cererilor ––––––––––– ––––––––––– ––––––––– 21
4.2. Performance Monitor ––––––––––– ––––––––––– ––––––––––– –– 21
4.3. SQL Server Profiler ––––––––––– ––––––––––– ––––––––––– ––- 23
4.4. Analiza planurilor de execuție ––––––––––– ––––––––––– ––––––––– 26
4.4.1. Capturarea unui plan de execuție ––––––––––– ––––––––––– ––––––- 26
4.4.2. Planul de exe cuție estimat ––––––––––– ––––––––––– ––––––––– 27
4.4.3. Planul de execuție actual ––––––––––– ––––––––––– ––––––––––- 31
4.4.4. Avantajele folosirii planurilor de execuție în format XML ––––––––––– –––––––– 33
4.5. Database Engine Tuning Advisor ––––––––––– ––––––––––– –––––––– 33
4.6. Optimizarea performanței cererilor ––––––––––– ––––––––––– ––––––– 35
4.6.1. Situațiile fără indecși, cu indecși normali și cu indecși de acoperire ––––––––––– ––––- 36
4.6.2. SELECT * from NumeTabel vs SELECT col1,col2, …, coln from NumeTabel ––––––––––- 39
4.6.3. Clauza ORDER BY ––––––––––– ––––––––––– ––––––––––– –– 41
4.6.4. Funcții ––––––––––– ––––––––––– ––––––––––– –––––––- 41
4.6.5. Verificarea existenței unei an umite valori ––––––––––– ––––––––––– –––– 42
4.6.6. Folosirea datelor din index ––––––––––– ––––––––––– –––––––––– 42
4.6.7. Clauza DISTINCT ––––––––––– ––––––––––– ––––––––––– ––- 43
4.6.8. UNION și UNION ALL ––––––––––– ––––––––––– ––––––––––– – 44
4.6.9. Reducerea numărului de scrieri pe disc atunci când folosim INSERT ––––––––––– –––- 45
4.6.10 . Conversia tipului de date din nvarchar în char ––––––––––– ––––––––––– –– 46
4.6.11. Folosirea declanșatoarelor poate afecta performanța ––––––––––– –––––––––– 47
5. Concluzie ––––––––––– ––––––––––– ––––––––––– ––––––––– 49
6. Bibliografie ––––––––––– ––––––––––– ––––––––––– –––––––– 51
7
1. Introducere
1.1. Aspecte introductive
Un administrator de baze de date este de cele mai multe ori nevoit să plănuiască, creeze
și să întreți nă baze de date. Întrucât bazele de date sunt o parte critică a oricărei aplicații, este
important să se înțeleagă cum funcționează acestea, cum se creează, precum și care sunt
opțiunile obișnuite pentru acest proces. Dar cel mai important este să mențină o performanță
bună a bazei de date. Prin urmare evaluarea performanței baze de date în mod constant ne ajută
șă minimizăm timpii de răspuns și să maximizăm debitul de lucru, adică să avem o performanță
optimală. Traficul prin rețea eficient, performanța d iscurilor și folosirea procesorului sunt
punctele cheie ale performanței. Cred că administratorii bazelor de date aud destul de des
întrebări precum: ”De ce aplicația mea se deconectează?”, ”De ce se încarcă așa greu
rapoartele?”, ”De ce înainte mergea mai repede aceasta interogare și acum nu?” și multe altele.
În fiecare caz răspunsul poate fi diferit și pot exista foarte multe motive pentru aceste probleme
cum ar fi lipsa unui index, existența unui index care nu ajută la execuția mai rapidă a unei
intero gări, planul de execuție reflectă diferență între numărul rândurilor actuale și numărul
rândurilor estimate ceea ar putea însemna încărcarea unui număr mare de informații și lipsa
reorganizării indecșilor sau înnoirea statisticilor pe acel tabel, cum datel e sunt întoarse și care
sunt legăturile între tabelele din interogarea noastră sau costul estimat mare pentru operațiile
pe care vreau să le realizez.
Toate aceste informații joacă un rol important în viața unui administrator de baze de d ate, de
care trebu ie să aibă grijă pentru a menține performanța bazei de date la un nivel crescut astfel
încât să nu fie afectată producția.
Orice companie care folosește o aplicație dorește o performanță cât mai bună a acesteia.
Pentru a obține o îmbunătățire majoră trebui e să avem în vedere atât proiectarea aplicației și
aici includem atât partea fizică, cât și partea logică a bazei de date, modul de proiectarea a
cererilor necesare și proiectarea indecșilor necesari cererilor noastre. [1]
De cele mai multe ori, putem să av em un sistem foarte performant din punct de vedere hardware
și totuși să avem probleme cu cererile neperformant proiectate care sunt trimise către aplicație
și care pot consuma foarte mule resurse. Așadar, înainte de a investi banii într -un sistem foarte
costisitor, dar performant din punct de vedere hardware ar trebui să facem o analiză a
performanței bazei de date pentru a vedea cum putem optimiza cererile.
1.2. Motivația alegerii temei
Am ales această temă pentru că sunt interesată de modurile în care putem îmbunătăți
timpul de execuție al aplicației atunci când folosim anumite cereri pentru a ne returna datele
necesare. Am realizat faptul că acesta este un domeniu de interes pentru toți dezvoltatorii,
respectiv administratorii unei baze de date. Întotdeaun a va exista întrebarea: De ce aplicația
mea merge așa greu?. Lucrând în acest domeniu și confruntându -mă în mod constant cu
aceste probleme de performanță am decis că pot să realizez o analiză mai detaliată în această
8
lucrare a problemelor de performanță î ntâlnite, a instrumentelor folosite pentru rezolvarea
acestora și a posibilelor soluții pentru acestea.
1.3. Obiectiv
Obiectul cercetării este reprezentat de realizarea unei analize complexe a problemelor
de performanță ale cererilor , menționarea categoriilor și posibile soluții pentru acestea.
Urmăresc ca prin această lucrare să ilustrez cazurile practi ce cu care m -am întâlnit în medi ile
de producție și să le reduc la exemple concrete, făcând totodată comparație în funcție de
anumiți indicatori și cum ilustre ază aceștia o performanță mai bună a cererii noastre.
1.4. Structura lucrării
Pentru a realiza această lucrare am ales ca să scriu în prima parte despre partea teoretică,
iar în cea de -a doua să prezint activitatea practică realizată .
Primul capitol prezintă subiectele precum motivația alegerii temei, care este obiectivul
cercetării și ceea ce cuprinde această lucrare.
Cel de -al doilea capitol cuprinde aspectele teoretice privind pezentarea contextului
actual, metodologia de depanare, monitorizarea SQL Server și care sunt instrumentele pentru
a realiza această monitorizare.
Al patrulea capitol include partea practică, cererile analizate în varianta originală, modul
de optimizare și analizele de comparație între perfomanța interogării originale și cea a
interogă rii optimizate.
Cel de -al cincilea capitol include concluziile și sfaturile de abordare pentru a optimiza
performanța unei cereri.
Ultimul capitol conține bibliografia.
2. Aspecte teoretice
2.1. Prezentare a contextului actual
Putem analiza presiunea pe care o p oate crea o aplicație pe un SQL Server din două
puncte de vedere.
Primul nivel este reprezentat de modul de acționare al aplicației care folosește baza de date pe
fiecare resursă hardware și cât de solicitate sunt aceste resurse. Putem să analizăm sistemul în
momentul în care este o activi tate intensă pe baza de date. [2]
Al doilea nivel este să identificăm cererile care pun cea mai mare presiune pe sistem și cele
care sunt cel mai des folosite și al căror timp de execuție afectează în mod direct timpul de
răspuns al aplicației.
Microsoft SQL Server și sistemul de operare Windows oferă mai multe instrumente care vin
în ajutorul unui administrator de baze de date să poată vedea starea actuală a performanței
bazei lui de date și să își monitorizeze performanța pentru a vedea dacă au loc schimbări
importante în mediul de activitate. Printre acestea putem enumera analiza numărului de
9
utilizatori care folosesc Microsoft SQL Server și care se conectează la o instanță de SQL
Server, respectiv dacă performanța sistemu lui de gestiune a bazelor de date se modifică în
vreun fel în funcție de numărul de utilizatori conectați, analiza procedurilor stocate care se
execută încet mai încet decât de obicei, determinarea scenariilor unde putem îmbunătăți
performanța, spre exempl u să analizăm timpul de răspuns pentru anumite interogări folosite
mai frecvent de utilizatorii unei anumite aplicații și în funcție de acesta să încercăm să
îmbunătățim performanța interogării noastre prin determinarea schimbărilor necesare la
instrucțiun ile cuprinse în interogarea noastră sau schimbarea tipului de indecși existenți pe
tabele. [3]
Pentru a monitoriza performanța Microsoft SQL Server putem să includem ca și plan
următoarele: stabilirea unei baze pentru performanța noastră, adică putem să ne alegem ca și
scenariu de comparație valorile unde sistemul nostru se comporta în parametrii normali și nu
au existat probleme de performanță, să identificăm schimbările performanței odată cu trecerea
timpului și să observăm componentele afectate și să le o ptimizăm, compararea din punct de
vedere al performanței pe mai multe mașini client care folosesc aplicația ce realizează
conexiunea la baza de date de pe serverul nostru de baze de date, testarea arhitecturii bazei de
date folosind un mediu de test și nu configurarea acesteia direct în mediul de producție pentru
că nu știm problemele care pot apărea și prin testarea prealabilă putem să evităm aceste situații
de blocare a producției.
Triunghiul de depanare prezent mai jos este caracterizat prin etape ce evoluează de la cea
mai generală spre cea mai particulară. Aceste etape sunt o recomandare generală de abordare
a unei probleme. Nu există metode clare care ar trebui urmărite în momentul în care se face
depanarea, ci doar pași orientativi.
În momentul dep anării unei probleme necunoscute, este necesar ă colectarea unei cantități
mari de date generale. Folosind datele colectate trebuie să se realizeze o restrângere a
problemei la o singură bază de date. După acest pas, întrucât problema a fost particularizat ă,
datele oferite de procesul de monitorizare pot fi mai detaliate și să ajute la trecerea la
următoarele etape. Sistemul de monitorizare trebuie să ofere de asemenea suficiente date
pentru a putea să se treacă de la o etapă la cealaltă.
După ce sistemul de monitorizare a fost pornit, trebuie să se obțină informații despre
problemă analizând datele de la cel puțin una dintre soluțiile de monitorizare. Monitorizarea
și avansarea în triunghiul de depanare se face treptat pe perioada a mai multe minute sau
chiar ore.
Figure 1 – Triunghi depanare
10
Monitorizarea este o parte importan tă din administrarea unei baze de date întrucât SQL
Server oferă servicii într -un mediu dinamic. Datele folosite se schimbă, tipul de acces necesar
utilizat orilor precum și modul lor de a se conecta se schimbă de asemenea pe parcursul
timpului. SQL Server administrează automat resurse cum ar fi memoria sau spațiul pe disc,
însă monitorizarea permite administratorilor să identifice anumite probleme și să real izeze
astfel acțiunile necesare pentru a le repara. [4]
2.2. Activity Monitor
Activity Monitor este un tool oferit de SQL Server Management Studio care oferă informații
despre procesele curent e, lock -uri și gradul de folosir e al resurselor I/O. Acesta poate fi
folosit pentru a rezolva probleme de concurență cum ar fi rezolvarea unor deadlock -uri.
Activity Monitor conține următoarele părți:
Overview :
Conține o reprezentare grafică a gradului de folosire al procesorului,
numărul de task -uri în așteptare, gradul de folosire al resurselor I/O și
batch -uri procesate.
Processes :
Oferă informații despre conexiunile active curent realizate pe
respectivul server, cum ar fi login -ul care a realizat conexiunea, baza
de date la care s -a conectat .
Resource Waits :
Oferă informații generale despre timpul de așteptare după anumite
tipuri de resurse cum ar fi memoria, placa de rețea, etc.
Data File I/O:
Oferă informații despre cantitatea de date citi tă sau scrisă, precum și
timpul de răspuns pentru fiecare fișier din fi ecare bază de date.
Recent expensive Queries :
Conține o listă cu cele mai scumpe cereri din punctul de vedere al
resurselor consumate și oferă și informații despre acestea.
2.3. Performance Monitor
Performance Monitor este un tool grafic de monitorizare in clus în sistemul de operare. În
funcție de versiunea de Windows, numele acestuia variază ( poate fi găsit și sub numele
System Monitor). În plus, variază și locația unde acesta poate fi găsit, interfața acestuia și
elementele pe care le conține.
La mod ge neral, în acest tool pot fi găsite următoarele elemente:
Obiecte :
Reprezintă componente sau subcomponente importante ale
calculatorului.
Ele pot fi hardware ( de exemplu un hard disk ) sau software ( de
exemplu un proces ).
SQL Server adaugă în monito r obiecte proprii pentru a facilita
monitorizarea acestora.
Counter -e:
11
Oferă diferite informații despre un anumit obiect, ele fiind incluse în
acestea. De exemplu, pentru obiectul Process, se poate folosi un
counter pentru a afla timpul de execuție al a cestuia.
Sunt integrate în SQL Server și monitorizează constant obiectele
aferente lor, fie dacă sunt folosite în monitor sau nu. Așadar, folosirea
a mai multor countere nu influențează performanța serverului.
Instanțe:
Instanțele sunt mai multe copi i ale aceluiași obiect aplicate pe
elemente diferite ale serverului. De exemplu, dacă acesta conține două
procesoare, obiectul Processor va avea câte o instanță pentru fiecare. În
momentul folosiri monitorului, se poate alege dacă se vrea accesarea
informa țiilor individuale ale unei instanțe sau informațiile agregate ale
tuturor.
Reliability Monitor:
Un submonitor care calculează un indice de stabilitate al sistemului.
Se poate crea un grafic cu valoarea respectivului ind ice pe parcursul
timpului și obser va astfel când acesta scade. Acest index poate fi
afectat de instalarea sau ștergera unor aplicații, actualizări de drivere
sau de sistem de operare.
Resource Views:
Monitorul oferă direct, sau sub forma unui submonitor separat, o
reprezentare grafică în real-time a resurselor de procesor, hard disk,
rețea și memorie folosite de fiecare proces.
Data Collector Sets:
Un data collector set poate fi creat și folosit pentru a salva datele
aferente monitorizării. Se pot defini counterele ce se vrea a fi folosi te
și fișierul unde vor fi incluse datele iar administratorul poate observa
rezultatele monitorizării într -un moment de timp ulterior acesteia.
Există incluse în monitor template -uri c e pot fi folosite pentru a ușur a
crearea unui data collector set. În cazul în care este nevoie, se pot crea
template -uri suplimentare.
2.4. Obiectele de performanță SQL Server
SQL Server adaugă în Performance Monitor, la instalare, o serie de obiecte ce pot fi folosite
pentru a accesa informații despre instanțele SQL Server. Cele mai folosite obiecte sunt:
SQLServer:Access Methods
Oferă detalii despre modul de alocare al obiectelor ( de exemplu
numărul de căutări prin indecși sau numărul de paginii alocate
indecșilor și datelor ).
SQLServer:Buffer Manager
Oferă informa ții despre buffer -ele SQL Server, cum ar fi memoria
ocupată de acestea sau hit ratio -ul.
SQLServer:Databases
Oferă informații despre bazele de date, cum ar fi cantitatea de spațiu
liber în fișierele de log sau numărul de tranzacții active
12
SQLServer:G eneral Statistics
Oferă informații generale despre SQL Server, cum ar fi numărul de
utilizatori conectați.
SQLServer:Locks
Oferă informații despre lock -urile din SQL Server, cum ar fi durata
acestora sau numărul de deadlock -uri
SQLServer:Memory Ma nager
Oferă informații despre diferitele subcomponente SQL Server ( de
exemplu query optimizer ).
SQLServer:PlanCache
Oferă informații despre cache -ul folosit pentru a stoca obiecte în
cursul folosirii lor ( numărul de obiecte stocate, numărul de pagin i
stocate pentru aceste obiecte).
SQLServer:Transactions
Oferă informații suplimentare despre tranzacțiile active în SQL
Server, cum ar fi dimensiunea lor sau numărul de conflicte dintre
acestea.
Monitorizarea unei instanțe de SQL Server necesită analiz a unor elemente cheie ale
serverului. Eliminarea limitărilor fizice poate duce la o creștere semnificativă a performanței
serverului. Aceste limitări pot fi rezolvate prin îmbunătățirea structurii bazelor de date, a
indecșilor, a instrucțiunilor, prin fol osirea de componente hardware mai puternice sau prin
distribuirea muncii între mai multe servere.
Elementele cheie ce trebuie monitorizate sunt:
Hard disk-uri:
SQL Server folosește sistemul de operare pentru a realiza scrierea sau
citirea de date de pe dispozitive I/O. SQL Server decide când și cum se
va realiza scrierea/citirea datelor, iar sistemul de operare este
responsabil cu realizarea acestor operații.
Se pot folosi counter -i ”Current Disk Queue Length ” ( conține
numărul de operații I/O în aș teptare la un disc ) și ” % Disk Time ” (
conține procentul de folosire al un ui disc ) pentru a monitoriza un hard
disk. Valori mari identifică o problemă ce poate fi rezolvată în felul
următor:
Folosirea de hard disk -uri mai rapide
Mutarea anumitor pagini de date pe alte hard disk -uri
Dacă se folosește RAID, se pot adăuga hard disk -uri
suplimentare.
În cazul în care valorile counter -ilor ” Page reads/sec ” ( numărul de
pagini de bază de date citite pe secundă ) și ” Page writes/sec ” (
numărul de pagini de date scrise pe secundă ) sunt apropiate de limita
dispozitivelor , se poate îmbunătății structura bazei de date ( cum ar fi
folosirea de indecși ) sau creșterea capacități dispozitivelor I/O.
Memorie :
Este importantă monitorizarea periodică a cantității de memorie
ocupată de SQL Server. Verificați dacă aceasta se încadrează între
13
anumite limite considerate normale. Nici un proces, inclusiv SQL
Server, nu trebuie să consume prea multă memorie sau să fie limitat de
inexistența unei cantități suficiente de memorie.
Mai jos se poate observa o listă cu cei mai importanți counter -i de
memorie și ce probleme indică aceștia:
Conter Descriere Recomandări
Memory –
Available Bytes Cantitatea de memorie
liberă disponibilă Valori mici indică o cantitate prea mic ă
de memorie în server sau o aplicație care
nu eliberează memoria.
Process –
Working Set Cantitatea de memorie
ocupată de un proces Dacă acest număr este constant mai mic
decât cantitatea de memorie configurată
prin min server memory și max server
memory , este posibil ca serverul să fie
configurat să ocupe prea multă memorie.
SQL Server:
Buffer Manager
– Buffer Cache
Hit Ration Indică numărul de pagini
găsite în cache, nefiind
astfel necesară accesarea
dispozitivelor I/O. Se adaugă memorie până când acea stă
valoare este peste 90%
Server –
Memory
Manager: Total
Server Memory Cantitatea totală de
memorie virtuală ocupată
de server În cazul în care valoarea acestui counter
este mare în mod constant, trebuie luat în
calcul adăugarea de memorie
suplimentară.
Tabel 1 – Indicatori de performanță
Procesor :
Este importantă monitorizarea periodică a gradului de utilizare al
procesorului pentru a verifica dacă gradul de folosire al procesorului se
află între limite normale. Un grad mare de folosire al procesorului
poate indica faptul că este necesară cumpărarea unui nou procesor mai
puternic sau folosirea mai multor procesoare. În plus, gradul de
folosire al procesorului este influențat și de aplicații care se execută
ineficient
Principali i counter -i sunt:
Processor – % Processor Time – Timpul pierdut cu execuția
thread -urilor. Valori peste 80 -90% indică nevoia unui upgrade
de procesor
Process – % Processor Time – Timpul de procesor ocupat cu
execuția unu thread din procesul de SQL Server . Acesta poate
fi folosit pentru a afla influența SQL Server la gradul de
folosire al procesorului.
14
2.5. SQL Server Profiler
SQL Server Profiler este un tool grafic ce poate fi folosit pentru monitorizarea unei
instanțe de SQL Server Se pot captura și salva d atele unei monitorizări pentru a fi analizate
ulterior. De exemplu, se poate monitoriza un mediu de producție pentru a vedea ce stored
procedure afectează performanța prin o execuție înceată.
SQL Server Profiler arată cum sunt executate intern instrucțiuni le, permițând
administratorilor să vadă exact ce instrucțiuni T -SQL sunt primite de server și cum accesează
acesta datele pentru a întoarce rezultatul.
Folosind SQL Server Profiler se pot realiza următoarele lucruri:
Crearea unui trace. Un trace este repr ezentarea activității monitorizate pe o
perioadă de timp. Există template -uri prin care se pot crea anumite tipuri de
trace -uri ( variază în funcție de activitatea monitorizată ).
Observarea rezultatelor unui trace în timp ce acesta se execută.
Stocarea rezultatelor unui trace într -un tabel sau într -un fișier pentru a fi
analizate ulterior.
Pornirea, oprirea temporară sau permanentă a unui trace
Pentru a putea folosi SQL Server Profiler, este necesară cunoașterea termenilor caracteristici
acestuia:
Un eveniment este o acțiune generată în cadrul unei instanțe SQL Server .
O clasă de evenimente este un tip de eveniment ce poate fi folosit pentru a
specifica dorința monitorizării respectivului eveniment
O coloană de date este un atribut al unei clase de e venimente. Coloanele
disponibile variază în funcție de clasă.
Un template conține un set de configurații standard pentru un trace și una sau
mai multe clase de evenimente.
Folosim SQL Server Profiler pentru a monitoriza doar evenimentele care prezintă int eres.
Dacă serverul conține prea multă activitate într -o anumită perioadă, folosiți filtre pentru a
restrânge datele colectate în trace. Monitorizarea a prea multor evenimente adaugă overhead
la server și poate duce la crearea unor fișiere de trace foarte mari, în special atunci când
monitorizarea se realizează pe o perioadă mai mare de timp. Activitatea monitorizată de un
trace variază în funcție de clasa de eveniment, coloana de date și filtrele folosite.
În momentul în care folosim SQL Server Profiler p entru a crea un trace, trebuie specificate o
serie de opțiuni pentru a defini activitatea monitorizată și unde se vor stoca rezultatele
monitorizării.
SQL Server Profiler oferă o serie de template -uri predefinite care permit configurarea ușoară
a eveniment elor care sunt necesar a fi monitorizate pentru anumite activități. Aceste
template -uri pot fi chiar trace -ul ce se va realiza sau punctul de plecare în configurarea unui
trace. În cazul în care template -urile predefinite nu sunt suficiente, SQL Server Pro filer oferă
posibilitatea definirii unora noi.
Este necesară salvarea rezultatelor unui trace în următoarele situații:
Analizarea acestora se va face ulterior.
Se vrea folosirea lor ca imput la Database Engine Tunning Advisor, acesta l a
rândul lui oferi nd recomandare pe baza activității din respectivul trace.
Salvarea se poate face sub formă de tabel sau sub formă de fișier.
15
În momentul creării respectivului trace se poate specifica timpul de oprire al acestuia,
putându -se monitoriza astfel serverul pe o perioadă fixă de timp ( de exemplu în perioada de
folosire maximă a acestuia ).
Chiar dacă SQL Server este optimizat să facă față unui volum mare de modificări de date, nu
este recomandată salvarea unui Trace în aceiași instanță ca cea monitorizată. Cel mai eficient
este salvarea ei sub forma unui fișier ce va fi importat ulterior în baza de date.
3. Ustensile de optimizare
Optimizarea SQL Server este alcătuită și din o etapă de monitorizare a serverului, întrucât
este necesară detectarea zonelor care nu oferă o performanță optimă și care trebuie
îmbunătățite. De aceea, printre ustensilele folosite în cadrul unui proces de optimizare se
regăsesc și o parte din cele de monitorizare.
Principalele ustensile folosite sunt:
SQL Server Profiler :
Această unealt ă grafică descrisă mai sus permite capturarea de date și
salvarea lor pentru a fi analizate ulterior. Aceste date pot fi folosite ca
intrare pentru Database Engine Tuning Advisor sau sincronizate cu
rezultatele unui collector set din Performance and Reliab ility Monitor .
Database Engine Tuning Advisor :
Această unealtă poate fi folosită pentru a analiza impactul asupra
performanței a unui workload ( acesta poate fi capturat cu SQL Server
Profiler ). După analizare el oferă recomandări despre îmbunătățirea
structurii indecșilor sau despre modul de partiționare a tabelelor.
Rapoarte SQL Server Management :
SQL Server Management Studio oferă informații despre diferite
obiecte sub forma unor rapoarte disponibile în cadrul meniului de click
dreapta. Acestea sun t folosite pentru a agrega informațiile cele mai
accesate despre server sau despre respectivele obiecte și reprezintă o
sursă importantă de informații
Resource Governor :
Această unealtă permite controlarea resursele consumate pentru
realizarea anumitor f uncționalități în SQL Server .
Data Collectori :
Aceștia sunt obiecte instalate pe SQL Server ce pot fi executate non
stop sau programate folosind joburi SQL Server Agent pentru a realiza
colectarea a diverse tipuri de date.
SQL Nexus
Această unealtă ne aj ută să identificăm care este cauza problemelor de
performanță SQL Server. El încarcă și analizează performanța datelor
colectate cu ajutorul instrumentului PSSDIAG. Scade considerabil
timpul pe care îl petrecem manual pentru analiza datelor.
PSSDIAG/SQLDI AG Manager este o interfață grafică care ne oferă
posibilitatea de a colecta date pentru SQL Server folosind mototrul de
colectare SQLDIAG. [7]
16
3.1. Resource Governor
Anumite instrucțiuni consumă mai multe resurse decât alte versiune ale acestora care
întorc acela și rezultat. De aceea o parte importantă în optimizare este dată de
îmbunătățirea performanței instrucțiunilor. Query Optimizer nu poate, oricât de
inteligent ar fi, să ofere cea mai eficientă care de a întoarce respectivele date. El
implementează cel mai eficient plan de execuție însă este limitat de instrucțiunea de la
care a plecat analiza acestuia.
Primul pas în cadrul procesului de optimizare este dat de analizarea instrucțiunii în
sine. Acest pas presupune analizarea codului, a planului de execuție și a resurselor
folosite.
Mediul de execuție al instrucțiunii este alcătuit din mai multe elemente, cum ar fi
tabele, indecși, aplicații care se execută concurent pe server, configurații ale bazei de
date sau ale serverului, precum și cantitatea de date din obiectele accesate. Oricare
dintre aceste elemente poate afecta nivelul de performanță al unei instrucțiuni.
Optimizarea instrucțiunilor nu este o știință exactă, un rol important avându -l și
experiența administratorului. În loc de a exista anumiți pași exacți ce pot fi urmați,
există o serie de recomandări ce trebuie luate în considerare în procesul de analizare a
instrucțiunii:
Testați mai multe versiune ale unei instrucțiuni. Query Optimizer poate
produce planuri de execuție diferite substanțial pentru instrucțiuni echivalente
logic. O modificare poate să ducă de fapt la un nivel de performanță mai prost
decât cel anterior.
Folosiți subquery -uri corelate pentru a îmbunătăți performanța. De exemplu,
subquery -urile sunt îndeosebit de utile în momen tul în care se realizează un
join cu un anumit tabel doar pentru a verifica existența anumitor rânduri. În
acest caz se recomandă înlocuirea instrucțiunii cu un subquery ce folosește
operatorul EXISTS.
Evitați folosirea de funcții scalare definite de util izator în cadrul condițiilor
WHERE pentru că acestea se vor executa pentru fiecare rând pe care condiția
trebuie să îl valideze. Încercați folosirea de variabile sau subquery -uri pentru
a înlocui respectiva funcție.
Folosiți funcții table -valued definit e de utilizator. Spre deosebire de cele
scalare, SQL Server va evalua respectiva funcție o singură dată ( cu excepția
cazului în care se folosesc operatorii APPLY ), economisind astfel resurse
utile.
Resource Governor folosește în de fapt funcționări lui mai multe elemente:
Pool-uri de resurse :
Reprezintă resursele fizice ale unui anumit server. Pentru fiecare tip de
resursă ( memorie sau procesor ) se va specifica valoarea minimă și
maximă disponibilă pentru respectivul pool.
Valoarea minimă de resurs e garantează că indiferent de gradul de
folosire al serverului vor fi disponibile procentul respectiv din resurse.
Datorită acestui lucru, suma valorilor minime a tuturor resurselor nu
poate depășii 100%. Valoarea minimă nu va fi alocată dacă nu este
necesară, evitându -se astfel pierderea inutilă de resurse.
17
Valoarea maximă reprezintă o limită superioară a resurselor
consumate. De exemplu, să presupunem că serverul este nefolosit. O
conexiune se realizează și acesta este asociată unui pool. Ea primește
automat resursele necesare. Întrucât este singura conexiune, dacă ea
are nevoie de 100% procesor, îl va primi. Dacă are nevoie de o valoare
mai mică decât minimul, va primi valoarea mai mică. Să presupunem
că obține 100% procesor. Dacă o altă conexiune se co nectează si are
minim 30% procesor și are nevoie de aceasta, automat el va fi alocat
iar cantitatea de procesor ocupată de primul va scădea la o valoare mai
mare sau egală cu minimul.
Grupuri de workload :
Reprezintă un container folosit pentru gruparea tuturor conexiunilor.
Fiecare grup este asociat cu un workpool iar conexiunile din
respectivul grup vor folosi resursele asociate pool -ului. Distribuirea
resurselor între conexiunile din același grup nu este controlată de
Resource Governor, ea fiind reali zată asemănător modului de
distribuire realizat la nivel de server în cazul nefolosirii acestei
ustensile.
Există un grup default ce va fi folosit în momentul în care o
conexiune nu poate fi alocată altui grup. Grupul internal este creat și
folosit doar de SQL Server. El nu poate fi modificat.
Funcții de clasificare :
Funcțiile de clasificare sunt cele responsabile cu alocarea unei
conexiuni unui anumit grup.
Figură 1 – Concepte Resource Governor
3.2. Data collector
Data collecto r permite colectarea a multor tipuri de date ( nu numai de performanță
cum permite SQL Server Profiler ) într -o locație centrală reprezentată sub forma unei baze de
18
date numită Management Data Warehouse . Aceștia pot fi executați non stop sau pot fi
program ați folosind SQL Server Agent. În ceea ce privește modul lor de implementare, ei
sunt în esență pachete SSIS predefinite și preconfigurate. Nu este necesară cunoașterea de
elemente SSIS în momentul folosirii lor.
Mai jos se pot observa elementele folosite în cadrul procesului de colectare a datelor:
țintă:
O instanță de SQL Server sau un obiect din baza de date . În funcție de
țintă, tipul acesteia are caracteristici diferite ( de exemplu un server are
alte proprietăți față de un tabel ).
colector :
Un pachet SSIS capabil să extragă datele și să le încarce în baza de
date Management Data Warehouse. El folosește providere pentru a
accesa respectivele date. Aceștia nu sunt incluși în pachet ci sunt
externi acestuia.
Aceștia pot fi de următoarele tipuri:
• T-SQL Query :
• Permite definirea unei instruc țiuni SELECT al cărui
rezultat să reprezinte datele colectate. Rezultatul este
stocat într -un tabel din baza de date Management Data
Warehouse . Tabelul este creat în funcție de rezultat și
de aceea coloanele tre buie să aibă un nume și să nu fie
de tipul image, text, ntext sau XML
• SQL Trace :
• Permite capturarea datelor de performanță disponibile
și în SQL Server Profiler. Rezultatul este stocat într -un
tabel de trace.
• Performance Counter :
• Permite capturarea anu mitor date de performanță.
Acestea vor fi stocate în tabelul
snapshots.performance_counters din baza de date
Management Data Warehouse.
• Query Activity :
• Accesează informații din view -urile de sistem
sys.dm_exec_requests , sys.dm_exec_sessions și
sys.dm_ex ec_query_stats .
Collection set:
O grupare de mai mulți colectori cu care se poate interacționa prin
cadrul interfeței SSMS. Ele pot fi aplicate pe un target doar dacă toți
colectori din set pot accesa date din respectivul target. Set -ul poate fi
executat de job -uri SQL Server Agent. Datele sunt încărcate în funcție
de modul de colectare.
Mod de colectare :
Reprezintă modul de colectare a datelor. Există două moduri de
colectare:
• cached : Colectarea datelor este separată de încărcarea lor în
baza de date. Datele sunt colectate non stop și salvate în cache,
19
iar la un anumit interval de timp ele sunt trecute din cache în
baza de date. Acest ultim job este programat folosind SQL
Server Agent.
• non-cached : Colectarea și încărcarea datelor se realizează în
acela și job. Mai întâi datele sunt colectate iar apoi ele sunt
imediat încărcate în baza de date.
Management Data Warehouse :
Baza de date în care sunt stocate datele.
Există trei roluri ce pot fi folosite pentru a oferi acces la collectori și la datele din aceștia.
Aceste roluri sunt create în baza de date Management Data Warehouse. Adăugarea unui login
într-un rol va duce la crearea unui utilizator pentru el în această bază de date:
mdw_reader : Utilizatorii din acest rol vor putea doar citi datele deja col ectate.
mdw_writer : Utilizatori acestui rol pot doar folosi colectori pentru a captura
date. El poate de asemenea accesa datele.
mdw_admin : Utilizatorii acestui rol pot citi și captura date. În plus ei pot și
șterge date deja capturate. Contul corespunz ător SQL Server Agent va trebui
să fie admin pentru a putea funcționa corect. De asemenea, administratori nu
au acces implicit la această bază de date. Ei se pot însă adăuga în oricare din
cele trei roluri. Utilizatorul care realizează configurarea proces ului de
colectare este adăugat implicit ca administrator în această bază de date.
Figură 2 – Data Collector
3.3. SQLNEXUS
Acest instrument poate fi folosit pentru a analiza performanța SQL Server precum blocarea,
timpilor de aștepta re și trasele SQL. Scenariile unde acest instrument poate fi folosit sunt:
Probleme de creștere ale utilizării procesorului intermitente pe mașina de SQL Server
Degradarea performanței în timpul unor ore specifice
Rezolvarea problemelor blocării în lanț în SQL Server
Analiza blocajelor generale de performanță în SQL Server folosind statisticile de
așteptare
Identificarea interogărilor de top care folosesc procesorul, durata, citirile și scrierile în
timpul unei anumite perioade.
20
Identificarea bazelor de d ate si sesiunilor , care sunt mari consumatoare de procesor
pe o anumită instanță de SQL Sever, spre exemplu în timpul unui anumit interval de
timp specific (să zicem, 09:00 – 17:00)
Depanarea problemelor de performanță, în czul în care avem anumite instru cțiuni care
se execută în fiecare noapte în timpul unui anumit interval de timp (să zicem 02:00 –
4:00)
Compararea performanțelor SQL server pentru diferite teste într -un scenariu de
laborator de testare –Spre exemplu putem să avem probleme de performanță ș de
scalabilitate atunci când sunt 150 de utiliatori conectați, dar nu și când sunt 50.
3.4. Database Engine Tuning Advisor
Database Engine Tuning Advisor pune la dispoziția utilizatorului două interfețe:
O interfață independentă, grafică
Un utilitar în lin ie de comandă ( dta.exe ) care oferă aceiași funcționalitate
Database Engine Tuning Advisor realizează testarea folosind workload -uri. Acestea sunt
mulțimi de instrucțiuni Transact -SQL executate asupra bazei de date care se vrea testată.
Workload -ul poate fi reprezentat sub forma unui fișier .sql, fișiere de trace generate de SQL,
sau un tabel de informații de trace, generate tot de SQL Profiler
După analizarea efectelor workload -ului asupra bazei de date, Database Engine Tuning
Advisor oferă recomandări. Aceste recomandări includ schimbări sugerate pentru baza de
date cum ar fi indecși noi, indecși care ar trebui șterși și în funcție de opțiunile alese la
execuția testului, și recomandări pentru partiții. Aceste recomandări sunt oferite sub forma
unei mulț imi de instrucțiuni T -SQL care ar avea ca efect implementarea recomandărilor.
Aceste instrucțiuni pot fi vizualizate și salvate pentru o folosire ulterioară sau pot fi executate
imediat.
Figură 3 – Instrumentul Database Engine T uning Advisor
21
4. Aspecte practice
4.1. Analiza performanței cererilor
O cauză a performanței scăzute a cererilor este volumul bazei de date mare folosite de
aplicație, respectiv natura cererilor care se execută. Pentru a realiza această analiză de
performanță este foarte important să examinăm volumul de date prelucrat de apl icație și să
identificăm cererile care cauzează suprasolicitarea resurselor și implicit încetinirea timpului de
răspuns. Pentru a face acest lucr u putem să folosim in strumentele Performance Monitor, SQL
Profiler și Database Engine Tuning Advisor.
4.2. Performan ce Monitor
Pentru a monitoriza activitatea SQL Server putem să folosim Performance Monitor, un
program care va colecta date despre sistemul nostru în timp ce baza de date este folosită în
mediul de producție.
1. Performance Monitor poate fi găsit în Cont rol Panel , System and Security ,
Administrative Tools .
Figură 2 – Performance Monitor
Am creat un nou Data Collector Set ce va fi folosit pentru a s alva rezultatele monitorizării și
am ales următorii counteri pentru a mon itoriza activitatea serverului.
Obiect Counter Instanță
Memory Pages/sec
Memory Page Faults/sec
Process % Processor Time sqlservr
SQLServer:Buffer Manager Buffer cache hit ratio
22
SQLServer:Locks Lock Requests/sec _Total
SQLServer:Locks Lock Timeouts/sec _Total
SQLServer:Locks Lock Wait Time (ms) _Total
SQLServer:Locks Number of Deadlocks/sec _Total
SQLServer:Memory Manager Total Server Memory (KB)
Figură 3 – Counter i
Figură 4 – Adăugare counteri
Am executat următoarele cereri Cerere1 .sql și Cerere2 .sql pentru a pune presiune pe sistem
și am așteptat ca ambele fișiere să termine execuția.
Figură 5 – Cerere1
23
Figură 6 – Cerere2
Putem să observăm graficul aferent counter -ilor, precum și valorile minime, maxime și medii
pentru fiecare.
Figură 7 – Raport
4.3. SQL Server Profiler
SQL Server Profiler este un instrument care ne ajută să vedem cum sunt executate cererile
noastre int ern și care date sunt accesate și în ce mod. De asemenea putem vedea instrucțiunile
executate la anumite momente în timp.
Am creat o urmă cu ajutorul SQL Server Profiler și am selectat următoarele evenimente pentru
a monitoriza activitatea pe o perioadă d e timp :
Cateogie Eveniment
Locks Lock:Timeout
Performance Showplan All
24
Scans Scan:Started
Stored Procedures SP:CacheHit
Stored Procedures SP:Starting
TSQL SQL:StmtStarting
Transactions SQLTransaction
Figură 8 – Eveniment S QL Server Profiler
Figură 9 – Trace
În rezultatul trace -ului din SQL Server Profiler , căutat dupa Update Production pentru a mă
poziționa pe prima instrucțiune de update a tabelului Production corespunzătoare unui
eveniment de t ipul SQL:StmtStarting și se poate observa instrucțiunea completă care urmează
a fi executată.
Figură 10 – SQLBatchStarting
25
Dacă selectăm evenimentul ShowPlan All vom observa planul de execuț ie al instrucțiunii de
mai sus și de asemenea folosirea indecșilor.
Figură 11 – ShowPlan All
Am realizat deasemenea și corelarea informațiilor din trace cu cele din data collector set. Pentru
acest lucru, din meniul File, am folosit intrarea Import Performance Data și am importat
rezultatul collector set -ului.
Figură 12 – Corelare cu Data Collector Set
În trace, în urma corelării cu data collector set dacă vom selecta un eveniment, vom observa că
nivelul de performanță din timpul executăr ii acestuia este afișat. Dacă realizăm un click oriunde
în grafic putem să observăm că evenimentul ce s -a realizat în timpul corespunzător este
evidențiat. Această analiză ne ajută să găsim cererile care consumă cele mai multe resurse sau
care nu se execut ă cu success din cauza altor cereri.
Figură 13 – Corelare informații
26
4.4. Analiza planurilor de execuție
Dacă avem o cerere specifică care nu se execută rapid, o parte importantă din investigarea
problemei de performanță este să n e uităm la planul de execuție al cererii și să vedem dacă
există indicații cu privire la identificarea acestei probleme. Este important să înțelegem ce
planul de execuție al cererii ne poate spune și foarte important ce nu ne poate spune.
Transact – SQL, p rescurtat T -SQL este un limbaj declarativ, ne spune ce să facem, dar nu
și cum să facem o cerere. Sunt excepții precum hints, ghiduri de plan și noi funcționalități
precum indecși columnstore care sunt sensibili la construcția de cereri.
Un plan de execuți e ne ajută să aflăm cum rezultatele sunt regăsite și cum potențialele
probleme pot fi optimizate.
Planurile cererilor ne ajută să evaluăm și să abordăm ariile cele mai impresionante care
necesită îmbunătățire, acordând prioritate printe instrucțiunile dintr-un bloc de instucțiuni și
operațiilor dintr -o instrucțiune specifică.
Planurile de execuție ne ajută să descoperim anumite trăsături precum costurile estimate
pentru operator , indecșii care sunt accesați, cum sunt folosiți indecși, operatorii (iterat ori, de
exemplu), rândurile accesate de fiecare operator, execuțiile pentru fiecare operator, ordinea în
care se execută operațiile de tip join și multe altele.
Planul de execuție al unei cereri nu ne oferă toate informațiile și anume, acesta nu ne spune
care sunt blocările necesare pe care trebuie să le facem asupra resurselor (numite în engleză
locks), care sunt statisticile de așteptare (numite în engleză wait statistics), dacă datele sunt în
cache sau nu, nu ne arată comparația între costul actual și ce l estimat, nu ne arată dacă unele
operații sunt ascunse sau expuse în moduri neașteptate și nu reprezintă o înlocuire a
instrumentelor SET STATISTICS IO (oferă informații despre activitatea logică și fizică a
discurilor generată de instrucțiunile Transact -SQL) și SET STATISTICS TIME ( arată timpul
necesar pentru a parsa, compila și executa instrucțiunea).
4.4.1. Captu rarea unui plan de execuție
Sunt multiple tehnici de a obține planul de execuție al unei cereri pe care le voi enumera mai
jos:
1) Planul estima t ne arată planul compilat și înseamnă că nu se execută interogarea în
sine și nu întoarcem rezultate sau modificăm date. Technicile pentru a obține acest
plan estimat sunt:
a) SET SHOWPLAN_TEXT
b) SET SHOWPLAN_ALL
c) SET SHOWPLAN_XML
d) SHOWPLAN grafic
e) Sys.dm_exec_q uery_plan, sys.dm_exec_text_query_plan
2) Capturarea planului actual ne întoarce atât planul estimat, cât și statisticile din
momentul în care interograrea a fost execută. Technicile pentru obținerea acestui
plan sunt:
a) SET STATISTICS PROFILE
b) SET STATISTICS XML
c) SHOWPLAN grafic
27
Pentru a testa modurile de capturare a planurilor de executie, voi folosi o baza de date care
are diagrama de mai jos:
Figură 14 – Diagrama bazei de date
4.4.2. Planul de execuție estimat
a) SET SHOWPLAN_TEXT
Pentru a vedea planul estimat pentru o anumită cerere am ales să testăm o interogare a unei
vederi (view în limba engleză) și să o filtrăm după o anumită coloană. Înainte de a executa
interogarea trebuie să setăm opțiunea SHOWPLAN_TEXT ca fiind activă, să executăm
instrucțiunea și apoi să setăm opțiunea din nou ca fiind inactivă. [5]
Figură 15 – SHOWPLAN_TEXT
Definiția vederii se poate observa mai jos:
28
Figură 16 – Definiție vedere
Această vedere are 3 ta bele de bază și legătura dintre primul și al treilea tabel se face prin
verificarea unei condiții de egalitate, prin același tip de condiție realizându -se și legătura
dintre al doilea tabel și primul tabel.
Mai jos se găsește rezultatul executării instrucț iunilor de mai sus și anume definiția cererii
noastre, respectiv operațiile care se realizează atunci când o executăm și anume nested loops,
scanarea indexului clustered care se poate vedea mai jos în structura grafică a tabelului:
Figură 17 – Index
,
filtrarea rezultatului folosind clauza WHERE, respectiv căutarea în indecșii aferenți
obiectelor folosite.
Figură 18 – Rezultate SHOWPLAN_TEXT
b) SET SHOWPLAN_ALL
Această funcționalitate de capturare a pl anului estimat de execuție, cât și cea de mai sus sunt
depreciate și nu se mai recomandă folosirea lor.
29
Figură 19 – SHOWPLAN_ALL
Rezultatul folosirii acestei instrucțiuni se găsește mai jos și oferă mai multe informații despre
planul de execuție, spre deosebire de instrucțiunea SHOWPLAN_TEXT. Pe lângă tipurile de
operații realizate întâlnim și numărul de operații fizice necesare, numărul de operații logice,
numărul de rânduri estimate , respectiv numărul de execuții estimat.
Figură 20 – SHOWPLAN_ALL Rezultat (1)
Figură 21 – SHOWPLAN_ALL Rezultat (2)
c) SHOWPLAN_XML
Pentru a captura planul de execuție estimat în modul XML pentru cererea noastră folosită ca
test trebuie să ex ecutăm instrucțiunile de mai jos, unde setăm SHOWPLAN_XML ca fiind
pornit, executăm interogarea și oprim apoi SHOWPLAN_XML.
Figură 22 – SHOWPLAN_XML
Metoda de afișare a planului de execuție acum poate fi fie XML, fie în format gr afic, după
cum se poate observa în imaginea următoare:
30
Figură 23 – Afișare plan de execuție în format grafic
Acest plan de execuție poate fi de asemenea schimbat din afișare în mod grafic în format
XML:
Figură 24 – Afișare plan de execuție în format XML: Pas (1)
Figură 25 – Afișare plan de execuție în format XML: Pas (2)
Un avantaj al planului în format XML este că putem să realizăm căutări după anumite cuvinte
cheie. Spre exem plu, am ales să caut după cuvântul missing pentru a vedea toți indecșii care
lipsesc și care m -ar putea ajuta să îmbunătățesc performanța de execuție a cererii mele.
31
Figură 26 – Indecși lipsă
Acest index are un impact destul de mare petru peformanța inteorgării noastre, de până la
93%.
d) SHOWPLAN estimat grafic
Pentru a captura planul de execuție estimat putem să folosim și interfața grafică oferită de
Microsoft SQL Server și anume selectăm cererea noastră și apoi apăsăm pe Que ry și pe
Display Estimated Execution Plan, fie combinația de taste CTRL+L.
Figură 27 – Plan de execuție estimat generat din interfață
4.4.3. Planul de execuție actual
a) STATISTICS PROFILE
De data aceasta vom obține și planul de execuție actual cu statisticile din timpul rulării și
rezultatul propriu -zis al cererii noastre. Rezultatul acestei opțiuni este similar cu cel
SHOWPLAN_ALL, cu o singură excepție și anume că de data aceasta putem sa vedem în
rezultat atât numărul de rând uri estimate pentru fiecare operație, cât și numărul de rânduri
întoarse pentru fiecare operație. Aceste 2 informații ne ajută pentru că putem să detectăm
problemele de estimare a cardinalității.
32
Figură 28 – STATISTICS PROFILE
Figură 29 – Rezultat STATISTICS PROFILE
b) STATISTICS XML
Un alt mod de a afișa planul de execuție actual este folosirea STATISTICS XML unde
vom vedea atât numărul de rânduri actuale, cât și numărul de rânduri estimate.
Figură 30 – STATISTICS XML
Rezultatul executării acestei instrucțiuni se poate observa mai jos, unde putem analiza de
asemenea, diferențele dintre planul de execuție estimat și cel actual pentru instrucțiunea
noastră SELECT:
Figură 31 – Rezultat STATISTICS XML
c) SHOWPLAN actual grafic
33
Pentru a genera planul actual în mod grafic procedăm la fel ca în cazul generării planului
estimat, numai că acum selectăm Include Actual Execution Plan sau folosim combinația de
taste CTRL+M.
Figură 32 – Plan de execuție actual generat din interfață
4.4.4. Avantajele folosirii planurilor de execuție în format XML
Planurile de execuție ale cererilor în format XML poate par mai greu de folosit decât
celel alte formate de tip text sau tabel, dar prezintă câteva avantaje care pot ajuta la analiza
acestora:
a) Putem să capturăm planul în format XML și atunci când avem spre exemplu o
performanță bună a execuției unei cereri, să obligăm sistemul să folosească acela și
plan de execuție pentru respectiva cerere.
b) Aceste planuri de execuție pot fi procesate prin Xpath și Xquery.
c) În cazul în care vrem să salvăm rezultatele instrucțiunilor SHOWPLAN_XML sau
STATISTICS_XML în format cu extensia .sqlplan putem să facem acest lucru ușor și
putem deschide aceste fișiere în SQL Server Management Studio (interfața grafică de
asministrare a SQL Server) atât în format grafic, cât și în format XML.
4.5. Database Engine Tuning Advisor
Pentru a testa acest instrument am încărcat un volum d e instrucțiuni cu scopul de a fi
analizate și pentru a vedea ce recomandări ne va da acesta.
Figură 33 – Instrumentul Database Engine Tuning Advisor
34
Pentru a încărca un volum de instrucțiuni care urmează să fie procesat și anali zat am folosit
fișier ul Querries.sql prezent mai jos și am ales baza de date la care se face conexiunea.
Figură 4 – Fișierul .sql
Am ales opțiunile de executare a testelor folosind pagina Tuning Options. Cele pe care le -am
ales pot fi observate în tabelul de mai jos.
Opțiune Valoare
Limit tunning time NU
Physical Design Structures
to use in database Indexes ( se vor folosi doar
indecșii )
Partitioning strategy to
employ No partitioning ( nu se vor
accepta recomandări legate
de partiționare )
Physical Design Structures
to keep in database Keep all existing PDS ( nu se
acceptă recomandări de
ștergere a indecșilor )
Tabel 2 – Opțiuni Database Engine Tuning Advisor
Figură 5- Opțiuni în interfață
35
După aceea am pornit analiza și am observant la final recomandările primite. Dacă vrem să
vedem care sunt definițiile Transact SQL pentru aceste recomandări, putem să accesăm
coloana Definitions.
Aceste recomandări pot fi observate mai jos:
use [AdventureWorks]
go
CREATE NONCLUSTERED INDEX
[_dta_index_SalesOrderDetail_17_642101328__K1_5] ON
[Sales] .[SalesOrderDetail]
(
[SalesOrderID] ASC
)
INCLUDE ( [ProductID] ) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING
= OFF, ONLINE = OFF) ON [PRIMA RY]
go
CREATE STATISTICS [_dta_stat_642101328_5_1] ON
[Sales] .[SalesOrderDetail] ([ProductID] , [SalesOrderID] )
go
CREATE NONCLUSTERED INDEX [_dta_index_Product_17_1461580245__K1]
ON [Production] .[Product]
(
[ProductID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
ON [PRIMARY]
go
Dacă aplicăm aceste recomandări, este posibil să vedem o îmbunătățire a performanței, dar
întotdeaua este recomandat ca acestea să fie testate în prealabil pe un mediu de test, pentru că
este posibil ca ac este modificări să ne ajute a avea o performanță bună pentru o anumită
interogare, dar să înrăutățim performanța pentru o altă cerere deja existentă. [6]
4.6. Optimizarea performanței cererilor
În urma analizării problemelor întâlnite în mediile de producție ale clienților care
folosesc un anumit sistem de gestiune a bazelor de date precum Microsoft SQL Server, am
identitificat dorința acestora de a obține rezultatele folsind cerereile SQL într -un mod mai
rapid. Din experiența mea în urma lucrului cu acest gen de probleme de performanță pot
menționa că majoritatea problemelor s -a rezolvat prin rescrierea cererilor într -un mod mai
eficient. Am subliniat mai jos modurile în care performanța acestor interogări poate fi
îmbunătățită, încercând să acopăr și să prezint l a modul general cele mai des întâlnite soluții
pentru a obține performanță mai bună imediat după rescrierea definiției cererilor.
36
După cum știm există anumite moduri care ne pot ajuta să rescriem cererile a căror
performanță fie nu a fost bună de la începu t, fie aceasta a fost deteriorată sau dorim un timp
de execuție mai bun. Metodele pe care o să le folosesc mai jos pentru a analiza diferențele
între cererile scrise inițial și cererile îmbunătățite sunt anal iza numărului de citiri logice
acesta fiind ofer ite de instrucțiunea SET STATISTICS IO ON, timpul total necesar (în
milisecunde) necesar pentru a parsa, compila și executa o cerere, timpul folosit de procesor
pentru executarea interogării, informații oferite de instrucțiunea SET STATISTICS TIME
ON, resp ectiv numărul de octeți trimiși prin rețea. În urma analizei acestor parametrii și prin
comparație vom observa că prin rescrierea cererilor trebuie să micșorăm numărul citirilor
logice și astfel putem obține performanță mai bună și apoi decidem care implem entare a
cererilor noastre va fi folosită în producție.
4.6.1. Situațiile fără indecși, cu indecși normali și cu indecși de acoperire
În acest parte voi testa bonusul de performanță adus de indecși normali și indecșii
covered pe un tabel cu un număr foar te mare de rânduri. Pentru acest lucru, va trebui mai
întâi creat respectivul tabel și adăugate datele în acesta.
Am creat un tabel cu 999999 de rânduri ce ocupă aproximativ 213MB. Din această cauză
operația de inserare va dura până la câteva minute. Am folosit instrucțiunile de mai jos pentru
a realiza acest tabel.
Figură 6 – Fișier pentru creare tabel
Astfel, pentru a putea testa performanța, vom folosi statisticile oferite de SQL Server.
Valoarea care ne interesează din to ate statisticile întoarse este citiri logice care întoarce
numărul de pagini accesate din cache, locația de unde sunt păstrate pentru moment datele
pentru a se oferi o accesare mai rapidă. Am activat accesarea statisticilor de input/output
folosind codul de mai jos:
SET STATISTICS IO ON
37
Următorul pas este crearea unor instrucțiuni SELECT prin care să testăm viteza bonus oferită
de existența indecșilor.
Am executat următoarele 3 instrucțiuni și am notat pentru fiecare valoarea de la citiri logice
întoar să de statistici. Informațiile de statistică pot fi ob servate în tab -ul Messages. Țin em
minte că aceste valori au fost întoarse în situația în care nu avem nici un index pe tabel.
Definiție Rezultat
Instrucțiune 1 SELECT Col1 , Col2
FROM dbo.CoveredInd exTest
WHERE Col1 =0;
(1 row(s) affected)
Table 'CoveredIndexTest'. Scan count 5,
logical reads 30961 , physical reads 0,
read-ahead reads 136, lob logical reads 0,
lob physical reads 0, lob read -ahead reads
0.
Instrucțiune 2 SELECT Col1 , Col2
FROM dbo.CoveredIndexTest
WHERE Col1 BETWEEN 1205
and 1225 ;
GO
(1740 row(s) affected)
Table 'CoveredIndexTest'. Scan count 5,
logical reads 30961 , physical reads 0,
read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read -ahead reads
0.
Instrucțiun e 3 SELECT Col1 , Col2
FROM dbo.CoveredIndexTest
WHERE Col1 BETWEEN 1205
and 1426 ;
(4350 row(s) affected)
Table 'CoveredIndexTest'. Scan count 5,
logical reads 30961 , physical reads 0,
read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob rea d-ahead reads
0.
Tabel 3 – Valori citiri logice în cazul fără indecși
Am creat pe tabelul dbo.CoveredIndexText un index nonclustered numit Noncovered care se
aplică doar pe coloana Col1.
CREATE NONCLUSTERED INDEX Noncovered on dbo.CoveredIndexTest (Col1 )
Am executat din nou cele 3 instrucțiuni de mai sus și am notat noile valori de la citiri logice .
Aceste valori sunt obținute aplicând pe tabel un index normal.
Definiție Rezultat
Instrucțiune 1 SELECT Col1 , Col2
FROM
dbo.Cover edIndexTest
WHERE Col1 =0;
(1 row(s) affected)
Table 'CoveredIndexTest'. Scan
count 1, logical reads 4, physical
reads 0, read -ahead reads 0, lob
logical reads 0, lob physical reads
0, lob read -ahead reads 0.
Instrucțiune 2 SELECT Col1 , Col2
FROM
dbo.CoveredIndexTest
WHERE Col1 BETWEEN
1205 and 1225 ; (1740 row(s) affected)
Table 'CoveredIndexTest'. Scan
count 1, logical reads 1748 ,
physical reads 0, read -ahead reads
38
GO
0, lob logical reads 0, lob physical
reads 0, lob read -ahead reads 0.
Instrucțiune 3 SELECT Col1 , Col2
FROM
dbo.CoveredIndexTest
WHERE Col1 BETWEEN
1205 and 1426 ;
(4350 row(s) affected)
Table 'CoveredIndexTest'. Scan
count 1, logical reads 4364 ,
physical reads 0, read -ahead reads
0, lob logical reads 0, lob physical
reads 0, lob read -ahead reads 0.
Tabel 4 – Valori citiri logice în cazul în care există un index normal
Am creat pe tabelul dbo.CoveredIndexText un index nonclustered numit Covered care se
aplică doar pe coloana Col1 dar include și coloana Col2 f olosind INCLUDE.
CREATE NONCLUSTERED INDEX Covered on dbo.CoveredIndexTest (Col1 ) INCLUDE
(Col2 )
Am executat din nou cele 3 instrucțiuni și am notat noile valori de la citiri logice . Aceste
valori sunt obținute aplicând pe tabel un index covered.
Defin iție Rezultat
Instrucțiune 1 SELECT Col1 , Col2
FROM
dbo.CoveredIndexTest
WHERE Col1 =0;
(1 row(s) affected)
Table 'CoveredIndexTest'. Scan
count 1, logical reads 3, physical
reads 0, read -ahead reads 0, lob
logical reads 0, lob physical reads
0, lob read -ahead reads 0.
Instrucțiune 2 SELECT Col1 , Col2
FROM
dbo.CoveredIndexTest
WHERE Col1 BETWEEN
1205 and 1225 ;
GO
(1740 row(s) affected)
Table 'CoveredIndexTest'. Scan
count 1, logical reads 53, physical
reads 0, read -ahead reads 50, lob
logical reads 0, lob physical reads
0, lob read -ahead reads 0.
Instrucțiune 3 SELECT Col1 , Col2
FROM
dbo.CoveredIndexTest
WHERE Col1 BETWEEN
1205 and 1426 ;
(4350 row(s) affected)
Table 'CoveredIndexTest'. Scan
count 1, logical reads 146, physical
reads 0, read -ahead r eads 92, lob
logical reads 0, lob physical reads
0, lob read -ahead reads 0.
Tabel 5 – Valori citiri logice în cazul în care există un index de acoperire
39
Comparăm valorile de la cele 3 cazuri de indexare pentru fiecare instrucțiun e și observăm
bonusul de performanță adus prin reducerea numărului de pagini citite pentru a se întoarce
rezultatul. În graficul de mai jos găsim rezultatele:
Instrucțiune 1 Instrucțiune 2 Instrucțiune 3
Fără indecși 30961 30961 30961
Indecși normali 4 1748 4364
Indecși covered 3 53 146
Tabel 6 – Număr pagini citite pentru f iecare caz de indexare
4.6.2. SELECT * from NumeTabel vs SELECT col1,col2, …, coln from NumeTabel
Am testat această operație vrând să întorc numele ș i salariul angajaților unde identificatorul
departamentului este egal cu 10.
Cererea pe care am făcut -o inițial a fost următoarea:
Figură 34 – Întoarcerea tuturor coloanelor
Figură 35 – Statisticile c lient
40
Îmbunătățirea statisticii am realizat -o întorcând doar coloanele de care am nevoie și filtrând
rezultatul cu clauza WHERE.
Figură 36 – Statisticile client după folosirea clauzei WHERE
Analiza comparativă pentru testarea s cenariului de mai sus este următoarea:
Figură 37 – Citiri logice operație SELECT
În versiunea optimizată, baza de date filtrează datele deoarece filtrează mai repede decât
programul. Datele care sunt trimise în rețea vor fi repr ezentate de mai puține rânduri și prin
urmare, performanțele se vor îmbunătăți.
Pentru a obține acest lucru, în acest caz, trebuie să reducem pe cât posibil cantitatea de date
pe care vrem să o returnăm și aici ne referim atât la numărul coloanelor, cât și numărul
rândurilor. În cazul în care operăm cu date mai puține, este clar că astfel vom reduce și
cantitatea de resurse de care avem nevoie pentru a executa această interogare. Trebuie să
avem în vedere faptul că este recomandat pe cât posibil să folosim clauza WHERE pentru a
limita astfel numărul de rânduri întoarse și scrierea explicită a coloanelor care vrem a fi
returnate pentru a limita numărul rândurilor din lista SELECT.
41
4.6.3 . Clauza ORDER BY
Figură 38 – Clauza ORDER BY
O clauză ORDER BY va adăuga întotdeauna o operație de sortare dacă nu există un
index utilizabil pentru a prelua datele în ordine. Pentru seturi mai mari de date, datele sortate
pot fi scrise pe disc, adăugând procesare suplimentară. Pentru seturile de re zultate mai mici în
care sunt folosite indicii, este posibil să nu existe o cerință pentru un pas de sortare.
Dacă este necesară sortarea, baza de date va trebui să recupereze toate datele cheie de sortare
și să le sorteze înainte de a prezenta rezultatele . Acest lucru poate întârzia disponibilitatea
primelor rânduri. Dacă dorim doar primele câteva rânduri dintr -un set mare, acest lucru poate
genera o mulțime de lucruri suplimentare pentru baza de date.
Dacă nu avem nevoie cu adevărat de această sortare est e recomandat să nu o folosim pentru
că se adaugă o operație în plus de sortare, numită Sort și care are în cazul nostru un cost de
78%.
4.6.4. Funcții
În funcți e de dificultatea și durata de rezolvare a cerințelor pe care le avem uităm de
multe ori să n e gândim și la testarea performanței. În ceea ce urmează am ilustrat un exemplu
de cerință și anume data comenzii să fie din luna iulia și anul 2008. Pentru a rezolva această
cerință putem să folosim funcțiile YEAR și MONTH în clauza WHERE pentru a întoarc e
datele corespunză toare cerinței noastre, fie pute m să folosim o clauză WHERE unde să
precizăm că valoarea trebuie să fie cuprinsă între 01/07/2008 și 31/07/2008. Atunci când
folosim o funcție în clauza WHERE, ca în cazul nostru SQL Server nu se va folosi de niciun
index care este disponibil.
Figură 39 – Folosire funcție în clauza WHER E
42
4.6.5. Verificarea existenței unei anumite valori
Pentru a realiza această operație, de cele mai multe ori suntem tentați să numărăm toate
rândurile din respectivul tabel acolo unde găsim valoarea pe care noi ne -o dorim. Dar în cazul
acesta operațiile de mai jos nu sunt eficiente, costul fiind de 100%.
Figură 40 – Folosire COUNT(*)
Pentru a optimiza această cerere p utem să folosim clauza EXISTS și în funcție de prima
valoarea găsită unde coloana Status conține valoarea dorită de noi și anume 5, atunci se va
afișa că a fost găsită această valoare și nu mai este necesară numărarea tuturor rândurilor ca
în cererea iniți ală. Costul acum este 1%.
Figură 41 – Folosirea clauzei EXISTS
4.6.6. Folosirea datelor din index
Tabelul Person are o cheie primară care a creat automat un index clustered. Structura
tabelului Person se găsește mai jos:
Figură 42 – Structură tabel Person
43
Pentru a testa lipsa indecșilor și impactul pe care îl au aceștia asupra performanței am
ales să mai creez un tabel care să conțină aceleași date și pentru a realiza acest lucru am
copiat datele din tabelul Person, tabelul unde am copiat neavând niciun index creat. Vrând să
ordonez datele ascendent după coloana identificatorul de business, voi observa că în primul
caz unde am un index clusterizat pe această coloană datele vor fi întoarse direct din a cest
index, pe când în cel de -al doilea caz se va face o operație de scanare a tabelului și de sortare,
costul cererii fiind mai mare decât în prima situație.
Figură 43 – Indexu l cu valorile sortate
4.6.7. Clauza DISTINCT
Putem să observăm că în primul caz folosind clauza DISTINCT, SQL Server alege să
întoarcă rezultatul direct din index, pe când în al doilea caz nu avem niciun index pe baza de
date și SQL Server scanează mai întâi tot tabelul și apoi se asigură că valorile su nt unice
printr-o operație de hash match.
Figură 44 – Clauza DISTINCT
Figură 45 – Rezervare memorie pentru clauza DISTINCT
Prin urmare, ca si recomandări putem menționa faptul că trebuie să folosim oper atorul
DISTINCT doar atunci când avem nevoie , pentru că în unele cazuri acesta necesită operații în
plus care sunt costisitoare din punct de vedere al costului si al memoriei folosite pentru ca
aceste rânduri să fie unice . Așa cum se observă mai sus in pla nul de execuție în cazul în care
44
folosim acest operator vom întâlni si operația de hash match al cărei cost este 10% și această
operație necesită și încărcarea datelor în memorie, lucru pe care îl putem observa atunci când
ne uităm la detaliile clauzei SEL ECT. Acordarea memoriei pentru cereri este o parte din
memoria serverului folosită pentru a stoca rândurile temporare cu scopul de a le sorta sau de a
face legătură cu alte rânduri. Serverul are nevoie ca aceste cereri să o rezerve înainte de a o
folosi ef ectiv. În cazul în care nu folosim acest operator vom observa că nu avem nevoie de
operațiile adiacente.
4.6.8. UNION și UNION ALL
Comanda UNION este folosită pentru a returna informații din 2 tabele și ne obligă ca datele
selectate să fie de același ti p. Atunci când folosim operatorul UNION, rândurile returnate vor
fi distincte. Folosirea operatorului UNION ALL este asemănătoare cu cea a lui UNION,
numai că acesta ne returnează și rândurile duplicate, nemaifiind nevoie să fie sortate. Pentru a
ilustra diferențele de performanță între acești doi operatori am ales să analizez planurile de
execuție ale unor cereri care întorc același coloane, singura diferență între acestea fiind
operatorul. În primul plan de execuție operatorul este UNION, iar în cel de -al doilea
operatorul este UNION ALL.
Figură 46 – Comparație UNION cu UNION ALL
Figură 47 – Rezervare memorie pentru clauza UNION
Dacă ne uităm la planurile de execuție observăm că pentru operatorul UNION avem operația
de sortare al cărei cost este 68% din costul total al execuției cererii noastre.și aici este
important de menționat că această operație de sortare este folosită pentru a elimina rândurile
duplicate și costul ei este destul de mare. De obicei necesită multă memorie. Atunci când
SQL Server rezervă memoria pentru a face această operație, dar dacă nu avem memorie
suficientă și numărul rândurilor care trebuie sortate este foarte mare, atunci operația este
scrisă pe disc pentru a fi executată în baz a de date de sistem numită tempdb. De asemenea, se
poate observa în Figura 4 6, că în cazul acesta, pentru că avem nevoie de operație de sortare,
trebuie să rezervăm și memorie pentru rân durile care urmează să fie sortate și să ni se
returneze doar cele unic e. Operația numită merge join este rapidă pentru ca ambele date
45
returnate de la cele 2 tabele folosite sunt deja sortate. În cazul nostru aceasta face operația
UNION.
Prin urmare, UNION ALL este mai rapid decât UNION pentru că acesta nu trebuie să elimine
rândurile duplicate și nu le sortează. UNION elimină duplicatele și sortează rândurile
întoarse, operație care necesită mai mult timp și imlicit timp de execuție mai mare pentru
executarea cererii.
4.6.9. Reducerea numărului de scrieri pe disc atunci câ nd folosim INSERT
O cerere poate să fie formată din mai multe instrucțiuni care să manipuleze datele. În
cazul în care proprietatea de a fi atomică este pentru fiecare interogare separat, atunci vor
exista prea multe scrieri pe disc în jurnalul de tranzac ții pentru a îndeplini cealaltă proprietate
a tranzacțiilor și anume durabilitate. După cum știm atunci când trebuie să facem operații cu
discul, acestea vor fi mult mai încete decât operațiile cu memoria sau activitatea procesorului.
Prin urmare, ca și c oncluzie în cazul în care folosim discul prea mult pentru scrieri, în caul
nostru pentru a scrie în jurnalul de tranzacții timpul de execuție al cererii noastre va fi mult
mai mare. Pentru a ilustra acest comprtament am testat exemplul de mai jos prin folo sirea
unei instrucțiuni de INSERT.
Figură 48 – Instrucțiune INSERT
Această instrucțiune este atomică și prin urmare vor exista scrieri în jurnalul de
tranzacții de fiecare dată când executăm această instrucțiune.
În cazul în car e vrem să reducem aceste scrieri pe disc în jurnalul de tranzacții putem să
includem multiplele operații INSERT într -o tranzacție prin folosirea blocului BEGIN
TRANSACTION și COMMIT.
Figură 49 – Instrucțiune INSERT în blocul BEGI N TRAN – COMMIT TRAN
Pentru a vedea care este procentul de spațiu fol osit în jurnalul de tranzacții pe care l -am
folosit în cele 3 cazuri: inițial, apoi când am executat instrucțiunile INSERT ca fiecare fiind o
unitate atomică și apoi când am folosit blocu l BEGIN TRAN – COMMIT TRAN am folosit
comanda DBCC SQLPERF (LOGSPACE ) care ne spune care este dimensiunea jurnalului de
tranzacții în MB, respective spațiul folosit din jurnalul de tranzacții în procent .
Se poate oberva mai jos că procentul inițial de spa țiu de jurnal de tranzacții folosit este
37,20%. Atunci când am executat instrucțiunile de INSERT în cad rul bloc ului WHILE acesta
46
a crescut cu aproximativ 33 de procente. Cea mai bună performanță este obținută în cazul
acesta dacă folosim includerea multip lelor instrucțiuni INSERT într -un bloc de tranzacție și
astfel vor fi tratate deodată, în cazul acesta observându -se o creștere de doar aproximativ 7%.
Figură 50 – Spațiu folosit în jurnalul de tranzacții
4.6.10. Conversia tipu lui de date din nvarchar în char
Pentru a ilustra problemele care pot apărea în cazul în care nu folosim tipul de date corect am
creat o procedură care are ca parametru de intrare un cod de tipul nvarchar( 2) și acest cod l –
am folosit și în clauza WHERE pe ntru a întoarce rânduri filtrate după valoarea dată ca și
parametru procedurii stocate.
CREATE PROCEDURE [dbo] .[spTestareConversie]
@cod NVARCHAR (2)
AS
[…]
FROM TestTabel c
WHERE cod = @cod ;
GO
Observația pe care aș vrea să o aduc aici este că tipul de date al coloanei cod din tabelul
TestTable este char(2) și noi am folosit în definiția procedurii nvarchar(2). Pentru a testa
performanța procedurii stocate am executat instrucțiunea de mai jos, colectând totodată și
statisiticile executării a cesteia:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXEC [dbo] .[spTestareConversie] N'AC' ;
47
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
Atunci când am analizat acest comportament am avut în vedere indicatorii de citiri logice în
ambele cazuri, p e care îi putem observa mai jos și diferența între numărul citirilor logice în
cazul în care folosim ca și tip de date al parametrului de intrare nvarchar(2) și nu char(2) care
este tipul de date al coloanei cod din tabelul TestTabel este foarte mare, în c azul nostru
observăm 9335 de citiri logice atunci când se face conversia tipului de date la cel specificat în
structura tabelului pentru coloana cod și doar 6 citiri logice atunci când conversia nu mai este
necesară. Timpul UCP este de asemenea mult mai ma re în primul caz și implicit timpul total
de execuție al interogării noastre va fi costisitor.
Figură 51 – Conversia tipului de date din nvarchar(2) în char(2)
Problema această este semnalată și în planul de execuție al executări i procedurii. Dacă citim
informațiile operatorului SELECT din cadrul planului de execuție care este de asemenea și
rădăcina tuturor celorlalți operatori, observăm că interogarea noastră trebuie să aștepte 96 de
secunde pentru rezervarea memoriei în timpul execuției și faptul că se face conversie
implicită poate afecta estimarea cardinalității în alegerea planului de execuție.
Am rezolvat această situație prin modificarea definiției procedurii și specificarea tipului de
date pentru parametrul de intrare ca fiind același cu cel al coloanei din tabelul folosit.
Soluția este prezentată mai jos și anume:
ALTER PROCEDURE [dbo] .[spTestareConversie]
@cod CHAR (2)
AS […]
Ca și recomandări trebuie să avem în vedere ca atunci când avem predicate de filtrare sau de
legătură între anumite coloane să folosim tipu ri de date care sa fie aceleași.
4.6.11. Folosirea declanșatoarelor poate afecta performanța
De multe ori se întâmplă ca să avem un declanșator care ne afectează performanța. Am creat
mai jos un declanșator care îmi modifică una dintre coloane și scrie ca valoarea ziua curentă
și timpul când modificarea s -a efectuat folosind funcția GETDATE():
CREATE TRIGGER [trg_TabelTest] ON [dbo] .[TabelTest]
AFTER INSERT
AS
48
BEGIN
UPDATE [dbo] .[TabelTest]
SET [Coloana1] = GETDATE ()
FROM [dbo] .[TabelTest1] AS [c]
INNER JOIN inserted AS [i]
ON [c].[Coloana2] = [i].[Coloan2] ;
END
GO
De asemenea Coloana2 face parte din tabelul TabelTest și este definită astfel: [… ] Coloana2
datetime NULL.
Pentru a vedea numărul citirilor logice am inserat 300000 de rânduri în tabelul TabelTest.
Numărul citirilor logice folosite și mplicit accesarea datelor pentru inserarea datelor este
820862, la această operație adăugându -se și nu mărul cititirilor logice aferente execuției
declanșatorului și anume 902296. Din cauza executării declanșatorului performanța execuției
cererii noastre a scăzut. Așadar în acest caz pentru a păstra aceeași funcționalitate, dar a
renunța la declanșator pute m să obținem același rezultat prin crearea unei constrângeri
default, care să ne insereze automat în coloană valorile corespunzătoare zilei si timpului din
momentul inserării, nemaifiind necesar să existe acel plus de access al datelor din cauza
declanșato rului: […] DEFAULT (GETDATE ()) FOR Coloana1.
Figură 52 – Citiri logice atunci când folosim declanșatoare
49
5. Concluzi e
Mulți administratori abordează problemele de performanță numai prin reglarea
perfo rmanțelor serverului la nivel de sistem: de exemplu, dimensiunea memoriei, tipul de
sistem de fișiere, numărul și tipul procesoarelor ș.a .m.d.Cu toate acestea, multe probleme de
performanță nu pot fi rezolvate în acest fel. Acestea sunt abordate mai bine ș i prin analizarea
cererilor și actualizărilor de aplicații pe care aplicația le trimite la baza de date și modul în
care aceste interogări și actualizări interacționează cu datele conținute în baza de date și
schema bazei de date. [8]
Pentru a îmbunătăți pe rformanța unei baze de date folosite de o aplicație, este important
să ne asigurăm că interogările sunt create corect pentru a putea să beneficiem de performanță
maximă și implicit de obiectele existente precum indecșii sau constrângerile existente pe baza
de date. Este important ca atunci când optimizăm performanț a unei anumite cereri să avem în
vedere și modul în care interacționează cu celelalte cereri și implicit să fie toate optimizate
pentru a evita astfel problema ca unele interorgări să se blocheze unele pe celelalte.
Atunci când colectăm informațiile despre o anumită cerere trebuie să avem în vedere și
factorii externi, cum ar fi viteza de scriere pe disc, presiunea pe care o aduc alte interogări și
resursele folsoite de acestea care pot bloca anum ite resurse. Pentru analiza performanței
acestor interogări am luat în considerare atât informațiile statistice oferite de instrucțiunile
SET STATISTICS IO și SET STATISTICS TIME și anume, numărul de citiri logice și
timpul de execuție total, respectiv tim pul folosit de procesor, cât și analiza planurilor actuale
și estimate de execuție. De asmenenea întâlnim și numărul de citiri a paginilior fizice.
Ca explicație atunci când ne referim la citirile logice vorbim de fapt de numărul de pagini
citite din memo ria cache de date, pe când dacă vorbim de citirile fizice – ne referim la
numărul de pagini citite de pe disc. Atunci când vedem rezultatele oferite de instrucțiunile de
mai jos știm că acestea ne ajută să ne concentrăm în primul rând pe modul în care date le
necesare sunt accesate din tabelele noastre, lucru care poate fi analizat prin vizualizarea
numărului de citiri logice . În cazul în care vedem un număr mare de citiri folosit de
interogarea noastră trebuie să ne focusăm pe rescrierea acestuia și îmbunăt ățirea
performanței. De asemenea, trebuie avut în vedere și costul procesorului și timpul total de
execuție al interogării noastre problematice. De îndată ce am identificat interogările al căror
cost de execuție este mare și după ce am terminat analiza in ițială prin care și analiza
planurilor de execuție trebuie să ne gândim la modurile în care putem optimiza cererile pentru
a avea o performanță mai bună.
Cea mai întâlnită și comună modalitate de îmbunătățire a performanței cererilor este crearea
unor inde cși care lipsesc.
Cele mai importante obiective pe care trebuie să le avem în vedere pentru a avea o
performanță bună sunt proiectarea și dezvoltarea unei baze de date eficiente, optimizarea
cererilor și a indecșilor, procedurilor stocate și tranzacțiilor, monitorizarea accesului de către
interogările noastre la datele necesare din tabele, analiza planurilor de execuție și să încercăm
să evităm pe cât posibil ca cererile noastre să își schimbe planul de execuție sau să fie
recompilate din cauza unor instruc țiuni care cer această operație.
În urma realizării acestor experimente și analizei performanței fiecărei interogări aș dori să
menționez sfaturi de operare în aceste situații care pot fi folosite de fiecare dată când trebuie
să dezvoltăm o bază de date și să creăm o anumită interogare pentru a satisface o cerință a
aplicației noastre din producție.
50
În primul rând trebuie să avem un index clustered și mai mulți indecși nonclustered care să
fie creați cu scopul ca interogările noastre să beneficieze de per formanță maximă.
De asemenea pe cât posibil să încercăm să evităm întoarcerea tuturor coloanelor dintr -o
anumită interogare pentru că dacă avem o cantitate mare de date atunci va dura mai mult până
când rezultatul va fi afișat. În această situație se recom andă să specificăm explicit numele
coloanelor și să folosi m clauza WHERE pentru a filtra rândurile întoarse.
Pe lângă această recomandare, putem să menționăm și evitarea pe cât posibil a clauzei
DISTINCT pentru că așa cum am explicat mai sus această clauz ă necesită verificarea ca
rândurile întoarse să nu fie duplicate, operație care nu este benefică pentru performanța
interogării noastre.
De asemenea este recomandat ca să ev ităm folosirea declanșatoarelor, mai ales că de multe
ori acestea sunt uitate în ba za de date și nu mai sunt luate în considerare, fiind găsite abia
atunci când se observă performanța scăzută a interogărilor.
În cazul în care vrem să verificăm dacă o anumită valoarea este într -o anumită coloană este
indicat să folosim funcția EXISTS() în locul funcției COUNT(*). Această funcție agregată va
scana toate rândurile din tabelul nostru, ceea ce va dura foarte mult. Prima funcție, în schimb,
se va opri din execuție după ce a găsit prima valoare egală cu cea specificată de noi.
Dacă vrem să creăm o procedură stocată care să primească un parametru de intrare care apoi
să fie comparat cu valorile dintr -o anumită coloană trebuie să avem în vedere ca tipul de date
pe care îl scriem în definiția procedurii stocate să fie același cu tipul de date al col oanei cu
care vrem să comparăm. Dacă nu potrivim să fie aceleași tipuri de date, vom vedea în
planurile de execuție că se va face conversia implicită după regulile de prioritate din SQL
Server. Așa cum am văzut mai sus, această conversie afectează performa nța și conduce la
alegerea unui plan de execuție ineficient.
Atunci când vrem să ordonăm rezultatele în mod ascendent sau descendent trebuie să fim
atenți la folosirea clauzei ORDER BY pentru că se va face o operație de sortare, al cărei cost
este mare. Du pă cum știm această operație de sortare are nevoie de mai multe resurse, cum ar
fi procesorul și totodată este foarte intensivă, mai ales dacă aplicăm operația de sortare pe
cantități mari de date.
Dar dacă avem, spre exemplu, un index clustered pe coloana pe care vrem să o ordonăm, știm
că acest tip de index ne oferă deja o reprezentare fizică și ordonată a datelor din această
coloană și putem să ne folosim de acesta pentru a evita operația de sortare.
Un ultim exemplu pe care aș vrea să îl aduc în discuți e este folosirea operatorilor UNION și
UNION ALL, care după cum știm sunt folosiți pentru a combina înregistrări din două tabele
diferite. Spre deosebire de JOIN acesta combină datele pe verticală și adaugă rândurile din
tabelele folosite.Atunci când ne ho tărăm să folosim acest operator trebuie să avem în vedere
faptul că doar UNION ALL ne întoarce toate rândurile din tabelele folosite, dar cu tot cu cele
duplicate, lucru bun pentru performanța interogării noastre, pe când UNION filtrează
rândurile pentru a nu fi duplicate, operație care ne costă din punct de vedere al performanței.
În această lucrare am analizat aspectele fundamentale ale optimizării interogării care stau
la baza tuturor algoritmilor de optimizare cunoscuți în literatura de specialitate. Re zultatele
experimentelor efectuate au fost verificate utilizând instrumentul Database Query Tuning
Advisor . Optimizarea interogărilor este esențială pentru sistemele mari de unde vrem să
întoarcem datele.
51
6. Bibliografie
[1] Grant Fritchey , Sajal Dam , SQL Server 2012 Query Pe rformance Tuning, Editura
Apress, 2012
[2] Jason Strate , Grant Fritchey , Expert Performance Indexing in SQL Server, Editura
Apress, 2015
[3] Dan Tow , Generating Optimal Execution Plans, Editura O'Reilly Media, 2003
[4] Cursuri: https://academy.microsoft.pub.ro/
[5] Michael L. Rupley, Jr. Introduction to Qu ery Processing and Optimization, Indiana
University at South Bend, 2008
[6] http://ijarcce.com/upload/2016/december -16/IJARCCE%2033.pdf
[7] https://sqlnexus.codeplex.com/
[8] Benjamin Nevarez , Microsoft SQL Server 2014 Query Tuning & Optimization ,
Editura McGraw -Hill Education, 2014
Copyright Notice
© Licențiada.org respectă drepturile de proprietate intelectuală și așteaptă ca toți utilizatorii să facă același lucru. Dacă consideri că un conținut de pe site încalcă drepturile tale de autor, te rugăm să trimiți o notificare DMCA.
Acest articol: Proiect Disertatie Acs Pasare V Alice Lavinia 92586 (1) [604705] (ID: 604705)
Dacă considerați că acest conținut vă încalcă drepturile de autor, vă rugăm să depuneți o cerere pe pagina noastră Copyright Takedown.