Cele mai bune practici SQL Server, partea 1 Configurare

Acasă »Cele mai bune practici pentru SQL Server, Partea 1: Configurare

cele

Citirea ghidurilor de bune practici pentru Microsoft SQL Server poate fi o treabă destul de grea. După ce ne-am luptat prin ghiduri tehnice, ghiduri de bune practici, articole TechNet și texte de blog de la experți SQL, am ajuns la concluzia că un text ușor de înțeles pe acest subiect ar fi util.

Scopul nostru nu este să intrăm în fiecare setare, ci mai degrabă să explicăm câteva lucruri care pot fi utile în timpul configurării și al problemelor de performanță.

Instanțe partajate versus instanțe dedicate

Dacă o aplicație utilizează un număr mare de scheme/proceduri stocate, aceasta poate degrada performanța aplicațiilor folosind aceeași instanță. Resursele disponibile pot fi împărțite sau blocate. Acest lucru poate duce la încetinirea altor aplicații bazate pe baze de date în instanța partajată SQL Server. Remedierea problemelor de performanță poate fi o corvoadă, deoarece trebuie să vă dați seama care instanță cauzează problema. Și asta nu este adesea atât de ușor.

Modul în care companiile răspund la această întrebare este de obicei cântărit în raport cu costul sistemelor de operare și al licențelor SQL. Dacă performanța aplicației este o prioritate, o instanță dedicată este foarte recomandată.

Microsoft nu vinde licențe SQL Server pentru servere pe instanță, ci pe bază. Din motive de cost, administratorii tind, așadar, să instaleze cât mai multe instanțe SQL Server pe care un server le poate gestiona, ceea ce pe termen lung poate duce la probleme masive de performanță.

Ori de câte ori este posibil, ar trebui deci să optați pentru instanțe SQL dedicate.

Împărțirea fișierelor SQL pe diferiți purtători de date

SQL Server utilizează modele I/O foarte diferite pentru citirea datelor și a fișierelor jurnal. Accesul la fișierele de date este de obicei aleatoriu, la fișierele jurnal de tranzacții secvențial. Cu unitățile de disc rotative, capul de citire trebuie repoziționat pentru acces aleatoriu la I/O. Accesul secvențial la date este, prin urmare, mai eficient decât cel aleatoriu. Dacă separați fișiere cu modele de acces diferite, reduceți și numărul de mișcări ale capului de citire și optimizați astfel capacitatea de stocare.

Utilizați sisteme RAID 10 pentru binare, date, fișiere jurnal și baze de date tempdb pentru cea mai bună performanță și disponibilitate posibilă.

Dimensionarea bazelor de date tempdb

Setați dimensiunea fișierului bazelor de date tempdb la valoarea maximă pentru a evita fragmentarea discului.

Pot apărea conflicte pe paginile GAM, SGAM și PFS atunci când SQL trebuie să scrie pe anumite pagini de sistem pentru a aloca obiecte noi. Zăvoarele protejează (blochează) aceste pagini în memorie. Pe un server SQL suprasolicitat, poate dura mult timp ca un fișier de sistem să fie blocat în baza de date tempdb, ceea ce duce la timpi de interogare mai lungi. Acest fenomen se numește „dispută de blocare”.

Regula generală pentru crearea fișierelor de date tempdb:

  • Pentru 8 nuclee
    • 8 fișiere de date tempdb

Începând cu SQL Server 2016, numărul de nuclee CPU vizibile pentru sistemul de operare este determinat automat în timpul instalării. Pe baza acestui fapt, SQL Server calculează și configurează numărul de fișiere tempdb necesare pentru performanțe optime. Aceasta este o îmbunătățire semnificativă față de versiunile anterioare - felicitări pentru Microsoft!

Configurare stocare

  • Memorie min. Server
  • Memorie maximă a serverului
  • Numărul maxim de fire de lucru
  • Stocare pentru crearea indexului
  • Memorie de lucru minimă pentru fiecare interogare

Memorie min. Server

Cu opțiunea „Min. Memorie server ”puteți determina cantitatea minimă de memorie pentru instanța SQL Server. Deoarece SQL Server este o memorie reală, folosind fiecare memorie RAM disponibilă, această setare este de obicei utilizată atunci când sistemul de operare solicită prea multă memorie de la SQL Server. Cu toate acestea, tehnologiile de virtualizare fac această setare mai importantă.

Memorie maximă a serverului

Cu opțiunea „Max. Memorie server ”, este setată memoria maximă pentru instanța SQL Server. Este deosebit de important atunci când alte aplicații rulează în plus față de SQL Server și doriți să vă asigurați că există suficientă memorie disponibilă pentru acestea.

