Inizializzazione immediata dei file di database

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

In questo articolo vengono fornite informazioni sull'inizializzazione immediata dei file (IFI) e su come abilitarla per velocizzare la crescita dei file di 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 precedente viene invece sovrascritto quando vengono scritti nuovi dati 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.

A differenza dell'inizializzazione immediata dei file per i file di dati, che viene impedito se transparent data encryption (TDE) è abilitata, l'inizializzazione immediata dei file è consentita per l'aumento del log delle transazioni nei database con TDE abilitato, a causa della crescita del file di log delle transazioni e del fatto che il log delle transazioni viene scritto in modo seriale.

• L'inizializzazione immediata dei file è in uso per i livelli Utilizzo generico e Business Critical del database SQL di Azure e per Istanza gestita di SQL di Azure, solo per sfruttare la crescita dei file di log delle 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 del servizio o al SID del servizio del motore di database viene concesso il SE_MANAGE_VOLUME_NAME privilegio . I membri del gruppo Amministratori di Windows hanno questo privilegio e possono concederlo ad altri utenti aggiungendoli al criterio di sicurezza "Esegui attività di manutenzione del volume". Il SE_MANAGE_VOLUME_NAME privilegio 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).

È consigliabile concedere il SE_MANAGE_VOLUME_NAME privilegio al SID del servizio del servizio Motore di database. Ciò garantisce che la concessione rimanga anche se si modifica l'account del servizio di motore di database. Per altre informazioni, vedere Uso dei SID del servizio per concedere autorizzazioni ai servizi in SQL Server.

In SQL Server 2016 (13.x) e versioni successive, questa autorizzazione può essere concessa al SID (Database Engine Service Security Identifier) in fase di installazione durante l'installazione.

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 un account o un SID di servizio, i Perform volume maintenance tasks criteri di sicurezza:

1. Nel computer in cui verranno creati i 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 Motore di database o il relativo SID del servizio.

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

6. Riavviare il servizio motore di database.

7. Controllare il log degli errori del motore di database 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 del servizio motore di database o al relativo SID del servizio viene concesso il SE_MANAGE_VOLUME_NAME privilegio , viene registrato un messaggio informativo simile all'esempio seguente:

      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 del servizio Motore di Database o al SID del relativo servizio non è stato concesso il SE_MANAGE_VOLUME_NAME privilegio, viene registrato un messaggio informativo simile all'esempio seguente.

      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 relative alla 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. Il DACL consente l'accesso ai file solo all'account del servizio SQL Server, al relativo SID del servizio e all'amministratore locale. Tuttavia, quando il file viene scollegato, è potenzialmente accessibile da un utente o da un servizio che non ha il SE_MANAGE_VOLUME_NAME privilegio.

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 servizio del motore di database e dal relativo SID del servizio.

  Nota

  La disabilitazione di IFI aumenta il tempo di crescita per i file di dati e influisce solo sui file creati o aumentati di dimensioni dopo la revoca del privilegio.

SE_MANAGE_VOLUME_NAME privilegio

Il SE_MANAGE_VOLUME_NAME privilegio può essere assegnato nell'applet Criteri di sicurezza locali degli Strumenti di amministrazione di Windows. 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)