Considerazioni sulle impostazioni di aumento automatico e autoshrink in SQL Server

  • Articolo

Versione originale del prodotto: SQL Server
Numero KB originale: 315512

Riepilogo

Le impostazioni predefinite di aumento automatico e autoshrink sono appropriate in molti sistemi SQL Server. Tuttavia, esistono ambienti in cui potrebbe essere necessario regolare i parametri autogrow e autoshrink. Questo articolo fornisce alcune informazioni di base che consentono di selezionare queste impostazioni per l'ambiente.

Ecco alcuni aspetti da considerare se si decide di ottimizzare i parametri autogrow e autoshrink.

Ricerca per categorie configurare le impostazioni

  1. È possibile configurare o modificare le impostazioni di aumento automatico e autoshrink usando una delle opzioni seguenti:

    Nota

    Per altre informazioni su come impostare queste impostazioni a livello di file di database, vedere Aggiungere file di dati o di log a un database.

    È anche possibile configurare l'opzione di aumento automatico quando si crea un database.

    Per visualizzare le impostazioni correnti, eseguire il comando Transact-SQL seguente:

    sp_helpdb [ [ @dbname= ] 'name' ]

  2. Tenere presente che le impostazioni di aumento automatico sono per ogni file. Pertanto, è necessario impostarli in almeno due posizioni per ogni database (uno per il file di dati primario e uno per il file di log primario). Se si dispone di più file di dati e/o di log, è necessario impostare le opzioni in ogni file. A seconda dell'ambiente in uso, è possibile che si verifichino impostazioni diverse per ogni file di database.

Considerazioni per AUTO_SHRINK

AUTO_SHRINKè un'opzione di database in SQL Server. Quando si abilita questa opzione per un database, questo database diventa idoneo per la compattazione da parte di un'attività in background. Questa attività in background valuta tutti i database che soddisfano i criteri per la compattazione e la compattazione dei file di dati o di log.

È necessario valutare attentamente l'impostazione di questa opzione per i database in un'istanza di SQL Server. Le frequenti operazioni di aumento e compattazione possono causare vari problemi di prestazioni.

  • Se più database vengono sottoposti a frequenti operazioni di compattazione e crescita, ciò comporterà facilmente la frammentazione a livello di file system. Ciò può avere un impatto grave sulle prestazioni. Questo è vero se si usano le impostazioni automatiche o se si aumenta e si compattano manualmente i file di frequente.

  • Dopo AUTO_SHRINK aver compattato correttamente i dati o il file di log, un'operazione DML o DDL successiva può rallentare in modo significativo se lo spazio è necessario e i file devono aumentare.

  • L'attività AUTO_SHRINK in background può richiedere risorse quando sono presenti molti database che richiedono la compattazione.

  • L'attività AUTO_SHRINK in background dovrà acquisire blocchi e altre sincronizzazioni che possono essere in conflitto con altre normali attività dell'applicazione.

È consigliabile impostare i database su una dimensione necessaria e pre-aumentarli. Lasciare lo spazio inutilizzato nei file di database se si ritiene che i modelli di utilizzo dell'applicazione ne avranno di nuovo bisogno. Ciò può impedire la compattazione e la crescita frequenti dei file di database.

Considerazioni per AUTOGROW

  • Se si esegue una transazione che richiede più spazio di log di quello disponibile ed è stata attivata l'opzione di aumento automatico per il log delle transazioni di tale database, il tempo necessario per completare la transazione includerà il tempo necessario per l'aumento del log delle transazioni in base alla quantità configurata. Se l'incremento di crescita è elevato o è presente un altro fattore che causa un tempo prolungato, la query in cui si apre la transazione potrebbe non riuscire a causa di un errore di timeout. Lo stesso tipo di problema può derivare da un aumento automatico della parte dei dati del database.

  • Se si esegue una transazione di grandi dimensioni che richiede l'aumento del log, anche le altre transazioni che richiedono una scrittura nel log delle transazioni dovranno attendere il completamento dell'operazione di crescita.

  • Se nei file di log sono presenti molte cresczioni di file, potrebbe essere presente un numero eccessivamente elevato di file di log virtuali (VLF). Ciò può causare problemi di prestazioni con operazioni online/di avvio del database, replica, mirroring e change data capture (CDC). Inoltre, questo può talvolta causare problemi di prestazioni con le modifiche dei dati.

Nota

