Gestire lo spazio dei file per i database in Istanza gestita di SQL di Azure

Si applica a: Istanza gestita di SQL di Azure SQL

Questo articolo illustra come monitorare e gestire i file nei database in Istanza gestita di SQL di Azure. Viene esaminato come monitorare le dimensioni del file di database, compattare il log delle transazioni, ingrandire un file di registro delle transazioni e controllare l'aumento di un file di registro delle transazioni.

Questo articolo si applica a Istanza gestita di SQL di Azure. Anche se molto simile, per informazioni sulla gestione delle dimensioni dei file di log delle transazioni in SQL Server, vedere Gestire le dimensioni del file registro transazioni.

Informazioni sui tipi di spazio di archiviazione per un database

La comprensione delle quantità di spazio di archiviazione seguenti è importante per la gestione dello spazio file di un database.

Il diagramma seguente illustra la relazione tra i diversi tipi di spazio di archiviazione per un database.

Eseguire una query su un database singolo per ottenere informazioni sullo spazio dei file

Usare la query seguente su sys.database_files per restituire la quantità di spazio per i dati del database allocato e la quantità di spazio inutilizzato allocato. L'unità di misura dei risultati di query è costituita da MB.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Monitorare l'uso dello spazio del log

Monitorare l'uso dello spazio del log tramite sys.dm_db_log_space_usage. Questo DMV restituisce informazioni sulla quantità di spazio del log attualmente usata e indica quando il log delle transazioni deve essere troncato.

Per informazioni sulle dimensioni correnti di un file di log, sulle relative dimensioni massime e sull'opzione di aumento automatico delle dimensioni per il file, è anche possibile usare le colonne size, max_size e growth per il file di resoconto in sys.database_files.

Metriche dello spazio di archiviazione visualizzate nelle API delle metriche basate su Azure Resource Manager misurano solo le dimensioni delle pagine di dati usate. Per esempi, vedere Get-metrics di PowerShell.

Compattare il file di log

Per ridurre la dimensione fisica di un file di log fisico rimuovendo lo spazio non utilizzato, è necessario compattare il file di resoconto. Una compattazione fa la differenza solo quando un file di log delle transazioni contiene spazio inutilizzato. Se il file di log è pieno, probabilmente a causa di transazioni aperte, esaminare cosa impedisce il troncamento del log delle transazioni.

Tenere presente il potenziale impatto negativo sulle prestazioni della compattazione dei file di database, vedere Manutenzione dell'indice dopo la compattazione. In rari casi, le operazioni di compattazione possono essere influenzate dai backup automatici del database. Se necessario, ripetere l'operazione di compattazione.

Prima di ridurre le dimensioni del log delle transazioni, tenere presenti i fattori che possono posticipare il troncamento del log. Se dopo una compattazione del log è di nuovo necessario lo spazio di archiviazione, il log delle transazioni torna a crescere e durante tale crescita origina un overhead delle prestazioni. Per ulteriori informazioni, consultare Suggerimenti.

È possibile compattare un file di log solo mentre il database è online ed è disponibile almeno un file di log virtuale (VLF). In alcuni casi, la compattazione del log potrebbe non essere possibile finché il log non viene troncato.

Fattori come una transazione con esecuzione prolungata, che mantiene i VLF attivi per un lungo periodo di tempo, possono limitare in tutto o in parte la compattazione del log. Per informazioni, vedere Fattori che possono posticipare il troncamento del log.

Il processo di compattazione di un file di log comporta la rimozione di uno o più VLF che non contengono alcuna parte del log logico, ovvero dei VLF inattivi. Quando si compatta un file di registro delle transazioni, dalla fine di un file di resoconto vengono rimossi alcuni file VLF inattivi, per ridurre il log approssimativamente alle dimensioni della destinazione.

Per altre informazioni sulle operazioni di compattazione, vedere quanto segue:

Compattare un file di log senza compattare i file di database

• DBCC SHRINKFILE (Transact-SQL)

• Compattare un file

Monitorare gli eventi di compattazione dei file di log

• Log File Auto Shrink Event Class.

Monitorare lo spazio del log

• sys.dm_db_log_space_usage (Transact-SQL)

