Compartir a través de

管理 Azure SQL 托管实例中的数据库的文件空间

适用于:Azure SQL 托管实例

本文介绍了如何在 Azure SQL 托管实例中监视和管理数据库中的文件。 我们会了解如何监视数据库文件大小、收缩事务日志、放大事务日志文件,以及控制事务日志文件的增长。

本文适用于 Azure SQL 托管实例。 虽然非常相似,但有关在 SQL Server 中管理事务日志文件大小的信息,请参阅管理事务日志文件的大小

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

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

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

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

关系图显示数据库数量表中不同数据库空间概念的大小。

查询单一数据库的文件空间信息

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

监视日志空间使用情况

使用 sys.dm_db_log_space_usage 监视日志空间使用情况。 此 DMV 返回有关当前使用的日志空间量信息,并指示何时需要截断事务日志。

若要了解有关日志文件的当前大小、最大大小以及文件的自动增长选项的信息,还可以在sys.database_files中针对此日志文件使用sizemax_sizegrowth列。

基于 Azure 资源管理器的指标以下 API 中显示的存储空间指标仅度量已用数据页面的大小。 有关示例,请参阅 PowerShell get-metrics

收缩日志文件大小

要通过删除未使用的空间来减少物理日志文件的物理大小,必须收缩日志文件。 只有当事务日志文件包含未使用的空间时,收缩才会产生影响。 如果日志文件已满(可能是由于打开的事务),请调查 什么正在阻止事务日志截断

注意

收缩操作不应被视为常规维护操作。 由于常规定期业务操作而增长的数据和日志文件不需要收缩操作。 Shrink 命令在运行时可能会影响数据库的性能,请尽量在使用率较低的时候运行它。 如果常规应用程序工作负载会导致文件再次增长到相同的分配大小,则不建议收缩数据文件。

注意收缩数据库文件的潜在负面影响,请参阅收缩后索引维护。 在极少数情况下,收缩操作可能会受到自动数据库备份的影响。 如有必要,请重试收缩操作。

收缩事务日志前,请记住可能延迟日志截断的因素。 如果在日志收缩后还需要存储空间,则会再次增加事务日志,导致在增加日志操作期间产生性能开销。 有关详细信息,请参阅建议

仅当数据库处于联机状态,而且至少一个虚拟日志文件 (VLF) 可用时,才能收缩日志文件。 在某些情况下,直到下一个日志截断后,才能收缩日志。

能够延长VLF活动时间的因素(如长时间运行的事务)可以限制甚至阻止日志收缩。 有关详细信息,请参阅可能延迟日志截断的因素

收缩日志文件可删除一个或多个不包含逻辑日志任何部分的 VLF(即不活动的 VLF)。 收缩事务日志文件时,将从日志文件末端删除不活动的 VLF,以将日志减小到接近目标大小。

有关收缩操作的详细信息,请查看以下内容:

收缩日志文件(而不收缩数据库文件)

监视日志文件收缩事件

监视日志空间

收缩后的索引维护

对数据文件执行完收缩操作后,索引可能会变得碎片化。 这会使某些工作负载的性能优化不再有效,例如使用大型扫描进行的查询。 如果在收缩操作完成后性能下降,请考虑通过索引维护来重新生成索引。 请记住,重新生成索引需要使用数据库中的可用空间,因此可能会导致已分配的空间增加,从而抵消收缩的影响。

有关索引维护的详细信息,请参阅优化索引维护以提高查询性能并减少资源消耗

评估索引页密度

如果截断数据文件未导致分配的空间的充分减少,可以决定收缩数据库数据文件,以从这些文件回收未使用的空间。 但是,作为可选但建议的步骤,应首先确定数据库中索引的平均页面密度。 对于相同数量的数据,如果页面密度较高,收缩将更快完成,因为它只需移动更少的页面。 如果某些索引的页面密度较低,请考虑对这些索引执行维护,以增加页面密度,然后再收缩数据文件。 这也将使收缩可以进一步减少分配的存储空间。

