Considerazioni sulle impostazioni di aumento e riduzione automatiche in SQL Server

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

Riepilogo

Le impostazioni autogrow e autoshrink controllano come SQL Server aumenta automaticamente (aumento automatico) o riduce (autoshrink) le dimensioni dei file di dati e di log del database. Le impostazioni predefinite sono appropriate in molti sistemi SQL Server, ma alcuni ambienti richiedono di modificarle. Questo articolo illustra come queste impostazioni influiscono sulle prestazioni e fornisce procedure consigliate per evitare transazioni lente, frammentazione dei file e utilizzo dello spazio su disco non pianificato.

Questo articolo è destinato agli amministratori di database (DBA) che configurano il comportamento di crescita e riduzione dei file per i database in SQL Server.

Come si configurano le impostazioni autogrow e autoshrink?

  1. Configurare o modificare le impostazioni autogrow e autoshrink usando uno dei metodi seguenti:

    Note

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

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

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

    sp_helpdb [ [ @dbname= ] 'name' ]

    È anche possibile eseguire una query sulla vista del catalogo sys.database_files per il database corrente o sulla vista del catalogo sys.master_files per tutti i database nell'istanza. Queste visualizzazioni includono l'incremento della crescita, se la crescita è una percentuale e le dimensioni massime del file. Per verificare se AUTO_SHRINK è attivato, eseguire una query sulla is_auto_shrink_on colonna in sys.databases.

  3. 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 sono presenti più file di dati e di log, è necessario impostare le opzioni in ogni file. A seconda dell'ambiente, è possibile che si verifichino impostazioni diverse per ogni file di database.

Quali sono le impostazioni predefinite di autogrow?

In SQL Server 2016 (13.x) e versioni successive, il valore predefinito FILEGROWTH è 64 MB per i file di dati e di log. Le versioni precedenti usano valori predefiniti più piccoli o basati su percentuali. Per l'impostazione predefinita per ogni versione, vedere Opzioni file e filegroup ALTER DATABASE (Transact-SQL).

Cosa devo considerare prima di attivare AUTO_SHRINK?

AUTO_SHRINK è un'opzione di database in SQL Server. Quando si abilita questa opzione per un database, un'attività in background può compattare il database. Questa attività in background valuta tutti i database che soddisfano i criteri per la riduzione e quindi riduce i file di dati o di log.

Valutare attentamente l'impostazione di questa opzione per i database in un'istanza di SQL Server. Le operazioni di aumento e riduzione frequenti possono causare vari problemi di prestazioni:

  • Se più database vengono sottoposti a operazioni di riduzione e aumento frequenti, la frammentazione del file system può verificarsi. La frammentazione può influire gravemente sulle prestazioni. Questo impatto si verifica sia che si usino le impostazioni automatiche, sia che si aumentino manualmente e si riducano spesso i file.

  • Dopo che AUTO_SHRINK ha compattato correttamente i dati o il file di log, un'operazione DML o DDL successiva può rallentare significativamente se è necessario spazio e i file devono aumentare di dimensioni.

  • L'attività AUTO_SHRINK in background può usare risorse significative quando molti database richiedono la riduzione.

  • L'attività AUTO_SHRINK in background deve acquisire lock e altri oggetti di sincronizzazione che possono essere in conflitto con la regolare attività dell'applicazione.

Impostare invece i database sulle dimensioni necessarie e preallocarli. Lasciare lo spazio inutilizzato nei file di database se si ritiene che i modelli di utilizzo dell'applicazione lo richiedano di nuovo. Per altre informazioni sulla compattazione dei file, vedere Compattare un database e DBCC SHRINKFILE.

Cosa occorre considerare prima di affidarsi ad autogrow?

L'aumento automatico può influire sulla durata delle transazioni, su altre sessioni e sul numero di file di log virtuali nel log delle transazioni:

  • Se si esegue una transazione che richiede più spazio di log rispetto a quello disponibile e l'aumento automatico è abilitato per il log delle transazioni del database, il tempo necessario per completare la transazione include il tempo necessario per aumentare il log delle transazioni in base all'importo configurato. Se l'incremento di crescita è elevato o un altro fattore fa sì che la crescita richieda molto tempo, la query in cui si apre la transazione potrebbe non riuscire a causa di un errore di timeout. Lo stesso problema può derivare da una crescita automatica (autogrow) della parte dei dati del database.

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

  • Se sono presenti molti aumenti delle dimensioni dei file di log, è possibile che si disponga di un numero eccessivamente elevato di virtual log files (VLFs). Molti VLF possono compromettere l'avvio del database, le operazioni online e altre funzionalità dipendenti dal log. Per controllare il numero di VLF, eseguire una query sulla funzione di gestione dinamica sys.dm_db_log_info.

