Controlla le impostazioni di crescita automatica del database

Qualsiasi transazione che richiede la crescita dei dati o del file di registro include il tempo impiegato dall'operazione di crescita del file. Il file cresce in base alla dimensione dell'incremento definita daCRESCITA DEI FILEopzione. È possibile cercare gli eventi di crescita dei file nelle tracce del profiler di SQL Server. Se la crescita dei file richiede molto tempo, potresti riscontrare tipi di attesa comeASYNC_IO_COMPLETION, che si verifica quando l'elaborazione dei dati è molto lenta. Tali tipi di attesa non solo influiscono sulle prestazioni, ma possono anche causare timeout delle transazioni. Se tale transazione blocca le risorse richieste da altre transazioni, il timeout comporterebbe gravi problemi di blocco del server.

Per questo motivo, ti consigliamo di configurare le impostazioni di crescita automatica con molta attenzione. Tieni inoltre presente che:

• La crescita dei file è una delle operazioni più costose in SQL Server.

• La frequente crescita automatica in piccoli blocchi può portare alla frammentazione del disco.

• La crescita automatica frequente nei file di registro genera un numero elevato di file di registro virtuali (VLF) e influisce sulle prestazioni, come illustrato nellasezione precedente.

Tutti questi motivi potrebbero rallentare l'avvio del database e aumentare i tempi di backup e ripristino.

Idealmente, dovresti pre-crescere i file in modo proattivo, sulla base di un monitoraggio regolare. Scegli con attenzione se impostare la crescita automatica come percentuale o come valore statico (in MB). In genere, l'impostazione della crescita automatica su un ottavo della dimensione del file è un buon punto di partenza, ma potrebbe non essere la scelta giusta. (Ad esempio, questa percentuale sarebbe troppo alta se il file di dati ha una dimensione di diversi TB.)

Nella maggior parte dei casi, un valore di crescita automatica di 1024 MB è ideale per i file di dati nella maggior parte dei database di grandi dimensioni. Per i file di registro, 512 MB sono un buon punto di partenza. Per le misure di emergenza, ti consigliamo vivamente di impostare il valore di crescita automatica, ma di espandere i file manualmente per alcuni mesi in base alle tendenze passate.

Nota

L'impostazione della crescita automatica deve essere una misura di emergenza, quindi è consigliabile impostarla dopo aver preallocato lo spazio di archiviazione su un file.

È possibile modificare le impostazioni di crescita automatica utilizzandoSQL Server Management Studio (SSMS)oTransact-SQL. La seguente illustrazione della schermata mostra le impostazioni di crescita automatica in SSMS.

Quando si utilizza ilCRESCITA DEI FILEopzione per dati e file di registro, scegli con attenzione se impostarlo come percentuale o come valore statico (in MB). L'impostazione di una percentuale comporta una crescita dei file sempre maggiore, quindi potresti preferire utilizzare una dimensione statica per un migliore controllo del rapporto di crescita.

• Nelle versioni precedenti a SQL Server 2022 (16.x), i log delle transazioni non possono utilizzare l'inizializzazione istantanea dei file, pertanto i tempi di crescita prolungati dei log sono particolarmente importanti.

• A partire da SQL Server 2022 (16.x, tutte le edizioni), l'inizializzazione istantanea dei file può favorire gli eventi di crescita dei log delle transazioni fino a 64 MB. L'incremento delle dimensioni di crescita automatica predefinito per i nuovi database è di 64 MB. Gli eventi di crescita automatica dei file di registro delle transazioni superiori a 64 MB non possono trarre vantaggio dall'inizializzazione istantanea dei file.