Gestire lo spazio file per database singoli e in pool nel database SQL di AzureManage file space for single and pooled databases in Azure SQL Database

Questo articolo descrive i diversi tipi di spazio di archiviazione per database singoli e in pool nel database SQL di Azure e le operazioni che è possibile eseguire quando lo spazio file allocato per i database e i pool elastici deve essere gestito esplicitamente.This article describes different types of storage space for single and pooled databases in Azure SQL Database, and steps that can be taken when the file space allocated for databases and elastic pools needs to be explicitly managed.

Nota

Le informazioni di questo articolo non sono valide per l'opzione di distribuzione dell'istanza gestita nel database SQL di Azure.This article does not apply to the managed instance deployment option in Azure SQL Database.

PanoramicaOverview

Nota

Questo articolo è stato aggiornato per usare il nuovo modulo Az di Azure PowerShell.This article has been updated to use the new Azure PowerShell Az module. È comunque possibile usare il modulo AzureRM, che continuerà a ricevere correzioni di bug almeno fino a dicembre 2020.You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. Per altre informazioni sul nuovo modulo Az e sulla compatibilità di AzureRM, vedere Introduzione del nuovo modulo Az di Azure PowerShell.To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. Per istruzioni sull'installazione del modulo Az, vedere Installare Azure PowerShell.For Az module installation instructions, see Install Azure PowerShell.

Importante

Il modulo Azure Resource Manager di PowerShell è ancora supportato dal database SQL di Azure, ma tutte le attività di sviluppo future sono per il modulo AZ. SQL.The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. Per questi cmdlet, vedere AzureRM. SQL.For these cmdlets, see AzureRM.Sql. Gli argomenti per i comandi nel modulo AZ e nei moduli AzureRm sono sostanzialmente identici.The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

Con i database singoli e in pool nel database SQL di Azure sono disponibili modelli di carico di lavoro in cui l'allocazione dei file di dati sottostanti per i database può superare la quantità di pagine di dati usate.With single and pooled databases in Azure SQL Database, there are workload patterns where the allocation of underlying data files for databases can become larger than the amount of used data pages. Questa condizione si può verificare quando lo spazio usato aumenta e i dati vengono successivamente eliminati.This condition can occur when space used increases and data is subsequently deleted. Ciò è dovuto al fatto che lo spazio file allocato non viene recuperato automaticamente quando i dati vengono eliminati.The reason is because file space allocated is not automatically reclaimed when data is deleted.

Può essere necessario monitorare l'utilizzo dello spazio file e compattare i file di dati per:Monitoring file space usage and shrinking data files may be necessary in the following scenarios:

  • Consentire l'aumento delle dimensioni dei dati in un pool elastico quando lo spazio file allocato per i relativi database raggiunge le dimensioni massime del pool.Allow data growth in an elastic pool when the file space allocated for its databases reaches the pool max size.
  • Consentire la riduzione delle dimensioni massime di un database singolo o di un pool elastico.Allow decreasing the max size of a single database or elastic pool.
  • Consentire il passaggio di un database singolo o di un pool elastico a un livello di servizio o a un livello di prestazioni diverso con dimensioni massime inferiori.Allow changing a single database or elastic pool to a different service tier or performance tier with a lower max size.

Monitoraggio dell'utilizzo dello spazio fileMonitoring file space usage

La maggior parte delle metriche per lo spazio di archiviazione visualizzate nel portale di Azure e delle API seguenti misura solo le dimensioni delle pagine di dati usate:Most storage space metrics displayed in the Azure portal and the following APIs only measure the size of used data pages:

Le API seguenti misurano invece anche le dimensioni dello spazio allocato per i database e i pool elastici:However, the following APIs also measure the size of space allocated for databases and elastic pools:

Compattazione dei file di datiShrinking data files

Il servizio database SQL non compatta automaticamente i file di dati per recuperare spazio allocato non usato a causa del potenziale impatto sulle prestazioni dei database.The SQL Database service does not automatically shrink data files to reclaim unused allocated space due to the potential impact to database performance. I clienti possono tuttavia compattare i file di dati in modalità self-service quando preferiscono seguendo la procedura illustrata in Recuperare lo spazio allocato non usato.However, customers may shrink data files via self-service at a time of their choosing by following the steps described in reclaim unused allocated space.

Nota