Unele aplicații folosesc pur și simplu memoria disponibilă la pornire și nu necesită mai multă memorie RAM. Nici măcar dacă ar fi necesar. Aici apare opțiunea „Max. Memoria serverului ”intră în joc.

Într-un cluster sau fermă SQL Server, pot exista mai multe instanțe SQL Server care concurează pentru resurse. Setând o limită de memorie pentru fiecare instanță SQL Server, evitați această competiție pentru RAM și asigurați performanțe optime.

Nu uitați să lăsați cel puțin 4-6 GB RAM pentru sistemul de operare pentru a preveni probleme de performanță.

Numărul maxim de fire de lucru

Această opțiune este utilizată pentru a optimiza performanța atunci când un număr mare de clienți sunt conectați la serverul SQL. De obicei, se creează un fir de sistem de operare separat pentru fiecare cerere de interogare. Cu toate acestea, utilizarea unui fir pentru fiecare cerere de interogare pentru sute de conexiuni la server poate consuma resurse mari de sistem. Opțiunea „Max. Numărul de fire de lucru ”ajută la îmbunătățirea performanței, deoarece SQL Server vă permite să creați grupuri de fire de lucru care pot gestiona un număr mai mare de cereri de interogare.

Valoarea implicită este 0, ceea ce permite SQL Server să configureze automat numărul de fire de lucru la pornire. Această setare este potrivită pentru majoritatea sistemelor. „Max. Numărul de fire de lucru ”este o opțiune avansată și ar trebui modificată numai în cooperare cu un administrator de baze de date experimentat.

Când ar trebui să configurați SQL Server pentru a utiliza mai multe fire de lucru? Dacă lungimea medie a cozii pentru fiecare modul de planificare este mai mare de 1, poate avea sens să creșteți numărul de fire de execuție - dar numai dacă încărcarea nu este legată de CPU sau dacă există altfel timpi de așteptare lungi. Dacă oricare dintre cele două este adevărat, nu are rost să adăugați alte fire, deoarece acestea ar ajunge și în coadă.

Stocare pentru crearea indexului

Aceasta este, de asemenea, o opțiune avansată care nu ar trebui modificată în mod normal. Aceasta controlează cantitatea maximă de RAM alocată pentru a crea indici. Valoarea implicită pentru această opțiune este 0, ceea ce înseamnă că SQL Server va configura automat această setare. Cu toate acestea, dacă aveți dificultăți în construirea indexului, puteți crește această valoare.

Memorie de lucru minimă pentru fiecare interogare

Când se execută o interogare, SQL Server încearcă să aloce cantitatea optimă de memorie pentru aceasta. În mod implicit, există un minim de 1.024 KB pentru fiecare interogare. Este recomandat să lăsați setarea implicită la 0, astfel încât SQL Server să poată gestiona dinamic memoria alocată pentru crearea indexului. Cu toate acestea, dacă SQL Server are mai multă RAM decât este necesar pentru executarea eficientă a programului, performanța unor interogări poate fi crescută prin creșterea numărului. Atâta timp cât pe server există memorie gratuită care nu este utilizată de SQL Server, alte aplicații sau sistemul de operare, creșterea valorii poate îmbunătăți performanța generală a SQL Server. Cu toate acestea, dacă nu există RAM disponibilă, acțiunea are un efect destul de negativ asupra performanței generale.

Configurarea procesorului

Hyperthreading

Hyperthreading este o implementare specială a simultanei multithreading (SMT) în procesoarele Intel pentru a îmbunătăți paralelizarea calculelor (multitasking) în microprocesoarele x86. Hardware-ul care folosește hiperthreading face ca procesorii de hiperthreading logici să apară în sistemul de operare ca CPU-uri fizice. SQL Server detectează apoi procesoarele fizice pe care le indică sistemul de operare. În acest fel puteți profita de procesoarele Hypherthreading.

Singura problemă este că fiecare versiune de SQL Server are propria limită de capacitate de calcul.

NUMA (acces neuniform la memorie)

NUMA este o metodă de optimizare a accesului la memorie. Și cu ajutorul lor, viteza procesorului poate fi mărită fără a crește utilizarea magistralei procesorului. SQL Server acceptă NUMA și funcționează bine pe hardware-ul NUMA fără a necesita nicio configurație specială.

Afilierea procesorului

Cu excepția cazului în care aveți probleme de performanță, este puțin probabil să aveți nevoie să schimbați setarea implicită de asociere a procesorului. Totuși, merită să știți mai multe despre asta.

