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 di 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'inizializzazione immediata dei file è disponibile solo per i file di log delle transazioni.

Inizializzazione immediata dei file e log delle transazioni

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

Storicamente, i file di log delle transazioni non potevano essere inizializzati istantaneamente. 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 è 64 MB. Gli eventi di aumento automatico dei file di log delle 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 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 di database SQL di Azure e Istanza gestita di SQL di Azure solo per trarre vantaggio dalla crescita dei file di log delle transazioni.
• L'inizializzazione immediata dei file non è configurabile 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 SE_MANAGE_VOLUME_NAME diritto 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).

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 sinistro espandere Criteri locali e 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 che esegue il servizio SQL Server.

5. Selezionare Applica e quindi chiudere tutte le finestre di dialogo 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 versioni successive.

   1. Se all'account di avvio del servizio SQL Server viene concesso SE_MANAGE_VOLUME_NAME, 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 di avvio del servizio SQL Server non è stato concesso SE_MANAGE_VOLUME_NAME, 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 sys.dm_server_services visualizzazione a gestione dinamica 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, è possibile accedervi da un utente o da un servizio che non dispone di 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 DACL appropriato, il contenuto eliminato può diventare disponibile per un utente o 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 dell'area di archiviazione presenti sempre nuove pagine come pre-inizializzate e che il sistema operativo reinizializzi le pagine potrebbe non essere necessario.

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 SE_MANAGE_VOLUME_NAME privilegio utente può essere assegnato in Strumenti di windows Amministrazione istrative, applet dei 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 messaggi seguenti registrati nel log degli errori di SQL Server e nel log 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 lungo di un database e/o di un file di log delle transazioni può causare problemi di prestazioni delle query. Ciò è dovuto al fatto che un'operazione che richiede l'aumento automatico di un file in risorse, ad esempio blocchi o latch durante la durata dell'operazione di aumento del file. Potrebbero verificarsi attese lunghe sui latch per le pagine di allocazione. L'operazione che richiede l'aumento automatico lungo mostra un tipo di attesa di PREEMPTIVE_OS_WRITEFILEGATHER.

Contenuto correlato

• CREATE DATABASE
• Gestione delle dimensioni del file di log delle transazioni