• sys.database_files (Transact-SQL) (vedere le colonne size, max_size e growth per i file o i file di log).

Manutenzione dell'indice dopo la compattazione

Al termine di un'operazione di compattazione rispetto ai file di dati, gli indici possono essere frammentati. In questo modo si riduce l'efficacia dell'ottimizzazione delle prestazioni per determinati carichi di lavoro, ad esempio le query che usano analisi di grandi dimensioni. Se si verifica una riduzione del livello delle prestazioni dopo il completamento dell'operazione di compattazione, prendere in considerazione la possibilità di eseguire la manutenzione degli indici ricostruendoli. Tenere presente che le ricompilazioni dell'indice richiedono spazio disponibile nel database e pertanto lo spazio allocato può aumentare, contrastando l'effetto della compattazione.

Per altre informazioni sulla manutenzione degli indici, vedere Ottimizzare la manutenzione degli indici per migliorare le prestazioni delle query e ridurre il consumo di risorse.

Valutare la densità della pagina dell'indice

Se il troncamento dei file di dati non ha comportato una riduzione sufficiente dello spazio allocato, è possibile compattare i file di dati del database per recuperare lo spazio inutilizzato da tali file. Tuttavia, come passaggio facoltativo ma consigliato, è necessario prima determinare la densità media delle pagine per gli indici all'interno del database. Per la stessa quantità di dati, la compattazione verrà completata più velocemente se la densità di pagina è elevata, perché dovrà spostare meno pagine. Se la densità di pagina è bassa per alcuni indici, si consiglia di eseguire operazioni di manutenzione su questi indici per aumentare la densità di pagina prima di compattare i file di dati. In questo modo si otterrà anche una compattazione più profonda dello spazio di archiviazione allocato.

Per determinare la densità di pagina per tutti gli indici nel database, usare la query seguente. La densità di pagina viene segnalata nella colonna avg_page_space_used_in_percent.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

Se sono presenti indici con un numero elevato di pagine con densità di pagina inferiore al 60-70%, valutare la possibilità di ricompilare o riorganizzare questi indici prima di compattare i file di dati.

Se sono presenti più indici con densità di pagina bassa, è possibile ricompilarli in parallelo in più sessioni di database per velocizzare il processo. Tuttavia, assicurarsi di non avvicinarsi ai limiti delle risorse del database in questo modo e di lasciare sufficiente capacità aggiuntiva delle risorse per i carichi di lavoro delle applicazioni. Monitorare l'utilizzo delle risorse (CPU, I/O dei dati, I/O dei log) nel portale di Azure o usando la vista sys.dm_db_resource_stats e avviare ricompilazione parallele aggiuntive solo se l'utilizzo delle risorse in ognuna di queste dimensioni rimane sostanzialmente inferiore al 100%. Se l'utilizzo di CPU, l'I/O dei dati o l'I/O dei log è pari al 100%, è possibile aumentare le prestazioni del database per avere più core CPU e aumentare la velocità effettiva di I/O, consetendo ricostruzioni parallele aggiuntive per completare più velocemente il processo.

Comando di ricompilazione dell'indice di esempio

Di seguito è riportato un comando di esempio per ricompilare un indice e aumentarne la densità di pagina usando l'istruzione ALTER INDEX:

ALTER INDEX [index_name] ON [schema_name].[table_name]
REBUILD WITH (FILLFACTOR = 100, MAXDOP = 8, 
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), 
RESUMABLE = ON);

Questo comando avvia una ricompilazione dell'indice online e ripristinabile. Ciò consente ai carichi di lavoro simultanei di continuare a usare la tabella mentre la ricompilazione è in corso e consente di riprendere la ricompilazione se viene interrotta per qualsiasi motivo. Tuttavia, questo tipo di ricompilazione è più lento rispetto a una ricompilazione offline, che blocca l'accesso alla tabella. Se nessun altro carico di lavoro deve accedere alla tabella durante la ricompilazione, impostare le opzioni ONLINE e RESUMABLE su OFF e rimuovere la clausola WAIT_AT_LOW_PRIORITY.

Per maggiori informazioni sulla manutenzione degli indici, vedere Ottimizzare la manutenzione degli indici per migliorare le prestazioni delle query e ridurre il consumo di risorse.