A differenza dei file di dati, il servizio di database SQL compatta automaticamente i file di log, in quanto tale operazione non influisce sulle prestazioni del database.Unlike data files, the SQL Database service automatically shrinks log files since that operation does not impact database performance.

Informazioni sui tipi di spazio di archiviazione per un databaseUnderstanding types of storage space for a database

La comprensione delle quantità di spazio di archiviazione seguenti è importante per la gestione dello spazio file di un database.Understanding the following storage space quantities are important for managing the file space of a database.

Quantità di databaseDatabase quantity DefinizioneDefinition CommentiComments
Spazio dati usatoData space used Quantità di spazio usato per archiviare i dati del database in pagine da 8 KB.The amount of space used to store database data in 8 KB pages. In genere, lo spazio usato aumenta quando vengono inseriti i dati e diminuisce quando vengono eliminati.Generally, space used increases (decreases) on inserts (deletes). In alcuni casi, lo spazio usato non cambia in caso di inserimenti o eliminazioni, a seconda della quantità e del modello di dati coinvolti nell'operazione e dell'eventuale frammentazione.In some cases, the space used does not change on inserts or deletes depending on the amount and pattern of data involved in the operation and any fragmentation. Ad esempio, se si elimina una riga da ogni pagina di dati, non si riduce necessariamente lo spazio usato.For example, deleting one row from every data page does not necessarily decrease the space used.
Spazio dati allocatoData space allocated Quantità di spazio file formattato messo a disposizione per l'archiviazione dei dati del database.The amount of formatted file space made available for storing database data. La quantità di spazio allocato aumenta automaticamente, ma non diminuisce mai dopo le eliminazioni.The amount of space allocated grows automatically, but never decreases after deletes. Questo comportamento assicura che gli inserimenti futuri avvengano più velocemente, perché non è necessario riformattare lo spazio.This behavior ensures that future inserts are faster since space does not need to be reformatted.
Spazio dati allocato ma non usatoData space allocated but unused Differenza tra la quantità di spazio dati allocato e lo spazio dati usato.The difference between the amount of data space allocated and data space used. Questa quantità rappresenta la quantità massima di spazio libero che può essere recuperata compattando i file di dati del database.This quantity represents the maximum amount of free space that can be reclaimed by shrinking database data files.
Dimensioni massime dei datiData max size Quantità massima di spazio che può essere usata per l'archiviazione dei dati del database.The maximum amount of space that can be used for storing database data. La quantità di spazio dati allocato non può superare le dimensioni massime dei dati.The amount of data space allocated cannot grow beyond the data max size.

Il diagramma seguente illustra la relazione tra i diversi tipi di spazio di archiviazione per un database.The following diagram illustrates the relationship between the different types of storage space for a database.

Tipi di spazio di archiviazione e relazioni

Eseguire una query su un database singolo per ottenere informazioni sullo spazio di archiviazioneQuery a single database for storage space information

Per determinare le quantità di spazio di archiviazione per un database singolo, è possibile usare le query seguenti.The following queries can be used to determine storage space quantities for a single database.

Spazio dati del database usatoDatabase data space used

Modificare la query seguente per restituire la quantità di spazio dati del database usato.Modify the following query to return the amount of database data space used. L'unità di misura dei risultati di query è costituita da MB.Units of the query result are in MB.

-- Connect to master
-- Database data space used in MB
SELECT TOP 1 storage_in_megabytes AS DatabaseDataSpaceUsedInMB
FROM sys.resource_stats
WHERE database_name = 'db1'
ORDER BY end_time DESC

Spazio di dati del database allocato e spazio allocato non usatoDatabase data space allocated and unused allocated space

Usare la query seguente per restituire la quantità di spazio dati di database allocato e la quantità di spazio non usato allocato.Use the following query to return the amount of database data space allocated and the amount of unused space allocated. L'unità di misura dei risultati di query è costituita da MB.Units of the query result are in MB.

-- Connect to database
-- Database data space allocated in MB and database data space allocated unused in MB
SELECT SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB, 
SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB 
FROM sys.database_files
GROUP BY type_desc
HAVING type_desc = 'ROWS'

Dimensioni massime dei dati del databaseDatabase data max size

Modificare la query seguente per restituire le dimensioni massime dei dati del database.Modify the following query to return the database data max size. L'unità di misura dei risultati di query è costituita da byte.Units of the query result are in bytes.