若要确定数据库中所有索引的页面密度,请使用以下查询。 页面密度在 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;

如果存在页数较多且页密度低于 60-70% 的索引,请考虑在收缩数据文件之前重新生成或重新组织这些索引。

注意

对于较大的数据库,用于确定页面密度的查询可能需要很长时间(数小时)才能完成。 此外,重新生成或重新组织大型索引也需要大量时间和资源使用。 一方面是花费额外的时间增加页面密度,另一方面是减少收缩持续时间和节省更多空间,这两者之间存在着一种权衡。

如果有多个索引的页面密度较低,可以在多个数据库会话中并行重新生成它们,以加快该过程。 但是,请确保这样做不会接近数据库资源限制,并为应用程序工作负载留出足够的资源空余空间。 在 Azure 门户中或使用 sys.dm_db_resource_stats 视图监视资源消耗(CPU、数据 IO、日志 IO),并且仅当上述每个维度上的资源利用率一直远低于 100% 时才启动其他并行重新生成。 如果 CPU、数据 IO 或日志 IO 利用率为 100%,则可以纵向扩展数据库,以拥有更多 CPU 核心并增加 IO 吞吐量,从而允许额外的并行重建以更快地完成该过程。

示例索引重新生成命令

下面是使用 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);

此命令将启动联机的可恢复索引重新生成。 这样,并发工作负载可以在重新生成过程中继续使用表,你可以在重新生成因任何原因中断后继续重新生成。 但是,这种类型的重新生成比脱机重新生成慢,后者会阻止对表的访问。 如果在重新生成期间没有其他工作负载需要访问表,请将 ONLINERESUMABLE 选项设置为 OFF 并删除 WAIT_AT_LOW_PRIORITY 子句。

若要了解有关索引维护的详细信息,请参阅优化索引维护以提高查询性能并减少资源消耗

收缩多个数据文件

如前所述,通过数据移动进行收缩是一个长时间运行的过程。 如果数据库有多个数据文件,可以通过并行收缩多个数据文件来加快该过程。 为此,可以打开多个数据库会话,并在每个会话中使用具有不同 file_id 值的 DBCC SHRINKFILE。 与前面重新生成索引类似,在开始每个新的并行收缩命令之前,请确保你有足够的资源空余空间(CPU、数据 IO、日志 IO)。

以下示例命令通过使用file_id 4 文件,移动文件中的页面来收缩数据文件,尝试将分配的大小缩小到 52,000 MB:

DBCC SHRINKFILE (4, 52000);

如果想将分配给文件的空间缩小到最小,请执行以下语句而不指定目标大小:

DBCC SHRINKFILE (4);

如果工作负载与收缩同时运行,它可能会在收缩完成并截断文件之前开始使用收缩释放的存储空间。 在这种情况下,收缩将无法将已分配的空间缩小到指定的目标。

可以通过以较小的跨度收缩每个文件来缓解这种情况。 这意味着,在DBCC SHRINKFILE命令中,设置的目标略小于文件当前分配的空间。 例如,如果为 file_id 为 4 的文件分配的空间是 200,000 MB,并且要将其缩小到 100,000 MB,则可以先将目标设置为 170,000 MB:

DBCC SHRINKFILE (4, 170000);

此命令完成后,它将截断文件,并将其分配大小减小到 170,000 MB。 然后可以重复此命令,先将目标设置为 140,000 MB,然后设置为 110,000 MB,依此类推,直到文件缩小到所需的大小。 如果命令完成但文件未截断,请使用更小的跨度,例如 15,000 MB 而不是 30,000 MB。

若要监视所有并发运行的收缩会话的收缩进度,可以使用以下查询:

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');

注意

收缩进度可能是非线性的,并且 percent_complete 列中的值可能在很长一段时间内几乎保持不变,即使收缩仍在进行中。

