Inizializzazione immediata dei file di database

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

Questo articolo contiene informazioni sull'inizializzazione immediata dei file (IFI) e su come abilitarla per velocizzare la crescita dei file del database di SQL Server.

Per impostazione predefinita, i file di dati e di log vengono inizializzati per sovrascrivere eventuali dati esistenti rimasti nel disco in seguito all'eliminazione precedente di file. I file di dati e di log vengono prima di tutto inizializzati azzerando i file (riempiendoli con zeri) quando si eseguono queste operazioni:

• Crea un database .
• Aggiungere dati o file di log a un database esistente.
• Aumento delle dimensioni di un file esistente (incluse operazioni di aumento automatico delle dimensioni).
• Ripristino di un database o un filegroup.

In SQL Server l'inizializzazione immediata dei file consente un'esecuzione più rapida delle operazioni sui file indicate in precedenza, poiché recupera lo spazio su disco usato senza riempire lo spazio con zeri. Il contenuto del disco viene invece sovrascritto via via che nuovi dati vengono scritti nei file.

In database SQL di Azure e Istanza gestita di SQL di Azure, l'inizializzazione immediata dei file è disponibile solo per i file registro transazioni.

Inizializzazione immediata dei file e log delle transazioni

Si applica a: SQL Server 2022 (16.x) e versioni successive, Database SQL di Azure e Istanza gestita di SQL di Azure.

Inizialmente, i file registro transazioni non potevano essere inizializzati in modo istantaneo. Tuttavia, a partire da SQL Server 2022 (16.x) (tutte le edizioni) e in database SQL di Azure e Istanza gestita di SQL di Azure, gli eventi di aumento automatico del log delle transazioni fino a 64 MB possono trarre vantaggio dall'inizializzazione immediata dei file. L'incremento predefinito delle dimensioni di crescita automatica per i nuovi database è di 64 MB. Gli eventi di aumento automatico dei file registro transazioni di dimensioni superiori a 64 MB non possono trarre vantaggio dall'inizializzazione immediata dei file.

L'inizializzazione immediata dei file è consentita per l'aumento del log delle transazioni nei database con transparent data encryption (TDE) abilitata, a causa della natura della modalità di espansione del file registro transazioni e del fatto che il log delle transazioni viene scritto in modo seriale.

• L'inizializzazione immediata dei file è in uso per i livelli di Utilizzo generico e Business Critical di database SQL di Azure e Istanza gestita di SQL di Azure solo per trarre vantaggio dalla crescita dei file registro transazioni.
• L'inizializzazione immediata dei file non può essere configurata in database SQL di Azure e Istanza gestita di SQL di Azure.

Abilitare l'inizializzazione immediata dei file

L'inizializzazione immediata dei file di dati è disponibile solo se all'account di avvio del servizio SQL Server viene concesso SE_MANAGE_VOLUME_NAME. I membri del gruppo Administrator di Windows dispongono di questo diritto e possono concederlo ad altri utenti aggiungendoli ai criteri di sicurezza Esecuzione attività di manutenzione volume . Il diritto SE_MANAGE_VOLUME_NAME non è necessario per l'inizializzazione immediata dei file degli eventi di crescita fino a 64 MB nel log delle transazioni, introdotto con il rilascio di SQL Server 2022 (16.x).

In SQL Server 2016 (13.x) e versioni successive, è possibile concedere questa autorizzazione all'account del servizio al momento dell'installazione, durante la configurazione.

Se si utilizza il prompt dei comandi installare, aggiungere l'argomento /SQLSVCINSTANTFILEINIT o selezionare la casella Concedi il privilegio Esecuzione attività di manutenzione volume al servizio del motore di database di SQL Server nel Monitoraggio guidato installazione.

Per concedere l'autorizzazione Perform volume maintenance tasks a un account:

1. Nel computer in cui verrà creato il file di dati aprire l'applicazione Criteri di sicurezza locali (secpol.msc).

2. Nel riquadro a sinistra espandere Criteri locali, quindi selezionare Assegnazione diritti utente.

3. Nel riquadro destro fare doppio clic su Esecuzione attività di manutenzione volume.

4. Selezionare Aggiungi utente o gruppo e aggiungere l'account del servizio SQL Server.

5. Selezionare Applica, quindi chiudere tutte le finestre di dialogo di Criteri di sicurezza locali .

6. Riavviare il servizio SQL Server.