-- Connect to database
-- Database data max size in bytes
SELECT DATABASEPROPERTYEX('db1', 'MaxSizeInBytes') AS DatabaseDataMaxSizeInBytes

Informazioni sui tipi di spazio di archiviazione per un pool elasticoUnderstanding types of storage space for an elastic pool

La comprensione delle quantità di spazio di archiviazione seguenti è importante per la gestione dello spazio file di un pool elastico.Understanding the following storage space quantities are important for managing the file space of an elastic pool.

Quantità di pool elasticoElastic pool quantity DefinizioneDefinition CommentiComments
Spazio dati usatoData space used Somma dello spazio dati usato da tutti i database nel pool elastico.The summation of data space used by all databases in the elastic pool.
Spazio dati allocatoData space allocated Somma dello spazio dati allocato da tutti i database nel pool elastico.The summation of data space allocated by all databases in the elastic pool.
Spazio dati allocato ma non usatoData space allocated but unused Differenza tra la quantità di spazio dati allocato e lo spazio dati usato da tutti i database nel pool elastico.The difference between the amount of data space allocated and data space used by all databases in the elastic pool. Questa quantità rappresenta la quantità massima di spazio allocato per il pool elastico che può essere recuperata compattando i file di dati del database.This quantity represents the maximum amount of space allocated for the elastic pool that can be reclaimed by shrinking database data files.
Dimensioni massime dei datiData max size Quantità massima di spazio dati che può essere usata dal pool elastico per tutti i rispettivi database.The maximum amount of data space that can be used by the elastic pool for all of its databases. Lo spazio allocato per il pool elastico non deve superare le dimensioni massime del pool elastico.The space allocated for the elastic pool should not exceed the elastic pool max size. Se si verifica questa condizione, lo spazio allocato e non usato può essere recuperato compattando i file di dati del database.If this condition occurs, then space allocated that is unused can be reclaimed by shrinking database data files.

Eseguire una query su un pool elastico per ottenere informazioni sullo spazio di archiviazioneQuery an elastic pool for storage space information

Le query seguenti possono essere usate per determinare le quantità di spazio di archiviazione per un pool elastico.The following queries can be used to determine storage space quantities for an elastic pool.

Spazio dati del pool elastico usatoElastic pool data space used

Modificare la query seguente per restituire la quantità di spazio dati del pool elastico usato.Modify the following query to return the amount of elastic pool data space used. L'unità di misura dei risultati di query è costituita da MB.Units of the query result are in MB.

-- Connect to master
-- Elastic pool data space used in MB  
SELECT TOP 1 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC

Spazio di dati del pool elastico allocato e spazio allocato non usatoElastic pool data space allocated and unused allocated space

Modificare lo script di PowerShell seguente per restituire una tabella che elenca lo spazio allocato e lo spazio allocato non usato per ogni database in un pool elastico.Modify the following PowerShell script to return a table listing the space allocated and unused allocated space for each database in an elastic pool. La tabella dispone i database in ordine decrescente in base alla quantità di spazio allocato non usato.The table orders databases from those databases with the greatest amount of unused allocated space to the least amount of unused allocated space. L'unità di misura dei risultati di query è costituita da MB.Units of the query result are in MB.

I risultati delle query per determinare lo spazio allocato per ogni database nel pool possono essere sommati per determinare lo spazio totale allocato per il pool elastico.The query results for determining the space allocated for each database in the pool can be added together to determine the total space allocated for the elastic pool. Lo spazio allocato del pool elastico non deve superare le dimensioni massime del pool elastico.The elastic pool space allocated should not exceed the elastic pool max size.

Lo script di PowerShell richiede il modulo SQL Server PowerShell. Vedere Scaricare il modulo PowerShell per l'installazione.The PowerShell script requires SQL Server PowerShell module – see Download PowerShell module to install.

# Resource group name
$resourceGroupName = "rg1" 
# Server name
$serverName = "ls2" 
# Elastic pool name
$poolName = "ep1"
# User name for server
$userName = "name"
# Password for server
$password = "password"

