Condividi tramite

Considerazioni sulle impostazioni di aumento e compattazione automatici 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, in alcuni ambienti potrebbe essere necessario modificare i parametri per l'aumento automatico delle dimensioni e la compattazione automatica. Questo articolo contiene alcune informazioni di base utili per la selezione di queste impostazioni per l'ambiente.

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

Ricerca per categorie configurare le impostazioni

  1. È possibile configurare o modificare le impostazioni autogrow e autoshrink usando una delle opzioni 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.

    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 sono presenti più file di dati e/o di log, è necessario impostare le opzioni in ogni file. A seconda dell'ambiente, è possibile terminare con 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 riduzione in base a 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 operazioni di aumento e riduzione frequenti possono causare vari problemi di prestazioni.

  • Se più database vengono sottoposti a operazioni frequenti di compattazione e aumento, ciò comporterà facilmente la frammentazione a livello di file system. Ciò può avere un impatto grave sulle prestazioni. Questo vale se si usano le impostazioni automatiche o se si aumentano manualmente e si riducono 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 significativamente se è necessario spazio e i file devono crescere.

  • L'attività AUTO_SHRINK in background può occupare risorse quando sono presenti molti database che richiedono una riduzione.

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

Valutare la possibilità di impostare i database su una dimensione richiesta e pre-aumentarli. Lasciare lo spazio inutilizzato nei file di database se si ritiene che i modelli di utilizzo dell'applicazione saranno di nuovo necessari. Ciò può impedire la riduzione 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 e si è attivata l'opzione di aumento automatico per il log delle transazioni del database, il tempo necessario per il completamento della transazione includerà il tempo necessario per aumentare il log delle transazioni in base all'importo configurato. Se l'incremento di crescita è elevato o c'è un altro fattore che la causa richiede molto tempo, 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 altre transazioni che richiedono una scrittura nel log delle transazioni dovranno attendere il completamento dell'operazione di aumento.

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

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 compattazione non provocheranno modifiche frequenti alle dimensioni massime e ridotte. Ad esempio, è possibile eseguire una transazione che causa l'aumento del log delle transazioni di 100 MB al momento del commit. Tempo dopo l'avvio della funzione autoshrink 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.

Se si aumenta il database con incrementi di piccole dimensioni o se si aumenta e quindi si riduce, è possibile terminare 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 nel 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 dei file di database.

Procedure consigliate per l'aumento automatico e la compattazione automatica

  • Per un sistema di produzione gestito, è necessario considerare la scalabilità automatica come semplice emergenza per una crescita imprevista. La crescita dei dati e dei log non viene gestita quotidianamente con l'aumento automatico delle dimensioni.

  • È 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 di spostare queste attività di manutenzione in ore non di punta.

  • L'autoshrink e l'aumento automatico devono essere valutati attentamente da un amministratore del database (DBA) sottoposto a training; Non devono essere lasciati non gestiti.

  • L'incremento automatico deve essere sufficientemente grande per evitare le penalità delle 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 MB dipende da molti fattori nell'ambiente. Una regola generale che è possibile usare per il test consiste nell'impostare l'impostazione di aumento automatico su circa otto dimensioni del file.

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

  • Mantenere le dimensioni delle transazioni il più piccolo possibile per evitare l'aumento dei file non pianificati.

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 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 Monitor prestazioni SQL Server: Oggetto database: Dimensioni file di dati (KB) e configurare un avviso quando il database raggiunge una determinata dimensione.

  • 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 con tali altre applicazioni.

  • L'incremento della crescita del log delle transazioni deve essere sufficientemente grande da rispettare le esigenze delle unità di transazione. Anche con 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.

  • 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 riduce il database, 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 li compatta contemporaneamente. Il funzionamento dei database avviene in modo round robin per sfalsare il carico in un determinato periodo di 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.

Riferimenti