De câteva ori pe săptămână îmi este cerută să sfătuiesc cum să păstrați eficient o bază de date de lucru. Uneori, întrebările apar de la administratorii de baze de date care utilizează soluții noi și caută ajutor în ajustarea practicii de service la caracteristicile noilor baze de date. Mai des, întrebările provin de la oameni care nu sunt administratori de baze de date profesionale, dar pentru un motiv sau altul, dețin bazele de date sau sunt responsabili pentru aceasta. Prefer să le numesc "administratori de baze de date involuntare". Scopul acestui articol este de a oferi un tutorial cu privire la cele mai bune opțiuni pentru menținerea bazelor de date SQL Server.
Ca și în cazul majorității sarcinilor și procedurilor din lumea tehnologiei informației, nu există o soluție eficientă pentru menținerea bazelor de date, dar există câteva domenii-cheie care necesită aproape întotdeauna atenție. Primele mele cinci personale din aceste zone, listate fără o anumită ordine de importanță:
- Gestionarea datelor și fișierelor jurnal
- Fragmentarea indexului
- statistică
- Detectarea defectelor
- backup-uri
O bază de date fără întreținere (sau prost întreținută) poate avea probleme în una sau mai multe dintre aceste domenii, ceea ce poate duce, în timp, la performanțe scăzute ale aplicațiilor sau chiar la întreruperi și pierderi de date.
Gestionarea datelor și fișierelor jurnal
Prima zonă pe care o recomand întotdeauna verificarea la gestionarea bazelor de date este setările asociate cu gestionarea datelor și a fișierelor de jurnal (tranzacții). Mai exact, este necesar să se asigure că:
- Datele și fișierele de jurnal sunt separate unul de celălalt, precum și izolate de orice altceva
- Zoom-ul configurat corect
- Se configurează inițializarea automată a fișierelor
- Compresia automată este dezactivată, iar comprimarea nu face parte din niciun plan de întreținere
Când fișierele de date și jurnal (care, în mod ideal, toate ar trebui să fie pe volume diferite) sunt în același volum ca orice altă aplicație care creează sau extinde fișiere, există o posibilitate de fragmentare a fișierelor. În fișierele de date, fragmentarea excesivă a fișierelor poate aduce o mică contribuție la performanța slabă a interogărilor (în special a celor care scanează date foarte mari). În fișierele din jurnal, poate provoca un impact mult mai grav asupra performanței, mai ales dacă extensia automată este instalată numai pe o creștere foarte mică a mărimii fișierului de fiecare dată când apare o necesitate.
În ceea ce privește dimensiunea fișierelor de date și jurnal, este mai bine să le creați cu dimensiunea originală corespunzătoare. Pentru fișierele de date, atunci când se alege dimensiunea originală, trebuie luată în considerare posibilitatea de a adăuga date suplimentare în baza de date pe termen scurt. De exemplu, în cazul în care dimensiunea datelor originale este de 50 GB, dar este cunoscut faptul că, în următoarele șase luni vor fi adăugate la o cantitate suplimentară de 50 GB de date, are sens să se stabilească imediat dimensiunea fișierului de date de 100 GB, mai degrabă decât să crească din nou la această dimensiune în termen de câteva luni.
Cu fișiere care se ocupă de reviste, din păcate, este oarecum mai complicat - trebuie să ia în considerare factori cum ar fi mărimea tranzacției (tranzacții pe termen lung nu pot fi eliminate din jurnalul până când realizează) și log de frecvență de rezervă (deoarece este atunci când a eliminat porțiunea inactivă a jurnalului). Pentru mai multe informații, consultați „8 pași pentru o mai bună tranzacție Log Throughput (“ 8 pași pentru a îmbunătăți randamentul a jurnalului de tranzacții „)“, cele mai populare posturi de pe blog pe SQLskills.com, scrise de soția mea, Kimberly Tripp (Kimberly Tripp).
După ce dimensiunile fișierelor sunt setate, acestea trebuie să fie vizionate la momente diferite și trebuie să fie mărite manual în avans la ora corespunzătoare a zilei. Mărirea automată ar trebui lăsată ca măsură de precauție, doar în cazul în care, astfel încât fișierele pot crește, dacă este necesar, în cazul unui eveniment neobișnuit. Motivul pentru gestionarea fișierelor nu trebuie să lase creștere în întregime automată este faptul că creșterea automată în bucăți mici duce la fragmentarea fișierelor și că creșterea automată poate fi consumatoare de timp, care împiedică activitatea de aplicare în momente neașteptate.
Mărimea măririi automate trebuie să fie setată la o anumită valoare, nu la un procent, pentru a limita timpul și spațiul necesar pentru a efectua mărirea automată, dacă aceasta are loc. De exemplu, în cazul unui fișier de date de 100 de gigaocteți, este de dorit să fixați mărimea creșterii automate ca 5 GB și nu, de exemplu, 10%. Aceasta înseamnă că va crește mereu cu 5 GB, indiferent de dimensiunea curentă a fișierului, și nu cu suma care crește după fiecare creștere a fișierului (10 GB, 11 GB, 12 GB și așa mai departe).
Compresia automată este deosebit de dăunătoare, deoarece rulează la fiecare 30 de minute în fundal și încearcă să comprime bazele de date pentru care este setată opțiunea de comprimare automată. Acest proces nu este complet previzibil prin faptul că comprimă numai baze de date cu spațiu liber mai mare de 25%. Compresia automată utilizează o mulțime de resurse și determină performanțe de reducere a fragmentării, deci este nedorită în toate circumstanțele. Ar trebui să fie întotdeauna dezactivată utilizând:
Planul obișnuit de întreținere, care include emiterea comenzii manuale în baza de date, este aproape la fel de rău. Dacă se constată că baza de date este în continuă creștere după ce planul de întreținere îl comprimă, aceasta se datorează faptului că baza de date are nevoie de acest spațiu pentru a funcționa.
Fragmentarea indicilor
În plus față de fragmentarea la nivelul sistemului de fișiere și în interiorul fișierului jurnal, este posibilă și fragmentarea în fișierele de date, în structurile care stochează date tabele și index. În fișierul de date există două tipuri de fragmentare de bază:
Fig. 1. Structura paginii bazei de date
Deseori, fragmentarea internă este cauzată de schimbările de date, cum ar fi inserările, actualizările și ștergerile, care pot lăsa pagini goale pe pagină. Ordonarea greșită a factorului de umplere poate contribui, de asemenea, la fragmentare, detalii sunt furnizate în documentația tehnică. În funcție de schema tabelei / indexului și de caracteristicile aplicației, acest spațiu gol poate să nu fie utilizabil după ce acesta apare, ceea ce poate duce la o creștere permanentă a spațiului neutilizat din baza de date.
Luați în considerare, de exemplu, un tabel de 100 de milioane de rânduri, unde înregistrarea medie este de 400 de octeți în dimensiune. În timp, șablonul de modificare a datelor aplicației va avea ca rezultat o medie de 2800 de octeți de spațiu liber pe pagină. Spațiul total cerut de tabel este de 59 GB, acesta fiind obținut prin calculul următor: 8096-2800 / 400 = 13 intrări pe o pagină de 8 kilobyte, apoi împărțiți 100 de milioane cu 13 pentru a obține numărul de pagini. Dacă spațiul nu a dispărut, atunci pe o singură pagină ar fi posibil să se potrivească 20 de înregistrări, ceea ce reduce spațiul total necesar până la 38 GB. O economie imensă!
Un spațiu neutilizat pe paginile de date / indexuri poate duce astfel la stocarea aceleiași cantități de date pe un număr mai mare de pagini. Acest lucru nu numai că duce la mai mult spațiu pe disc, dar, de asemenea, înseamnă că cererea trebuie să efectueze mai multe operații I / O pentru a citi același număr de date. Toate aceste pagini suplimentare ocupă mai mult spațiu în cache-ul de date, preluând astfel memoria serverului.
În Fig. 2 arată paginile index create recent cu un factor de umplere de 100% - paginile sunt pline, iar ordinea fizică a paginilor coincide cu ordinea logică. În Fig. 3 prezintă fragmentarea care poate apărea după inserții / actualizări / ștergeri aleatorii.
Fig. 2. Paginile proaspete create ale indexului fără fragmentare, paginile sunt pline de 100%
Fragmentarea poate fi prevenită uneori prin modificarea schemei tabel / index, dar, așa cum am menționat mai sus, acest lucru poate fi foarte dificil sau imposibil. Dacă prevenirea este nerealistă, există modalități de a elimina fragmentarea după apariția acesteia - în special prin restaurarea sau reorganizarea indexului.
Unii utilizatori decid pur și simplu pentru a repara sau reconstrui toate indexurile în fiecare noapte sau în fiecare săptămână (de exemplu, folosind o variantă cu plan de serviciu), mai degrabă decât imaginind care indexurile sunt fragmentate și ce beneficii va elimina fragmentarea. In timp ce acest lucru poate fi o soluție bună pentru un DBA involuntar care doar dorește să aplice un fel de soluție cu un efort minim, acest lucru poate fi o alegere foarte proastă pentru baze de date și sisteme mai mari, în cazul în care resursele sunt în scurt de aprovizionare.
Indiferent de metoda utilizată, este recomandat să căutați în mod regulat și să remediați fragmentarea.
Procesorul de interogare face parte din SQL Server, care decide cum să execute interogarea - și anume, ce tabele și indexuri de utilizat și ce operații să le efectueze pentru a obține rezultate; acest lucru se numește un plan de interogare. Cea mai importantă contribuție la acest proces decizional este statisticile care descriu distribuția valorilor datelor pentru coloanele dintr-un tabel sau un index. Evident, pentru a fi util pentru procesorul de interogări, statisticile trebuie să fie corecte și proaspete, altfel pot fi selectate planuri de interogare neproductive.
Statisticile sunt create prin citirea datelor tabele / index și determinarea distribuției datelor pentru coloanele corespunzătoare. Statisticile pot fi construite prin verificarea tuturor valorilor datelor pentru o anumită coloană (scanare completă), dar poate fi de asemenea construită pe baza procentului de date specificat de utilizator (cazuri de testare). Dacă distribuția valorilor din coloană este relativ uniformă, atunci verificarea exemplelor poate fi suficientă, ceea ce face ca crearea și actualizarea statisticilor să fie mai rapidă decât atunci când este complet verificată.
Fig. 4. Modificarea setărilor bazei de date prin SQL Server Management Studio
Dacă aveți nevoie să actualizați statisticile ca parte a planului de întreținere obișnuit, trebuie să vă amintiți despre un truc. Atât UPDATE STATISTICS, cât și sp_updatestats utilizează implicit nivelul de colectare a datelor specificat anterior (dacă este specificat) - și poate fi mai mic decât scanarea completă. Recuperarea indexului actualizează automat statisticile cu o scanare completă. Dacă actualizați manual statisticile după restaurarea indexului, puteți obține statistici și mai puțin precise! Acest lucru se poate întâmpla dacă verificarea exemplelor din actualizare suprascrie manual scanarea completă creată prin restaurarea indexului. Pe de altă parte, atunci când indexul este reorganizat, statisticile nu sunt actualizate deloc.
Din nou, mulți au un plan de întreținere care actualizează toate statisticile în orice moment înainte sau după restaurarea tuturor indexurilor - și fără să știe acest lucru, se pot dovedi statistici mai puțin corecte. Dacă selectați o recuperare simplă a tuturor indexurilor din când în când, atunci va avea grijă de statistici. Dacă selectați o cale mai complexă cu eliminarea fragmentării, merită să faceți și să mențineți statistici. Propun următoarele:
- Analizați indexurile și determinați ce index trebuie utilizat și cum să defragmentați.
- Actualizați statisticile pentru toate indexurile care au fost restaurate.
- Actualizați statisticile pentru toate coloanele care nu sunt indexate.
Detectarea defectelor
Am vorbit despre serviciul de performanță. Acum este momentul să treceți la povestea de detectare a daunelor și de atenuare a acestora.
Este foarte puțin probabil ca baza de date să conțină informații complet inutile pe care nimeni nu le pasă - cum puteți să vă asigurați că datele rămân nedeteriorate și recuperabile în caz de urgență? Detalii despre punerea împreună a unei strategii complete de recuperare în caz de catastrofe și disponibilitatea ridicată sunt dincolo de sfera de aplicare a acestui articol, dar puteți face câteva lucruri simple pentru a începe.
Marea majoritate a daunelor este cauzată de "echipament". De ce este în ghilimele? Ei bine, hardware-ul aici este de fapt un simbol pentru "ceva în subsistemul I / O, sub SQL Server". Subsistemul I / O constă din elemente cum ar fi sistemul de operare, driverele sistemului de fișiere, driverele de dispozitive, controlerele RAID, cablurile, rețeaua și driverele. Masa locurilor în care pot apărea probleme (și apar).
Una dintre cele mai frecvente probleme este o întrerupere a alimentării în momentul în care discul scrie pe pagina bazei de date. Dacă discul nu va fi în măsură să finalizeze înregistrarea, înainte de a fugit de energie electrică (sau în cazul în care operațiunile de scriere sunt în cache și sursă de alimentare redundantă nu este suficient pentru a curăța cache-ul de disc), rezultatul poate fi o imagine pagină incompletă pe disc. Acest lucru se poate întâmpla, deoarece o pagină de baze de date de 8 kilobyte conține de fapt 16 segmente de discuri de 512 de octeți contigue. O înregistrare incompletă ar putea scrie câteva sectoare din noua pagină, însă va lăsa unele sectoare din imaginea paginii anterioare. Această situație se numește o pagină sfâșiată. Cum puteți detecta când se întâmplă acest lucru?
SQL Server are un mecanism pentru detectarea acestei situații. Aceasta include salvarea mai multor biți din fiecare sector al paginii și scrierea unui șablon specific în locul lor (acest lucru are loc imediat înainte de a scrie pagina pe disc). Dacă șablonul sa schimbat în timpul citirii paginii, atunci SQL Server știe că pagina este "rupt" și dă o eroare.
Fig. 5. Setarea unui avertisment pentru toate erorile de gravitate 24
Dacă această comandă emite ceva, DBCC a găsit corupția în baza de date. Apoi intrebarea se transforma in: "Ce se intampla daca DBCC CHECKDB gaseste daune?". Aici, pe scenă, apar backup-uri.
În caz de avarie sau de altă defecțiune, cea mai eficientă modalitate de recuperare este restaurarea bazei de date din copii de rezervă. Bineînțeles, acest lucru presupune existența unor astfel de copii și că ei înșiși nu sunt afectați. Prea des, utilizatorii doresc să știe cum să facă o bază de date serios deteriorată să ruleze din nou atunci când nu au o copie de rezervă. Răspunsul simplu este că acest lucru este imposibil, cel puțin fără pierderi de date, ceea ce poate corupe întreaga logică de afaceri și integritatea datelor relaționale.
În al doilea rând, păstrați copiile de rezervă pentru câteva zile, în cazul în care unul dintre ele este deteriorat - vechea copie de rezervă este mai bună decât nici una. De asemenea, trebuie să verificați integritatea backup-urilor folosind comanda RESTORE WITH VERIFYONLY (din nou, consultați documentația online). Dacă parametrul WITH CHECKSUM a fost utilizat la crearea rezervelor, atunci când se utilizează comanda de verificare, se va verifica dacă suma de control de rezervă este corectă, precum și sumele de control ale paginilor din interiorul acesteia.
După cum puteți vedea, asigurarea funcționării și disponibilității bazei de date necesită mai multe sarcini obligatorii. Aici este ultima mea listă de verificare a administratorilor de baze de date neintenționate care au primit managementul bazei de date:
Am citat comanda T-SQL în articol, dar multe pot fi făcute de la Management Studio. Sper că v-am dat niște instrucțiuni utile pentru întreținerea eficientă a bazelor de date.
Articole similare
Trimiteți-le prietenilor: