Overwegingen voor de instellingen voor automatisch groeien en automatisch verkleinen in SQL Server

  • Artikel

Originele productversie: SQL Server
Origineel KB-nummer: 315512

Samenvatting

De standaardinstellingen voor automatisch groeien en automatisch verkleinen zijn geschikt voor veel SQL Server systemen. Er zijn echter omgevingen waarin u mogelijk de parameters voor automatisch groeien en automatisch verkleinen moet aanpassen. Dit artikel bevat achtergrondinformatie om u te helpen bij het selecteren van deze instellingen voor uw omgeving.

Hier volgen enkele zaken die u moet overwegen als u besluit uw parameters voor automatisch groeien en automatisch verkleinen af te stemmen.

Hoe kan ik de instellingen configureren

  1. U kunt de instellingen voor automatisch groeien en automatisch verkleinen configureren of wijzigen met behulp van een van de volgende opties:

    Opmerking

    Zie Gegevens of Logboekbestanden toevoegen aan een database voor meer informatie over het instellen van deze instellingen op databasebestandsniveau.

    U kunt ook de optie voor automatisch groeien configureren wanneer u een database maakt.

    Voer de volgende Transact-SQL-opdracht uit om de huidige instellingen weer te geven:

    sp_helpdb [ [ @dbname= ] 'name' ]

  2. Houd er rekening mee dat de instellingen voor automatisch groeien per bestand zijn. Daarom moet u deze op ten minste twee plaatsen instellen voor elke database (één voor het primaire gegevensbestand en één voor het primaire logboekbestand). Als u meerdere gegevens en/of logboekbestanden hebt, moet u de opties voor elk bestand instellen. Afhankelijk van uw omgeving kunt u eindigen met verschillende instellingen voor elk databasebestand.

Overwegingen voor AUTO_SHRINK

AUTO_SHRINKis een databaseoptie in SQL Server. Wanneer u deze optie inschakelt voor een database, komt deze database in aanmerking voor verkleining door een achtergrondtaak. Met deze achtergrondtaak worden alle databases geëvalueerd die voldoen aan de criteria voor het verkleinen en verkleinen van de gegevens of logboekbestanden.

U moet het instellen van deze optie voor de databases in een SQL Server exemplaar zorgvuldig evalueren. Frequente groei- en verkleiningsbewerkingen kunnen leiden tot verschillende prestatieproblemen.

  • Als meerdere databases regelmatig verkleinings- en groeibewerkingen ondergaan, leidt dit gemakkelijk tot fragmentatie op bestandssysteemniveau. Dit kan een ernstige invloed hebben op de prestaties. Dit geldt ongeacht of u de automatische instellingen gebruikt of dat u de bestanden regelmatig handmatig laat groeien en verkleinen.

  • Nadat AUTO_SHRINK het gegevens- of logboekbestand is verkleind, kan een volgende DML- of DDL-bewerking aanzienlijk vertragen als er ruimte nodig is en de bestanden moeten groeien.

  • De AUTO_SHRINK achtergrondtaak kan resources in beslag nemen wanneer er veel databases zijn die moeten worden verkleind.

  • De AUTO_SHRINK achtergrondtaak moet vergrendelingen en andere synchronisatie verkrijgen die een conflict kunnen veroorzaken met andere reguliere toepassingsactiviteiten.

Overweeg databases in te stellen op een vereiste grootte en deze vooraf te laten groeien. Laat de ongebruikte ruimte in de databasebestanden staan als u denkt dat de gebruikspatronen van de toepassing deze opnieuw nodig hebben. Dit kan voorkomen dat de databasebestanden regelmatig worden verkleind en groeit.

Overwegingen voor AUTOGROW

  • Als u een transactie uitvoert waarvoor meer logboekruimte is vereist dan beschikbaar is en u de optie voor automatisch groeien hebt ingeschakeld voor het transactielogboek van die database, omvat de tijd die nodig is om de transactie te voltooien de tijd die het transactielogboek nodig heeft om met de geconfigureerde hoeveelheid te groeien. Als de toename van de groei groot is of als er een andere factor is waardoor het lang duurt, kan de query waarin u de transactie opent mislukken vanwege een time-outfout. Hetzelfde soort probleem kan het gevolg zijn van een automatische groei van het gegevensgedeelte van uw database.

  • Als u een grote transactie uitvoert waarvoor het logboek moet worden uitgebreid, moeten andere transacties waarvoor een schrijfbewerking naar het transactielogboek is vereist, ook wachten totdat de groeibewerking is voltooid.

  • Als u veel bestandsgroeien in uw logboekbestanden hebt, hebt u mogelijk een te groot aantal virtuele logboekbestanden (VLF). Dit kan leiden tot prestatieproblemen met opstart-/onlinebewerkingen van databases, replicatie, spiegeling en Change Data Capture (CDC). Bovendien kan dit soms prestatieproblemen veroorzaken met gegevenswijzigingen.

Opmerking

