管理 Azure SQL 数据库中的单一和入池数据库的文件空间

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

概述

Note

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介。 有关安装说明,请参阅安装 Azure PowerShell

Important

PowerShell Azure 资源管理器模块仍受 Azure SQL 数据库的支持,但所有未来的开发都是针对 Az.Sql 模块的。 若要了解这些 cmdlet,请参阅 AzureRM.Sql。 Az 模块和 AzureRm 模块中的命令参数大体上是相同的。

使用 Azure SQL 数据库中的单一和入池数据库时,可能存在如下所述的工作负荷模式:其中数据库基础数据文件的分配可能会大于已使用数据页的数量。 如果使用的空间不断增大,并且后续删除了数据,则可能会出现这种情况。 这是因为分配的文件空间不会自动回收。

在以下情况下,可能需要监视文件空间用量并收缩数据文件:

  • 当分配给数据库的文件空间达到池的最大大小时,允许在弹性池中增大数据。
  • 允许减少单一数据库或弹性池的最大大小。
  • 允许将单一数据库或弹性池更改为最大大小更小的其他服务层或性能层。

监视文件空间用量

Azure 门户和以下 API 中显示的大多数存储空间指标仅度量已用数据页面的大小:

但是,以下 API 还度量分配给数据库和弹性池的空间大小:

收缩数据文件

SQL 数据库服务不会自动收缩数据文件来回收未使用的分配空间,因为这可能会影响数据库的性能。 但是,客户可遵循回收未使用的分配空间中所述的步骤,在其选定的时间通过自助式操作收缩数据文件。

Note

与数据文件不同,SQL 数据库服务会自动收缩日志文件,因为该操作不会影响数据库的性能。

了解数据库存储空间的类型

了解以下存储空间数量对于管理数据库的文件空间非常重要。

数据库数量 定义 注释
已用数据空间 用于存储数据库数据的空间量,以 8 KB 页面数为单位。 通常,已用空间会在执行插入操作时增大,在执行删除操作时减小。 在某些情况下,已用空间不会在执行插入或删除操作时发生变化,具体取决于该操作涉及的数据数量和模式,以及是否有任何碎片。 例如,从每个数据页中删除一行不一定会减小已用空间。
已分配的数据空间 可用于存储数据库数据的格式化文件空间量。 已分配的空间量会自动增长,但执行删除操作后永远不会减小。 此行为可确保将来的插入操作速度更快,因为不需要重新设置空间的格式。
已分配但未使用的数据空间 已分配的数据空间量与已使用的数据空间量之间的差值。 此数量表示通过收缩数据库数据文件可回收的最大可用空间量。
数据最大大小 可用于存储数据库数据的最大空间量。 已分配的数据空间量在增长后不能超过数据最大大小。

下图演示了数据库的不同存储空间类型之间的关系。

存储空间类型和关系

查询单一数据库的存储空间信息

可使用以下查询来确定单一数据库的存储空间数量。

已用的数据库数据空间

修改以下查询,返回已用的数据库数据空间量。 查询结果以 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

已分配的,以及已分配但未使用的数据库数据空间

使用以下查询,返回已分配的,以及已分配但未使用的数据库数据空间量。 查询结果以 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'

数据库数据最大大小

修改以下查询,返回数据库数据最大大小。 查询结果以字节为单位。

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

了解弹性池存储空间的类型

了解以下存储空间数量对于管理弹性池的文件空间非常重要。

弹性池数量 定义 注释
已用数据空间 弹性池中所有数据库已使用的数据空间总和。
已分配的数据空间 弹性池中所有数据库已分配的数据空间总和。
已分配但未使用的数据空间 弹性池中所有数据库已分配的数据空间量与已使用的数据空间量之间的差值。 此数量表示弹性池通过收缩数据库数据文件可回收的最大空间量。
数据最大大小 可由弹性池用于其所有数据库的最大数据空间量。 为弹性池分配的空间不应超过弹性池最大大小。 如果发生这种情况,可通过收缩数据库数据文件来回收未使用的空间。

查询弹性池的存储空间信息

可使用以下查询确定弹性池的存储空间数量。

已用的弹性池数据空间

修改以下查询,返回已用的弹性池数据空间量。 查询结果以 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

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

修改以下 PowerShell 脚本来返回一个表,其中列出了为弹性池中每个数据库分配的空间,以及已分配但未使用的空间。 该表中数据库的排序顺序为:已分配但未使用空间量最大的数据库排在最前,已分配但未使用空间量最小的数据库排在最后。 查询结果以 MB 为单位。

将查询结果(确定分配给池中每个数据库的空间)相加,可以确定为弹性池分配的总空间。 分配的弹性池空间不应超过弹性池最大大小。

PowerShell 脚本需要 SQL Server PowerShell 模块 - 请参阅下载 PowerShell 模块进行安装。

# 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

以下屏幕截图显示了脚本输出的示例:

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

弹性池数据最大大小

修改以下 T-SQL 查询,返回弹性池数据最大大小。 查询结果以 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

回收已分配但未使用的空间

DBCC 收缩

识别可回收已分配但未使用的空间的数据库后,请修改以下命令中的数据库名称,以收缩每个数据库的数据文件。

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

此命令在运行时可能会影响数据库的性能,请尽量在使用率较低的时候运行它。

有关此命令的详细信息,请参阅 SHRINKDATABASE

自动收缩

或者,可以为数据库启用自动收缩。 自动收缩可降低文件管理的复杂性,并且与 SHRINKDATABASESHRINKFILE 相比,对数据库性能的影响更小。 在管理包含多个数据库的弹性池时,自动收缩可能特别有用。 但是,与 SHRINKDATABASESHRINKFILE 相比,自动收缩在回收文件空间方面的效率更低。 若要启用自动收缩,请修改以下命令中的数据库名称。

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

有关此命令的详细信息,请参阅 DATABASE SET 选项。

重建索引

收缩数据库数据文件后,索引可能会碎片化,失去其性能优化效力。 如果性能降低,请考虑重建数据库索引。 有关碎片和重新生成索引的详细信息,请参阅重新组织和重新生成索引

后续步骤