# Get list of databases in elastic pool
$databasesInPool = Get-AzSqlElasticPoolDatabase `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -ElasticPoolName $poolName
$databaseStorageMetrics = @()

# For each database in the elastic pool,
# get its space allocated in MB and space allocated unused in MB.
  
foreach ($database in $databasesInPool)
{
    $sqlCommand = "SELECT DB_NAME() as DatabaseName, `
    SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB, `
    SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB `
    FROM sys.database_files `
    GROUP BY type_desc `
    HAVING type_desc = 'ROWS'"
    $serverFqdn = "tcp:" + $serverName + ".database.windows.net,1433"
    $databaseStorageMetrics = $databaseStorageMetrics + 
        (Invoke-Sqlcmd -ServerInstance $serverFqdn `
        -Database $database.DatabaseName `
        -Username $userName `
        -Password $password `
        -Query $sqlCommand)
}
# Display databases in descending order of space allocated unused
Write-Output "`n" "ElasticPoolName: $poolName"
Write-Output $databaseStorageMetrics | Sort `
    -Property DatabaseDataSpaceAllocatedUnusedInMB `
    -Descending | Format-Table

Lo screenshot seguente mostra un esempio di output dello script:The following screenshot is an example of the output of the script:

Esempio di spazio allocato del pool elastico e spazio allocato non usato

Dimensioni massime dei dati del pool elasticoElastic pool data max size

Modificare la query T-SQL seguente per restituire le dimensioni massime dei dati del pool elastico.Modify the following T-SQL query to return the elastic pool data max size. L'unità di misura dei risultati di query è costituita da MB.Units of the query result are in MB.

-- Connect to master
-- Elastic pools max size in MB
SELECT TOP 1 elastic_pool_storage_limit_mb AS ElasticPoolMaxSizeInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC

Recuperare lo spazio allocato non usatoReclaim unused allocated space

Nota

Questo comando può influire sulle prestazioni del database mentre è in esecuzione e quindi, se possibile, dovrebbe essere eseguito in periodi di utilizzo ridotto.This command can impact database performance while it is running, and if possible should be run during periods of low usage.

Compattazione tramite DBCCDBCC shrink

Dopo l'identificazione dei database per il recupero di spazio allocato non usato, modificare il nome del database nel comando seguente per compattare i file di dati di ogni database.Once databases have been identified for reclaiming unused allocated space, modify the name of the database in the following command to shrink the data files for each database.

-- Shrink database data space allocated.
DBCC SHRINKDATABASE (N'db1')

Questo comando può influire sulle prestazioni del database mentre è in esecuzione e quindi, se possibile, dovrebbe essere eseguito in periodi di utilizzo ridotto.This command can impact database performance while it is running, and if possible should be run during periods of low usage.

Per altre informazioni su questo comando, vedere SHRINKDATABASE.For more information about this command, see SHRINKDATABASE.

Compattazione automaticaAuto-shrink

In alternativa, è possibile abilitare la compattazione automatica per un database.Alternatively, auto shrink can be enabled for a database. La compattazione automatica riduce la complessità della gestione dei file e il suo impatto sulle prestazioni del database è inferiore rispetto a SHRINKDATABASE o SHRINKFILE.Auto shrink reduces file management complexity and is less impactful to database performance than SHRINKDATABASE or SHRINKFILE. Può essere particolarmente utile per la gestione dei pool elastici con molti database.Auto shrink can be particularly helpful for managing elastic pools with many databases. Può essere però meno efficace nel recupero dello spazio file rispetto a SHRINKDATABASE e SHRINKFILE.However, auto shrink can be less effective in reclaiming file space than SHRINKDATABASE and SHRINKFILE. Per abilitare la compattazione automatica, modificare il nome del database nel comando seguente.To enable auto shrink, modify the name of the database in the following command.

-- Enable auto-shrink for the database.
ALTER DATABASE [db1] SET AUTO_SHRINK ON

Per altre informazioni su questo comando, vedere Opzioni ALTER DATABASE SET.For more information about this command, see DATABASE SET options.

Ricompilazione degli indiciRebuild indexes

Dopo la compattazione dei file di dati del database, gli indici possono diventare frammentati e perdere l'efficacia di ottimizzazione delle prestazioni.After database data files are shrunk, indexes may become fragmented and lose their performance optimization effectiveness. In caso di riduzione del livello delle prestazioni, provare a ricompilare gli indici del database.If performance degradation occurs, then consider rebuilding database indexes. Per altre informazioni sulla frammentazione e sulla ricompilazione degli indici, vedere Riorganizzare e ricompilare gli indici.For more information on fragmentation and rebuilding indexes, see Reorganize and Rebuild Indexes.

Passaggi successiviNext steps