Se si aumenta il database con incrementi di piccole dimensioni o se lo si aumenta e quindi si riduce, si può causare la frammentazione del disco. La frammentazione del disco può causare problemi di prestazioni in alcune circostanze. Anche piccoli aumenti graduali possono ridurre le prestazioni del sistema.

Note

Se si combinano le opzioni autogrow e autoshrink, è possibile creare un sovraccarico non necessario. Assicurarsi che le soglie che attivano le operazioni di aumento e riduzione non causino frequenti aumenti e diminuzioni 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, la funzione autoshrink viene avviata e compatta il log delle transazioni di 100 MB. Quindi, si esegue la stessa transazione e il log delle transazioni aumenta di nuovo di 100 MB. In questo esempio si crea un sovraccarico non necessario e si crea potenzialmente la frammentazione del file di log, che può influire negativamente sulle prestazioni.

In che modo l'inizializzazione immediata dei file influisce sull'aumento automatico?

L'inizializzazione immediata dei file (IFI) velocizza le operazioni di aumento automatico dei file di dati perché SQL Server non azzera il nuovo spazio. In SQL Server 2022 (16.x) e versioni successive, gli eventi di aumento automatico del log delle transazioni fino a 64 MB possono trarre vantaggio anche da IFI. Senza IFI, l'aumento automatico richiede più tempo man mano che l'incremento della crescita aumenta. Per i requisiti e le limitazioni, vedere Inizializzazione immediata dei file di database.

Quali sono le procedure consigliate per autogrow e autoshrink?

  • Per un sistema di produzione gestito, considerare la crescita automatica solo come misura di emergenza per una crescita imprevista. Non gestire i dati e la crescita dei log su base giornaliera usando l'aumento automatico.

  • Usare avvisi o programmi di monitoraggio per monitorare le dimensioni dei file e aumentare le dimensioni dei file in modo proattivo. Questo approccio consente di evitare la frammentazione e di spostare queste attività di manutenzione in ore non di punta.

  • Chiedere a un amministratore del database (DBA) adeguatamente formato di valutare attentamente autoshrink e autogrow. Non lasciarli senza gestione.

  • Imposta l'incremento di crescita automatica su un valore sufficientemente elevato per evitare le penalità sulle prestazioni descritte in precedenza in questo articolo. Il valore esatto da usare nell'impostazione di configurazione e la scelta tra una crescita percentuale e un incremento di dimensione specifico in MB dipendono da molti fattori nell'ambiente. Una regola generale che è possibile usare per il test consiste nell'impostare l'impostazione di incremento automatico su circa un ottavo della dimensione del file. Per le raccomandazioni sull'aumento del log delle transazioni, vedere Gestire le dimensioni del file di log delle transazioni.

  • Attivare l'impostazione MAXSIZE per ogni file per impedire che un singolo file venga a crescere fino a un punto in cui usa tutto lo spazio disponibile su disco.

  • Mantenere le dimensioni delle transazioni le più piccole possibile per evitare la crescita imprevista del file.

Perché è ancora necessario monitorare lo spazio su disco quando la crescita automatica è attivata?

  • 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 fa affidamento 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 esaurire lo spazio, è possibile monitorare il contatore SQL Server: Databases Object: Data File(s) Size (KB) e impostare un avviso quando il database raggiunge una determinata dimensione.

  • La crescita non pianificata dei file di dati o di log può occupare spazio che altre applicazioni si aspettano sia disponibile e potrebbe causare problemi con tali altre applicazioni.

  • L'incremento di crescita del log delle transazioni deve essere sufficientemente grande da soddisfare il fabbisogno 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 per soddisfare le esigenze della query. Per altre informazioni, vedere Risolvere i problemi relativi a un log delle transazioni completo (SQL Server errore 9002).

Con quale frequenza SQL Server controlla i database per la compattazione automatica?

SQL Server non testa costantemente i database che hanno raggiunto la soglia configurata per la compattazione automatica. Esamina invece i database disponibili e trova il primo configurato per la compattazione automatica. Controlla il database e ne riduce le dimensioni, se necessario. Attende quindi alcuni minuti prima di controllare il database successivo configurato per la compattazione automatica.

In altre parole, SQL Server non controlla tutti i database contemporaneamente e non li riduce tutti contemporaneamente. Scorre i database in modalità round-robin per distribuire il carico nel tempo. Di conseguenza, a seconda del numero di database configurati per la compattazione automatica in una determinata istanza di SQL Server, potrebbero essere necessarie diverse ore dal momento in cui il database raggiunge la soglia fino a quando non si riduce effettivamente.

Contenuti correlati

  • Ultimo aggiornamento: