Momentan sunteți offline, așteptați să vă reconectați la internet

Considerente privind setările „autogrow” și „autoshrink” din SQL Server

REZUMAT
Setările implicite autogrow și autoshrink vor fi funcționa automat fără reglaje pe multe dintre sistemele SQL Server. Însă, există medii în care nu trebuie să activați setările sau în care este necesar să reglați parametrii pentru autogrow și autoshrink. Acest articol vă oferă unele informații de fundal pentru a vă îndruma atunci când selectați setările pentru mediul dvs.
INFORMAŢII SUPLIMENTARE
Iată câteva lucruri de luat în considerare dacă decideți să reglați parametrii autogrow și autoshrink.

Cum configurez setările?

  1. Aveți posibilitatea să configurați setările autogrow și autoshrink utilizând una dintre următoarele:
    • O instrucțiune ALTER DATABASE (nu este disponibilă în SQL Server 7.0)
    • SQL Server Management Studio sau SQL Enterprise Manager
    • Procedura stocată sp_dboption (perimată în SQL Server 2005)
    Notă Dacă utilizați SQL Server 2005, utilizați SQL Server Management Studio în loc de SQL Enterprise Manager. Pentru mai multe informații despre cum se setează aceste setări în SQL Server 2005, accesați următoarele site-uri Web Microsoft Developer Network (MSDN):
    Cum să: Adăugați date sau fișiere jurnal la o bază de date (SQL Server Management Studio)
    http://msdn2.microsoft.com/ro-ro/library/ms189253.aspx
    Proprietățile bazei de date (Pagina de fișiere)
    http://msdn2.microsoft.com/ro-ro/library/ms180254.aspx
    De asemenea, aveți posibilitatea să configurați opțiunea autogrow când creați o bază de date.

    Aveți posibilitatea să vizualizați setările curente prin proprietățile bazei de date în SQL Enterprise Manager (SEM). Sau, aveți posibilitatea să executați următoarea comandă Transact-SQL:
    sp_helpdb [ [ @dbname= ] 'name' ]
  2. Rețineți că setările autogrow se aplică pentru fiecare fișier. De aceea, trebuie să le setați în cel puțin două locuri pentru fiecare bază de date (odată pentru fișierul de date principal și odată pentru fișierul jurnal principal). Dacă aveți mai multe fișiere de date și/sau jurnal, trebuie să setați opțiunile pentru fiecare fișier. În funcție de mediul dvs., este posibil ca în final să aveți setări diferite pentru fiecare fișier bază de date.

Care sunt implicațiile pentru performanță?

  • Dacă executați o tranzacție care necesită mai mult spațiu de înregistrare în jurnal decât este disponibil și ați activat opțiunea autogrow pentru jurnalul de tranzacții al acelei baze de date, atunci durata de timp necesară pentru efectuarea tranzacției va include timpul necesar pentru ca jurnalul tranzacției să crească cu cantitatea configurată. Dacă incrementul de creștere este mare sau există un alt factor care determină o durată mare de timp, interogarea în care deschideți tranzacția poate să nu reușească din cauza unei erori de expirare. Același gen de problemă poate apărea ca rezultat al unei creșteri automate a unei porțiuni de date din baza de date. Pentru a modifica configurația autogrow, consultați subiectul „ALTER DATABASE” din SQL Server Books Online.
  • Dacă executați o tranzacție mare care necesită creșterea jurnalului, atunci celelalte tranzacții care trebuie să scrie în jurnalul de tranzacții vor trebui să aștepte până când se termină operațiunea de creștere.
  • În cazul în care combinați opțiunile autogrow și autoshrink, este posibil să creați o suprasarcină nenecesară. Asigurați-vă că pragurile care declanșează operațiunile de creștere sau micșorare nu vor genera creșteri sau micșorări frecvente de dimensiune. De exemplu, este posibil să executați o tranzacție care determină creșterea jurnalului de tranzacție cu 100 MO până la momentul realizării ei. La un moment dat după aceea, se pornește autoshrink și micșorează jurnalul de tranzacții cu 100 MO. Apoi, executați aceeași tranzacție, care determină din nou creșterea jurnalului de tranzacții cu 100 MO. În acest exemplu, creați o suprasarcină nenecesară și o fragmentare potențială a fișierului jurnal, iar oricare dintre aceste două aspecte pot afecta negativ performanțele.
  • Fragmentarea fizică rezultată din modificarea dimensiunii fișierelor de date sau de jurnal pot afecta serios performanțele. Acest lucru este valabil indiferent dacă utilizați setări automate sau dacă creșteți sau micșorați frecvent aceste fișiere în mod manual.
  • Dacă creșteți baza de date cu incremente mici sau dacă o creșteți și o micșorați, aceste acțiuni pot avea ca rezultat fragmentarea discului. Fragmentarea discului poate provoca probleme de performanțe în unele circumstanțe. Un scenariu de incrementări mici de creștere poate să reducă, de asemenea, performanțele sistemului.
  • În SQL Server 2005 sau în versiunile anterioare, aveți posibilitatea să activați inițializarea instantanee a fișierelor. Inițializarea instantanee a fișierelor poate crește viteza alocării fișierelor doar pentru fișierele de date. Inițializarea instantanee a fișierelor nu se aplică pentru fișierele jurnal.
  • Dacă aveți multe creșteri de fișiere în fișierele jurnal, este posibil să aveți un număr excesiv de mare de fișiere jurnal virtuale (VLF). Astfel, pot apărea probleme de performanțe privind operațiunile online/de pornire ale bazei de date, operațiunile de reproducere, oglindire și capturare a modificărilor de date (CDC). În plus, acest lucru poate determina uneori probleme de performanțe privind modificările de date.