完成所有数据文件的收缩后,请使用空间使用情况查询确定分配的存储大小的最终减少量。 如果已用空间和分配的空间之间仍有较大差异,则可以重新生成索引。 这可能会暂时进一步增加分配的空间,但是在重新生成索引后再次收缩数据文件,应会使分配的空间进一步减小。

放大日志文件

在 Azure SQL 托管实例中,放大现有日志文件(如果磁盘空间允许)以将空间添加到日志文件。 不支持将日志文件添加到数据库。 一个事务日志文件就足够了,除非日志空间不足且保留日志文件的卷上的磁盘空间也不足。

要扩大日志文件,请使用 ALTER DATABASE 语句的 MODIFY FILE 子句,指定 SIZEMAXSIZE 语法。 有关详细信息,请参阅 ALTER DATABASE (Transact-SQL) 文件和文件组选项

有关详细信息,请参阅建议

控制事务日志文件的增长

使用 ALTER DATABASE (Transact-SQL) 文件和文件组选项语句管理事务日志文件的增长。 请注意以下事项:

  • 要更改当前文件大小(以 KB、MB、GB 和 TB 为单位),请使用 SIZE 选项。
  • 要更改增量,请使用 FILEGROWTH 选项。 如果值为 0,则表明自动增长已设置为关闭,且不允许增加空间。
  • 要控制日志文件的最大大小(以 KB、MB、GB 和 TB 为单位)或将增长设置为 UNLIMITED,请使用MAXSIZE选项。

建议

下面是使用事务日志文件时的一些一般建议:

  • FILEGROWTH 选项设置的事务日志的自动增长 (autogrow) 增量必须足够大,以领先于工作负载事务的需求。 因此,为了避免经常向日志文件中扩充内容,应该采用足够大的文件增量。 要正确设置事务日志的大小,建议监视以下时间内所占用的日志数量:

    • 执行完整备份所需的时间,因为日志备份在其完成后才能进行。
    • 最大型索引维护操作所需的时间。
    • 在数据库中执行最大批操作所需的时间。
  • 使用FILEGROWTH选项设置数据和日志文件的autogrow时,建议首选使用size而不是使用percentage进行设置,以便更好地控制增长比,因为 percentage 表示的是日益增长量。

    • 在 Azure SQL 托管实例中,即时文件初始化可能会使事务日志增长事件受益高达 64 MB。 新数据库的默认自动增长大小增量为 64 MB。 大于 64 MB 的事务日志文件自动增长事件则无法利用即时文件初始化。
    • 最佳做法是,针对日志事务,请勿将 FILEGROWTH 选项值设置为超过 1,024 MB。
  • 小型的自动增长增量可能生成过多的VLF并且可能降低性能。 若要确定给定实例中所有数据库的当前事务日志大小的最佳 VLF 分发,以及实现所需大小需要的增长量,请参阅此脚本以分析和修复 VDF(由 SQL Tiger Team 提供)

  • 大型自动增长增量可能会导致两个问题:

    • 大型自动增长增量可能会导致数据库在分配新空间时暂停,这可能会导致查询超时。
    • 大型的自动增长增量可能生成过少的大型VLF并且也可能影响性能。 若要确定给定实例中所有数据库的当前事务日志大小的最佳 VLF 分发,以及实现所需大小需要的增长量,请参阅此脚本以分析和修复 VDF(由 SQL Tiger Team 提供)
  • 即使启用自动增长,如果增长速度不能满足查询需求,也可能收到提示事务日志已满的消息。 有关更改增长增量的详细信息,请参阅ALTER DATABASE (Transact-SQL) 文件和文件组选项

  • 日志文件可以设为自动收缩。 但是,不建议这样做,auto_shrink 数据库属性默认设为 FALSE。 如果 auto_shrink 设置为 TRUE,则仅当其空间的 25% 以上未使用时,自动收缩才会减少文件的大小