Compattare più file di dati

Come indicato in precedenza, la compattazione con lo spostamento dei dati è un processo a esecuzione prolungata. Se nel database sono presenti più file di dati, è possibile velocizzare il processo compattando più file di dati in parallelo. Per farlo, aprire più sessioni di database e usare DBCC SHRINKFILE in ogni sessione con un valore di file_id diverso. Analogamente alla ricompilazione degli indici in precedenza, assicurarsi di disporre di sufficienti capacità aggiuntive per le risorse (CPU, I/O dati, I/O log) prima di avviare ogni nuovo comando di compattazione parallela.

Il comando di esempio seguente riduce il file di dati con file_id 4, spostando le pagine all'interno del file nel tentativo di ridurre le dimensioni allocate a 52.000 MB :

DBCC SHRINKFILE (4, 52000);

Se si vuole ridurre lo spazio allocato per il file al minimo possibile, eseguire l'istruzione senza specificare le dimensioni di destinazione:

DBCC SHRINKFILE (4);

Se un carico di lavoro è in esecuzione simultaneamente alla compattazione, può iniziare a usare lo spazio di archiviazione liberato dalla compattazione prima del completamento della stessa e troncare il file. In questo caso, la compattazione non sarà in grado di ridurre lo spazio allocato alla destinazione specificata.

È possibile attenuare questo problema compattando ogni file in passaggi più piccoli. Ciò significa che nel comando DBCC SHRINKFILE si imposta la destinazione leggermente inferiore rispetto allo spazio allocato corrente per il file. Ad esempio, se lo spazio allocato per il file con file_id 4 è 200.000 MB e si vuole ridurlo a 100.000 MB, è possibile impostare prima la destinazione su 170.000 MB:

DBCC SHRINKFILE (4, 170000);

Una volta che il comando è stato eseguito, il file verrà troncato e le dimensioni allocate saranno ridotte a 170.000 MB. È quindi possibile ripetere questo comando, impostando la destinazione prima su 140.000 MB, quindi su 110.000 MB e così via, fino a quando il file non viene ridotto alle dimensioni desiderate. Se il comando viene completato ma il file non viene troncato, usare passaggi più piccoli, ad esempio 15.000 MB anziché 30.000 MB.

Per monitorare lo stato di compattazione per tutte le sessioni di compattazione in esecuzione simultanea, è possibile usare la query seguente:

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

Una volta completata la compattazione per tutti i file di dati, usare la query sull'utilizzo dello spazio per determinare la riduzione risultante delle dimensioni di archiviazione allocate. Se esiste ancora una grande differenza tra lo spazio usato e lo spazio allocato, è possibile ricompilare gli indici. Questo può aumentare temporaneamente lo spazio allocato, ma la compattazione dei file di dati dopo la ricompilazione degli indici dovrebbe comportare una riduzione ulteriore dello spazio allocato.

Ingrandire un file di log

In Istanza gestita di SQL di Azure, aggiungere spazio a un file di resoconto ampliando il file di log esistente (se lo spazio su disco è consentito). L'aggiunta di un file di resoconto al database non è supportata. Un file di log delle transazioni è sufficiente, a meno che lo spazio del log sia in esaurimento e anche lo spazio su disco sia in esaurimento nel volume che contiene il file di log.

Per espandere il file di log usare la clausola MODIFY FILE dell'istruzione ALTER DATABASE specificando la sintassi SIZE e MAXSIZE. Per altre informazioni, vedere Opzioni per file e filegroup ALTER DATABASE (Transact-SQL).

Per ulteriori informazioni, consultare i Consigli.

Controllare l'aumento delle dimensioni di un file di log delle transazioni

Per gestire la crescita di un file di log delle transazioni, usare l'istruzione descritta in Opzioni per file e filegroup ALTER DATABASE (Transact-SQL). Notare quanto segue:

• Per modificare le dimensioni del file corrente in unità KB, MB, GB e TB usare l'opzione SIZE.
• Per modificare l'incremento di crescita, usare l'opzione FILEGROWTH. Il valore 0 indica che l'aumento automatico delle dimensioni è disattivato e non è consentita l'allocazione di spazio aggiuntivo.
• Per controllare le dimensioni massime di un file di log in unità KB, MB, GB e TB o per impostare la crescita su UNLIMITED, usare l'opzione MAXSIZE.