Practici recomandate

  • Pentru un sistem de producție gestionată, trebuie să luați în considerare ca setarea autogrow să fie doar o măsură de aplicat pentru creșterile neașteptate. Nu gestionați creșterea jurnalului și a datelor din funcționarea curentă folosind autogrow.
  • Aveți posibilitatea să utilizați programe de avertizare și monitorizare pentru a supraveghea proactiv dimensiunile fișierelor și creșterea acestora. Acest lucru vă ajută să evitați fragmentarea și vă permite să plasați aceste activități de întreținere în afara orelor de vârf.
  • Opțiunile AutoShrink și autogrow trebuie să fie evaluate cu grijă de un administrator de baze de date (DBA) calificat; ele nu trebuie să rămână negestionate.
  • Incrementul autogrow trebuie să fie suficient de mare pentru a evita problemele de performanță enumerate în secțiunea anterioară. Valoarea exactă de utilizat în setarea configurației și alegerea între o creștere procentuală și o creștere fixă a dimensiunii în MO depinde de mulți factori din mediul dvs. O primă regulă generală care poate fi utilizată pentru testare este să stabiliți setarea autogrow la aproximativ o optime din dimensiunea fișierului.
  • Activați setarea <MAXSIZE> pentru fiecare fișier pentru a împiedica un fișier să crească până la punctul în care utilizează tot spațiul disponibil pe disc.
  • Mențineți dimensiunea tranzacțiilor cât mai mică posibil pentru a împiedica creșterea neplanificată a fișierului.

De ce trebuie să mă preocup de spațiul de pe disc dacă setările de dimensiune sunt controlate automat?

  • Setarea autogrow nu poate crește dimensiunea bazei de date dincolo de limitele spațiului de disc disponibil pe unitățile pentru care sunt definite fișierele. De aceea, dacă vă bazați pe funcționalitatea autogrow pentru a dimensiona baza dvs. de date, este în continuare necesar să verificați în mod independent spațiul disponibil de pe hard disk. Setarea autogrow este, de asemenea, limitată de parametrul MAXSIZE pe care îl selectați pentru fiecare fișier. Pentru a reduce posibilitatea de a rămâne fără spațiu, aveți posibilitatea să monitorizați contorul Performance Monitor SQL Server: Databases Object :Data File(s) Size (KB) și să configurați o avertizare pentru momentul în care baza de date atinge o anumită dimensiune.
  • Creșterea neplanificată a fișierelor de date sau de jurnal poate ocupa spațiu pe care alte aplicații se așteaptă să îl găsească disponibil și poate determina apariția de probleme în alte aplicații.
  • Incrementul de creștere al jurnalului de tranzacții trebuie să fie suficient de mare pentru a devansa nevoile unității de tranzacții. Chiar și având opțiunea autogrow activată, este posibil să primiți un mesaj că jurnalul de tranzacții este plin, dacă nu poate crește suficient de repede pentru a satisface nevoile interogării dvs.
  • SQL Server nu testează constant pentru a găsi baze de date care au atins pragul configurat pentru autoshrink. În schimb, cauză bazele de date disponibile și o găsește pe prima care este configurată pentru micșorare automată. Verifică acea bază de date și o micșorează dacă este necesar. Apoi, așteaptă câteva minute înainte să verifice următoarea bază de date configurată cu opțiunea autoshrink. Cu alte cuvinte, SQL Server nu verifică toate bazele de date odată și nu le micșorează pe toate odată. Va trece prin toate bazele de date prin rotație pentru a micșora sarcina de-a lungul unei perioade de timp. De aceea, în funcție de numărul de baze de date dintr-o anumită instanță de SQL Server pe care le-ați configurat să se micșoreze automat, pot trece câteva ore de la momentul în care baza de date atinge pragul până la momentul în care se micșorează efectiv.
REFERINŢE
Pentru mai multe informații despre creșterea și micșorarea bazei de date și fișierelor jurnal, faceți clic pe următoarele numere de articole pentru a le vizualiza în Baza de cunoștințe Microsoft:
256650 Cum se micșorează jurnalul de tranzacții SQL Server 7.0 (articolul poate să fie în limba engleză)
272318 Micșorarea jurnalului de tranzacții în SQL Server 2000 cu DBCC SHRINKFILE (articolul poate să fie în limba engleză)
317375 Un jurnal de tranzacții crește neașteptat sau devine plin pe un computer care execută SQL Server (articolul poate să fie în limba engleză)
247751 Eroare: Planul de întreținere al bazei de date nu micșorează baza de date (articolul poate să fie în limba engleză)
305635 Se produce o expirare la extinderea automată a bazei de date (articolul poate să fie în limba engleză)
949523 Latența unei reproduceri tranzacționale este mare în SQL Server 2005 când valoarea proprietății „Initial Size” și valoarea proprietății Autogrowth sunt mici (articolul poate să fie în limba engleză)
Pentru mai multe informații despre inițializarea fișierelor de baze de date, vizitați următorul site Web Microsoft Developer Network (MSDN): Pentru mai multe informații despre activarea inițializării instantanee a fișierelor, vizitați următorul site Web Microsoft: Pentru mai multe informații despre arhitectura fizică a jurnalului de tranzacții, vizitați următorul site Web MSDN:
SQL Server Books Online; subiecte: „Transaction Log Physical Architecture”; „Shrinking the Transaction Log”
shrinking growing auto shrink grow expand collapse reduce 1105 log full empty large smaller larger Could not allocate space for object
Proprietăți

ID articol: 315512 - Ultima examinare: 09/17/2011 08:49:00 - Revizie: 3.0

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbsqlmanagementtools kbinfo KB315512
Feedback