Se si combinano le opzioni autogrow e autoshrink, si potrebbe creare un sovraccarico non necessario. Assicurarsi che le soglie che attivano le operazioni di aumento e riduzione non causeranno frequenti modifiche delle dimensioni. Ad esempio, è possibile eseguire una transazione che causa l'aumento del log delle transazioni di 100 MB al momento del commit. Qualche tempo dopo l'avvio del file autoshrink e la riduzione del log delle transazioni di 100 MB. Si esegue quindi la stessa transazione e il log delle transazioni aumenta nuovamente di 100 MB. In questo esempio si sta creando un sovraccarico non necessario e potenzialmente la frammentazione del file di log, una delle quali può influire negativamente sulle prestazioni.

Se si aumenta il database di piccoli incrementi o se lo si aumenta e quindi lo si riduce, si può finire con la frammentazione del disco. La frammentazione del disco può causare problemi di prestazioni in alcune circostanze. Uno scenario di piccoli incrementi di crescita può anche ridurre le prestazioni del sistema.

In SQL Server è possibile abilitare l'inizializzazione immediata dei file. L'inizializzazione immediata dei file accelera le allocazioni di file solo per i file di dati. L'inizializzazione immediata dei file non si applica ai file di log. Per altre informazioni, vedere Inizializzazione immediata file del database.

Procedure consigliate per aumento automatico e autoshrink

  • Per un sistema di produzione gestito, è necessario considerare la crescita automatica come una semplice emergenza per una crescita imprevista. Non gestire quotidianamente la crescita dei dati e dei log con l'aumento automatico.

  • È possibile usare avvisi o programmi di monitoraggio per monitorare le dimensioni dei file e aumentare i file in modo proattivo. Ciò consente di evitare la frammentazione e consente di spostare queste attività di manutenzione in ore non di punta.

  • Autoshrink e autogrow devono essere valutati attentamente da un amministratore di database sottoposto a training; Non devono essere lasciati non gestiti.

  • L'incremento di aumento automatico deve essere sufficientemente grande da evitare le penalità per le prestazioni elencate nella sezione precedente. Il valore esatto da usare nell'impostazione di configurazione e la scelta tra una crescita percentuale e una crescita specifica delle dimensioni di MB dipendono da molti fattori nell'ambiente. Una regola generale che è possibile usare per il test consiste nell'impostare l'impostazione di aumento automatico su circa 8 dimensioni del file.

  • Attivare l'impostazione \<MAXSIZE> per ogni file per evitare che un file aumenti fino a un punto in cui usa tutto lo spazio disponibile su disco.

  • Mantenere le dimensioni delle transazioni il più piccole possibile per evitare la crescita non pianificata dei file.

Perché è necessario preoccuparsi dello spazio su disco se le impostazioni delle dimensioni vengono controllate automaticamente

  • L'impostazione di aumento automatico non può aumentare le dimensioni del database oltre i limiti dello spazio su disco disponibile nelle unità per cui sono definiti i file. Pertanto, se si basa sulla funzionalità di aumento automatico delle dimensioni dei database, è comunque necessario controllare in modo indipendente lo spazio disponibile su disco rigido. L'impostazione di aumento automatico è limitata anche dal MAXSIZE parametro selezionato per ogni file. Per ridurre la possibilità di esaurimento dello spazio, è possibile monitorare il contatore Monitor prestazioni SQL Server: Oggetto Database: Dimensioni file di dati (KB) e impostare un avviso quando il database raggiunge determinate dimensioni.

  • La crescita non pianificata dei file di dati o di log può richiedere spazio che altre applicazioni si aspettano di essere disponibili e potrebbero causare problemi alle altre applicazioni.

  • L'incremento di crescita del log delle transazioni deve essere sufficientemente grande da rimanere al passo con le esigenze delle unità di transazione. Anche se l'aumento automatico è attivato, è possibile ricevere un messaggio che indica che il log delle transazioni è pieno, se non può crescere abbastanza velocemente da soddisfare le esigenze della query.

  • SQL Server non esegue costantemente il test per i database che hanno raggiunto la soglia configurata per autoshrink. Al contrario, esamina i database disponibili e trova il primo configurato per la creazione automatica del programma. Il database viene controllato e compattato, se necessario. Attende quindi alcuni minuti prima di controllare il database successivo configurato per autoshrink. In altre parole, SQL Server non controlla tutti i database contemporaneamente e li compatta contemporaneamente. Funzionerà attraverso i database in modo round robin per sfalsare il carico in un periodo di tempo. Di conseguenza, a seconda del numero di database configurati per il salvataggio automatico in una particolare istanza di SQL Server, potrebbero essere necessari diverse ore dal momento in cui il database raggiunge la soglia fino a quando non viene effettivamente compattato.

Riferimenti