管理 Azure SQL 数据库中的单一和池化数据库的文件空间Manage file space for single and pooled databases in Azure SQL Database

本文介绍了 Azure SQL 数据库中单一和池化数据库的各种类型的存储空间,以及当需要显式管理分配给数据库和弹性池的文件空间时可以执行的步骤。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.

概述Overview

Note

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

Important

PowerShell Azure 资源管理器模块仍受 Azure SQL 数据库的支持,但所有未来的开发都是针对 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. 若要了解这些 cmdlet,请参阅 AzureRM.SqlFor these cmdlets, see AzureRM.Sql. Az 模块和 AzureRm 模块中的命令参数大体上是相同的。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

使用 Azure SQL 数据库中的单一和池化数据库时,可能存在如下所述的工作负荷模式:其中数据库基础数据文件的分配可能会大于已使用数据页的数量。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. 如果使用的空间不断增大,并且后续删除了数据,则可能会出现这种情况。This condition can occur when space used increases and data is subsequently deleted. 这是因为分配的文件空间不会自动回收。The reason is because file space allocated is not automatically reclaimed when data is deleted.

在以下情况下,可能需要监视文件空间用量并收缩数据文件:Monitoring file space usage and shrinking data files may be necessary in the following scenarios:

  • 当分配给数据库的文件空间达到池的最大大小时,允许在弹性池中增大数据。Allow data growth in an elastic pool when the file space allocated for its databases reaches the pool max size.
  • 允许减少单一数据库或弹性池的最大大小。Allow decreasing the max size of a single database or elastic pool.
  • 允许将单一数据库或弹性池更改为最大大小更小的其他服务层或性能层。Allow changing a single database or elastic pool to a different service tier or performance tier with a lower max size.

监视文件空间用量Monitoring file space usage

Azure 门户和以下 API 中显示的大多数存储空间指标仅度量已用数据页面的大小:Most storage space metrics displayed in the Azure portal and the following APIs only measure the size of used data pages:

但是,以下 API 还度量分配给数据库和弹性池的空间大小:However, the following APIs also measure the size of space allocated for databases and elastic pools:

收缩数据文件Shrinking data files

SQL 数据库服务不会自动收缩数据文件来回收未使用的分配空间,因为这可能会影响数据库的性能。The SQL Database service does not automatically shrink data files to reclaim unused allocated space due to the potential impact to database performance. 但是,客户可遵循回收未使用的分配空间中所述的步骤,在其选定的时间通过自助式操作收缩数据文件。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.

Note

与数据文件不同,SQL 数据库服务会自动收缩日志文件,因为该操作不会影响数据库的性能。Unlike data files, the SQL Database service automatically shrinks log files since that operation does not impact database performance.

了解数据库存储空间的类型Understanding types of storage space for a database

了解以下存储空间数量对于管理数据库的文件空间非常重要。Understanding the following storage space quantities are important for managing the file space of a database.

数据库数量Database quantity 定义Definition 注释Comments
已用数据空间Data space used 用于存储数据库数据的空间量,以 8 KB 页面数为单位。The amount of space used to store database data in 8 KB pages. 通常,已用空间会在执行插入操作时增大,在执行删除操作时减小。Generally, space used increases (decreases) on inserts (deletes). 在某些情况下,已用空间不会在执行插入或删除操作时发生变化,具体取决于该操作涉及的数据数量和模式,以及是否有任何碎片。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. 例如,从每个数据页中删除一行不一定会减小已用空间。For example, deleting one row from every data page does not necessarily decrease the space used.
已分配的数据空间Data space allocated 可用于存储数据库数据的格式化文件空间量。The amount of formatted file space made available for storing database data. 已分配的空间量会自动增长,但执行删除操作后永远不会减小。The amount of space allocated grows automatically, but never decreases after deletes. 此行为可确保将来的插入操作速度更快,因为不需要重新设置空间的格式。This behavior ensures that future inserts are faster since space does not need to be reformatted.
已分配但未使用的数据空间Data space allocated but unused 已分配的数据空间量与已使用的数据空间量之间的差值。The difference between the amount of data space allocated and data space used. 此数量表示通过收缩数据库数据文件可回收的最大可用空间量。This quantity represents the maximum amount of free space that can be reclaimed by shrinking database data files.
数据最大大小Data max size 可用于存储数据库数据的最大空间量。The maximum amount of space that can be used for storing database data. 已分配的数据空间量在增长后不能超过数据最大大小。The amount of data space allocated cannot grow beyond the data max size.

下图演示了数据库的不同存储空间类型之间的关系。The following diagram illustrates the relationship between the different types of storage space for a database.

存储空间类型和关系

查询单一数据库的存储空间信息Query a single database for storage space information

可使用以下查询来确定单一数据库的存储空间数量。The following queries can be used to determine storage space quantities for a single database.

已用的数据库数据空间Database data space used

修改以下查询,返回已用的数据库数据空间量。Modify the following query to return the amount of database data space used. 查询结果以 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

已分配的,以及已分配但未使用的数据库数据空间Database data space allocated and unused allocated space

使用以下查询,返回已分配的,以及已分配但未使用的数据库数据空间量。Use the following query to return the amount of database data space allocated and the amount of unused space allocated. 查询结果以 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'

数据库数据最大大小Database data max size