SQL Server acceptă afinitatea procesorului prin două opțiuni de mască:

  • Affinity Mask (cunoscută și sub numele de CPU affinity mask)
  • Afinitate I/O

SQL Server utilizează toate procesoarele disponibile în sistemul de operare. Modulele de programare sunt create pentru toate procesoarele pentru a utiliza resursele în mod optim. Atunci când este multitasking, sistemul de operare sau alte aplicații de pe serverul SQL pot muta fire de proces între procesoare. Deoarece SQL Server utilizează cantități mari de resurse, acest lucru poate afecta performanța. Pentru a minimiza acest efect, procesoarele pot fi configurate astfel încât încărcarea SQL Server să fie alocată unui grup de procesoare selectat anterior. Acest lucru este posibil prin intermediul măștii de afinitate a procesorului.

Opțiunea I/O Affinity leagă I/O de disc SQL Server de un anumit subset de procesoare. În mediile de procesare tranzacțională online (OLTP), această îmbunătățire poate îmbunătăți performanța threadurilor SQL Server care cauzează I/O.

Notă: Afinitatea hardware pentru discuri individuale sau controlere de disc nu este acceptată.

Gradul maxim de paralelism (MAXDOP)

În mod implicit, SQL Server utilizează toate procesoarele disponibile pentru a executa interogări. Deși acest lucru este benefic pentru interogări mari, acesta poate afecta performanța și concurența. O abordare mai bună este limitarea paralelismului la numărul de nuclee fizice dintr-un socket de procesor. De exemplu, MAXDOP ar trebui să fie setat la 4 pe un server SQL cu două prize de procesor fizice cu patru nuclee fiecare, indiferent de funcția de hiperthreading. MAXDOP nu poate determina ce procesor este utilizat. Mai degrabă, limitează numărul maxim de procesoare care pot fi utilizate pentru o singură interogare.

Pragul de cost pentru concurență

Valoarea implicită pentru această opțiune este 5. Optimizatorul de interogare utilizează pragul de cost pentru a determina dacă are sens să creeze planuri paralele pentru interogări. 5 este o valoare foarte mică, care este potrivită numai pentru aplicații OLTP pure (apropo, inclusiv DatAdvantage).

Pentru sistemele non-OLTP, vă recomandăm să setați prima valoare la aproximativ 50 și să o ajustați după cum este necesar. Ar trebui să ajustați cu siguranță valoarea pentru interogările critice dintr-o aplicație.

Alte setări importante

Initializare fisier instant

Dacă acordați SQL Server privilegiul Windows „Efectuați sarcini de întreținere a volumului”, veți avea o performanță mai bună la creșterea fișierelor de date.

De obicei, Windows scrie o mulțime de zerouri de îndată ce un utilizator are nevoie de spațiu. Dacă creați un fișier de 1 MB, Windows va scrie 1 MB de zerouri pe disc pentru a inițializa fișierul. Dacă SQL Server are privilegiul „Efectuați sarcini de întreținere a volumului”, acesta îi solicită Windows să marcheze spațiul necesar ca ocupat și să îl returneze imediat la SQL Server. Aceasta permite mărirea mai rapidă a fișierelor.

Compresie de rezervă

Începând cu SQL Server 2008r2, compresia de rezervă poate fi activată folosind o casetă de selectare.

Drept urmare, copiile de rezervă necesită mai puțin spațiu de stocare, necesită mai puțin timp și sunt restaurate chiar mai rapid. Cu siguranță ar trebui să utilizați această setare.

Conexiune dedicată administrator de la distanță

Aveți nevoie de această setare numai dacă ceva nu este în regulă cu SQL Server.

Când accesați printr-o conexiune de administrator dedicat (DAC), SQL Server oferă o conexiune dedicată, un planificator CPU și o memorie. Depanarea la distanță a unei instanțe SQL Server care este în mod constant la 100% utilizare CPU este mult mai ușoară atunci când aveți resurse dedicate! Trebuie să fiți conectat la SQL Server fie fizic prin consolă, fie de la distanță prin RDP pentru a utiliza un DAC la distanță. Ar trebui să utilizați cu siguranță și această setare. Odată activat, îl puteți uita din nou!

Concluzie

SQL Server oferă performanța și scalabilitatea necesare pentru a sprijini aplicațiile bazei de date de producție, cu condiția să fie respectate cele mai bune practici.
În următoarea noastră postare de blog, vom analiza cele mai bune practici pentru SQL Server în medii virtualizate.