7. Controllare il log degli errori di SQL Server all'avvio.

   Si applica a: SQL Server, a partire da SQL Server 2012 (11.x) SP4, SQL Server 2014 (12.x) SP2 e SQL Server 2016 (13.x) e successivi.

   1. Se all'account di avvio del servizio SQL Server viene concesso SE_MANAGE_VOLUME_NAME, viene registrato un messaggio informativo simile al seguente esempio:

      Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.

   2. Se all'account di avvio del servizio SQL Server non viene concesso SE_MANAGE_VOLUME_NAME, viene registrato un messaggio informativo simile al seguente esempio:

      Database Instant File Initialization: disabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.

   Nota

   In SQL Server usare il valore di instant_file_initialization_enabled nella DMV sys.dm_server_services per identificare se l'inizializzazione immediata dei file è abilitata per l'istanza.

Considerazioni sulla sicurezza

È consigliabile abilitare l'inizializzazione immediata dei file perché i vantaggi possono superare i rischi per la sicurezza.

Quando si usa l'inizializzazione immediata dei file, il contenuto del disco eliminato viene sovrascritto solo quando vengono scritti nuovi dati nei file. Per questo motivo, il contenuto eliminato potrebbe essere accessibile a utenti o servizi non autorizzati, finché non vengono eseguite altre scritture dati in tale area specifica del file di dati.

Finché il file di database è collegato all'istanza di SQL Server, questo rischio di diffusione di informazioni è ridotto dall'elenco di controllo di accesso discrezionale (DACL) per il file. Questo elenco consente l'accesso al file solo all'account del servizio SQL Server e all'amministratore locale. Tuttavia, quando il file viene scollegato, è potenzialmente accessibile a un utente o a un servizio privo del diritto SE_MANAGE_VOLUME_NAME.

Considerazioni di questo tipo sono valide quando:

• Viene eseguito il backup del database. Se il file di backup non è protetto con un elenco di controllo di accesso discrezionale (DACL) appropriato, il contenuto eliminato può diventare disponibile a un utente o a un servizio non autorizzato.

• Un file viene incrementato usando l'inizializzazione immediata dei file. Un amministratore SQL Server può potenzialmente accedere ai contenuti delle pagine non elaborati e visualizzare i contenuti precedentemente eliminati.

• I file di database sono ospitati in una rete di archiviazione. È anche possibile che la rete di archiviazione visualizzi sempre le pagine nuove come pagine preinizializzate e non sia necessario che il sistema operativo ne esegua nuovamente l'inizializzazione.

Se la potenziale diffusione del contenuto eliminato rappresenta un problema, è consigliabile procedere con una o con entrambe le azioni seguenti:

• Assicurarsi sempre che i file di dati e i file di backup scollegati presentino elenchi di controllo di accesso discrezionale (DACL) restrittivi.

• Disabilitare l'inizializzazione immediata dei file per l'istanza di SQL Server. A tale scopo, revocare SE_MANAGE_VOLUME_NAME dall'account di avvio del servizio SQL Server.

  Nota

  La disabilitazione aumenterà i tempi di allocazione per i file di dati e ha effetto solo sui file che sono stati creati o le cui dimensioni sono aumentate dopo la revoca del diritto utente.

Diritto utente SE_MANAGE_VOLUME_NAME

Il privilegio utente SE_MANAGE_VOLUME_NAME può essere assegnato in Strumenti di amministrazione Windows e applet Criteri di sicurezza locali. In Criteri locali selezionare Assegnazione diritti utente e modificare la proprietà Esecuzione di attività di manutenzione volume.

Considerazioni sulle prestazioni

Il processo di inizializzazione dei file di database scrive zeri nelle nuove aree del file in fase di inizializzazione. La durata di questo processo dipende dalle dimensioni della parte del file inizializzata e dal tempo di risposta e dalla capacità del sistema di archiviazione. Se l'inizializzazione richiede molto tempo, è possibile che vengano visualizzati i seguenti messaggi registrati nel log degli errori di SQL Server e nel registro applicazioni.

Msg 5144
Autogrow of file '%.*ls' in database '%.*ls' was cancelled by user or timed out after %d milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

Msg 5145
Autogrow of file '%.*ls' in database '%.*ls' took %d milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

Un aumento automatico esteso di un file di log di database e/o di un file di log delle transazioni può causare problemi di prestazioni delle query. Un'operazione che richiede l'aumento automatico di un file si mantiene in risorse quali blocchi o latch per la durata dell'operazione di aumento delle dimensioni dei file. Potrebbero verificarsi lunghe attese nei latch per le pagine di allocazione. L'operazione che richiede l'aumento automatico esteso mostra un tipo di attesa PREEMPTIVE_OS_WRITEFILEGATHER.

Contenuto correlato

• CREATE DATABASE
• Gestione delle dimensioni del file di log delle transazioni
• Inizializzazione immediata dei file di SQL Server: SetFileValidData (Windows) e fallocate (Linux)