修改以下查询,返回数据库数据最大大小。Modify the following query to return the database data max size. 查询结果以字节为单位。Units of the query result are in bytes.

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

了解弹性池存储空间的类型Understanding types of storage space for an elastic pool

了解以下存储空间数量对于管理弹性池的文件空间非常重要。Understanding the following storage space quantities are important for managing the file space of an elastic pool.

弹性池数量Elastic pool quantity 定义Definition 注释Comments
已用数据空间Data space used 弹性池中所有数据库已使用的数据空间总和。The summation of data space used by all databases in the elastic pool.
已分配的数据空间Data space allocated 弹性池中所有数据库已分配的数据空间总和。The summation of data space allocated by all databases in the elastic pool.
已分配但未使用的数据空间Data space allocated but unused 弹性池中所有数据库已分配的数据空间量与已使用的数据空间量之间的差值。The difference between the amount of data space allocated and data space used by all databases in the elastic pool. 此数量表示弹性池通过收缩数据库数据文件可回收的最大空间量。This quantity represents the maximum amount of space allocated for the elastic pool that can be reclaimed by shrinking database data files.
数据最大大小Data max size 可由弹性池用于其所有数据库的最大数据空间量。The maximum amount of data space that can be used by the elastic pool for all of its databases. 为弹性池分配的空间不应超过弹性池最大大小。The space allocated for the elastic pool should not exceed the elastic pool max size. 如果发生这种情况,可通过收缩数据库数据文件来回收未使用的空间。If this condition occurs, then space allocated that is unused can be reclaimed by shrinking database data files.

查询弹性池的存储空间信息Query an elastic pool for storage space information

可使用以下查询确定弹性池的存储空间数量。The following queries can be used to determine storage space quantities for an elastic pool.

已用的弹性池数据空间Elastic pool data space used

修改以下查询,返回已用的弹性池数据空间量。Modify the following query to return the amount of elastic pool data space used. 查询结果以 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

已分配的,以及已分配但未使用的弹性池数据空间Elastic pool data space allocated and unused allocated space

修改以下 PowerShell 脚本来返回一个表,其中列出了为弹性池中每个数据库分配的空间,以及已分配但未使用的空间。Modify the following PowerShell script to return a table listing the space allocated and unused allocated space for each database in an elastic pool. 该表中数据库的排序顺序为:已分配但未使用空间量最大的数据库排在最前,已分配但未使用空间量最小的数据库排在最后。The table orders databases from those databases with the greatest amount of unused allocated space to the least amount of unused allocated space. 查询结果以 MB 为单位。Units of the query result are in MB.

将查询结果(确定分配给池中每个数据库的空间)相加,可以确定为弹性池分配的总空间。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. 分配的弹性池空间不应超过弹性池最大大小。The elastic pool space allocated should not exceed the elastic pool max size.

PowerShell 脚本需要 SQL Server PowerShell 模块 - 请参阅下载 PowerShell 模块进行安装。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.chinacloudapi.cn,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

以下屏幕截图显示了脚本输出的示例:The following screenshot is an example of the output of the script:

已分配的,以及已分配但未使用的弹性池空间示例

弹性池数据最大大小Elastic pool data max size

修改以下 T-SQL 查询,返回弹性池数据最大大小。Modify the following T-SQL query to return the elastic pool data max size. 查询结果以 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

回收已分配但未使用的空间Reclaim unused allocated space

DBCC 收缩DBCC shrink

识别可回收已分配但未使用的空间的数据库后,请修改以下命令中的数据库名称,以收缩每个数据库的数据文件。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')

此命令在运行时可能会影响数据库的性能,请尽量在使用率较低的时候运行它。This command can impact database performance while it is running, and if possible should be run during periods of low usage.

有关此命令的详细信息,请参阅 SHRINKDATABASEFor more information about this command, see SHRINKDATABASE.

自动收缩Auto-shrink

或者,可以为数据库启用自动收缩。Alternatively, auto shrink can be enabled for a database. 自动收缩可降低文件管理的复杂性,并且与 SHRINKDATABASESHRINKFILE 相比,对数据库性能的影响更小。Auto shrink reduces file management complexity and is less impactful to database performance than SHRINKDATABASE or SHRINKFILE. 在管理包含多个数据库的弹性池时,自动收缩可能特别有用。Auto shrink can be particularly helpful for managing elastic pools with many databases. 但是,与 SHRINKDATABASESHRINKFILE 相比,自动收缩在回收文件空间方面的效率更低。However, auto shrink can be less effective in reclaiming file space than SHRINKDATABASE and SHRINKFILE. 若要启用自动收缩,请修改以下命令中的数据库名称。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

有关此命令的详细信息,请参阅 DATABASE SET 选项。For more information about this command, see DATABASE SET options.

重建索引Rebuild indexes

收缩数据库数据文件后,索引可能会碎片化,失去其性能优化效力。After database data files are shrunk, indexes may become fragmented and lose their performance optimization effectiveness. 如果性能降低,请考虑重建数据库索引。If performance degradation occurs, then consider rebuilding database indexes. 有关碎片和重新生成索引的详细信息,请参阅重新组织和重新生成索引For more information on fragmentation and rebuilding indexes, see Reorganize and Rebuild Indexes.

后续步骤Next steps