Consigli

Di seguito sono elencate alcune indicazioni di carattere generale relative all'uso dei file registro transazioni:

• L'incremento automatico (autogrow) delle dimensioni del log delle transazioni, definito dall'opzione FILEGROWTH, deve essere sufficiente a soddisfare le esigenze delle transazioni del carico di lavoro. È consigliabile specificare un incremento di crescita per un file di log sufficientemente grande da consentire di evitare l'espansione frequente. Un buon indicatore per il dimensionamento corretto di un log delle transazioni è la quantità di spazio del log occupato durante:

  • Il tempo necessario per l'esecuzione di un backup completo, perché i backup del log non possono verificarsi finché non termina il backup completo.
  • Il tempo necessario per le operazioni di manutenzione dell'indice più grande.
  • Il tempo necessario per eseguire il batch più grande in un database.

• Quando si imposta autogrow per i file di dati e di log usando l'opzione FILEGROWTH può essere preferibile impostare size anziché percentage, per consentire un controllo migliore del rapporto di crescita, dato che percentage corrisponde a un valore in continua crescita.

  • In Istanza gestita di SQL di Azure l'inizializzazione immediata dei file può trarre vantaggio dagli eventi di crescita del log delle transazioni fino a 64 MB. L'incremento predefinito delle dimensioni di crescita automatica per i nuovi database è 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.
  • Come procedura consigliata, evitare di impostare un valore dell'opzione FILEGROWTH superiore a 1024 MB per i log delle transazioni.

• Un incremento della crescita ridotto può generare molti file VLF piccoli e ridurre le prestazioni. Per determinare la distribuzione dei file di log virtuali ottimale per le dimensioni correnti del log delle transazioni di tutti i database in un'istanza specifica e gli incrementi della crescita necessari per ottenere le dimensioni richieste, vedere questo script per l'analisi e la correzione di VLF forniti dal team di SQL Tiger.

• Un aumento automatico di grandi dimensioni può causare due problemi:

  • Un aumento automatico di grandi dimensioni può causare la sospensione del database mentre viene allocato il nuovo spazio, causando potenzialmente timeout delle query.
  • Un incremento della crescita elevato può generare pochi file VLF di grandi dimensioni e ridurre a sua volta le prestazioni. Per determinare la distribuzione dei file di log virtuali ottimale per le dimensioni correnti del log delle transazioni di tutti i database in un'istanza specifica e gli incrementi della crescita necessari per ottenere le dimensioni richieste, vedere questo script per l'analisi e la correzione di VLF forniti dal team di SQL Tiger.

• Anche con l'aumento automatico attivato è possibile che si riceva un messaggio indicante che il log delle transazioni è pieno, se questo non può crescere a sufficienza per soddisfare le esigenze della query. Per altre informazioni su come modificare l'incremento della crescita, vedere Opzioni per file e filegroup ALTER DATABASE (Transact-SQL).

• È possibile impostare i file di log in modo che vengano compattati automaticamente. Tuttavia questa impostazione è sconsigliata e la proprietà del database auto_shrink è FALSE per impostazione predefinita. Se auto_shrink è impostata su TRUE, la compattazione automatica riduce le dimensioni di un file solo quando più del 25% dello spazio del file risulta inutilizzato.

  • Il file viene compattato fino a quando la percentuale di spazio inutilizzato nel file non risulta pari al 25 percento oppure fino a quando il file non raggiunge le dimensioni originali, a seconda di quale tra questi due sia il valore maggiore.
  • Per informazioni sulla modifica dell'impostazione della proprietà auto_shrink, vedere Visualizzare o modificare le proprietà di un database e Opzioni ALTER DATABASE SET (Transact-SQL).

Contenuto correlato

• Backup automatizzati in Istanza gestita di SQL di Azure
• Opzioni per file e filegroup ALTER DATABASE (Transact-SQL)
• Panoramica dei limiti delle risorse di Istanza gestita di SQL di Azure
• Risolvere gli errori del log delle transazioni con Istanza gestita di SQL di Azure