Als u de opties voor automatisch groeien en automatisch verkleinen combineert, kunt u onnodige overhead creëren. Zorg ervoor dat de drempelwaarden die de groei- en verkleiningsbewerkingen activeren, niet frequente wijzigingen in de grootte veroorzaken. U kunt bijvoorbeeld een transactie uitvoeren waardoor het transactielogboek met 100 MB groeit op het moment dat het wordt doorgevoerd. Enige tijd daarna wordt de autoshrink gestart en wordt het transactielogboek met 100 MB verkleind. Vervolgens voert u dezelfde transactie uit en wordt het transactielogboek weer met 100 MB vergroot. In dat voorbeeld creëert u onnodige overhead en mogelijk fragmentatie van het logboekbestand, die beide de prestaties negatief kunnen beïnvloeden.

Als u uw database met kleine stappen vergroot, of als u deze vergroot en vervolgens verkleint, kunt u schijffragmentatie hebben. Schijffragmentatie kan in sommige omstandigheden prestatieproblemen veroorzaken. Een scenario met kleine groeiverhogingen kan ook de prestaties van uw systeem verminderen.

In SQL Server kunt u direct initialisatie van bestanden inschakelen. Direct initialiseren van bestanden versnelt bestandstoewijzingen alleen voor gegevensbestanden. Direct initialiseren van bestanden is niet van toepassing op logboekbestanden. Zie Initialisatie van direct databasebestand voor meer informatie.

Aanbevolen procedures voor automatisch groeien en automatisch verkleinen

  • Voor een beheerd productiesysteem moet u automatische groei beschouwen als een gebeurtenis voor onverwachte groei. Beheer uw gegevens en logboekgroei niet dagelijks met automatische groei.

  • U kunt waarschuwingen of bewakingsprogramma's gebruiken om bestandsgrootten te bewaken en bestanden proactief te laten groeien. Hierdoor voorkomt u fragmentatie en kunt u deze onderhoudsactiviteiten verplaatsen naar niet-piekuren.

  • Autoshrink en autogrow moeten zorgvuldig worden geëvalueerd door een getrainde databasebeheerder (DBA); Ze mogen niet onbeheerd blijven.

  • Uw toename van automatische groei moet groot genoeg zijn om de prestatieboetes te voorkomen die in de vorige sectie worden vermeld. De exacte waarde die moet worden gebruikt in uw configuratie-instelling en de keuze tussen een groeipercentage en een specifieke MB-groottegroei, is afhankelijk van veel factoren in uw omgeving. Een algemene vuistregel die u kunt gebruiken voor het testen, is om uw instelling voor automatische groei in te stellen op ongeveer acht de grootte van het bestand.

  • Schakel de \<MAXSIZE> instelling voor elk bestand in om te voorkomen dat een bestand groeit tot een punt waarop alle beschikbare schijfruimte wordt gebruikt.

  • Houd de grootte van uw transacties zo klein mogelijk om ongeplande bestandsgroei te voorkomen.

Waarom moet ik me zorgen maken over schijfruimte als de grootte-instellingen automatisch worden beheerd

  • Met de instelling voor automatische groei kan de databasegrootte niet groter worden dan de limieten van de beschikbare schijfruimte op de stations waarvoor bestanden zijn gedefinieerd. Dus als u afhankelijk bent van de functionaliteit voor automatisch groeien om de grootte van uw databases te vergroten, moet u de beschikbare schijfruimte nog steeds onafhankelijk controleren. De instelling voor automatische groei wordt ook beperkt door de MAXSIZE parameter die u voor elk bestand selecteert. Als u de kans op onvoldoende ruimte wilt beperken, kunt u de prestatiemeteritems controleren SQL Server: Databases Object: Gegevensbestand(en) Grootte (KB) en een waarschuwing instellen wanneer de database een bepaalde grootte bereikt.

  • Niet-geplande groei van gegevens of logboekbestanden kan ruimte in beslag nemen die andere toepassingen verwachten beschikbaar te zijn en kan ertoe leiden dat deze andere toepassingen problemen ondervinden.

  • De groei van uw transactielogboek moet groot genoeg zijn om de behoeften van uw transactie-eenheden voor te blijven. Zelfs als automatische groei is ingeschakeld, kunt u een bericht ontvangen dat het transactielogboek vol is, als het niet snel genoeg kan groeien om aan de behoeften van uw query te voldoen.

  • SQL Server test niet voortdurend op databases die de geconfigureerde drempelwaarde voor autoshrink hebben bereikt. In plaats daarvan wordt gekeken naar de beschikbare databases en wordt de eerste gevonden die is geconfigureerd voor autoshrink. Deze database wordt gecontroleerd en indien nodig verkleind. Vervolgens wacht het enkele minuten voordat de volgende database wordt gecontroleerd die is geconfigureerd voor autoshrink. Met andere woorden, SQL Server controleert niet alle databases tegelijk en verkleint ze allemaal tegelijk. Het zal de databases op een round robin-manier doorlopen om de belasting over een bepaalde periode te verdelen. Afhankelijk van het aantal databases dat u hebt geconfigureerd voor autoshrink op een bepaald SQL Server exemplaar, kan het enkele uren duren vanaf het moment dat de database de drempelwaarde bereikt totdat deze daadwerkelijk wordt verkleind.

Verwijzingen