SQL 数据库资源限制SQL Database resource limits

本文概述了管理单一数据库和弹性池的 SQL 数据库服务器的 SQL 数据库资源限制。This article provides an overview of the SQL Database resource limits for a SQL Database server that manages single databases and elastic pools. 其中提供了有关在达到或超出这些资源限制时会发生什么情况的信息。It provides information on what happens when those resource limits are hit or exceeded.

Note

有关托管实例限制,请参阅托管实例的 SQL 数据库资源限制For Managed Instance limits, see SQL Database resource limits for managed instances.

最大资源限制Maximum resource limits

资源Resource 限制Limit
每个服务器的数据库数Databases per server 50005000
任意区域中每个订阅的服务器默认数量Default number of servers per subscription in any region 20 个20
任意区域中每个订阅的服务器数上限Max number of servers per subscription in any region 200200
每个服务器的 DTU/eDTU 配额DTU / eDTU quota per server 54,00054,000
每个服务器/实例的 vCore 配额vCore quota per server/instance 540540
每个服务器的最大池数Max pools per server 受限于 DTU 或 vCore 数。Limited by number of DTUs or vCores. 例如,如果每个池是 1000 个 DTU,则一个服务器可以支持 54 个池。For example, if each pool is 1000 DTUs, then a server can support 54 pools.

Important

随着数据库的数量接近每个 SQL 数据库服务器的限制,可能出现以下情况:As the number of databases approaches the limit per SQL Database server, the following can occur:

  • 对主数据库运行查询的延迟增加。Increasing latency in running queries against the master database. 这包括资源利用率统计信息的视图,如 sys.resource_stats。This includes views of resource utilization statistics such as sys.resource_stats.
  • 管理操作和呈现门户视点(涉及枚举服务器中的数据库)的延迟增加。Increasing latency in management operations and rendering portal viewpoints that involve enumerating databases in the server.

Note

若要获取更高的 DTU/eDTU 配额、vCore 配额或超过默认数量的服务器,请在 Azure 门户中提交新的支持请求。To obtain more DTU/eDTU quota, vCore quota, or more servers than the default amount, submit a new support request in the Azure portal.

存储大小Storage size

对于单一数据库资源存储大小,请参阅基于 DTU 的资源限制基于 vCore 的资源限制,了解每个定价层的存储大小限制。For single databases resource storage sizes, refer to either DTU-based resource limits or vCore-based resource limits for the storage size limits per pricing tier.

如果达到数据库资源限制,会发生什么?What happens when database resource limits are reached

计算(DTU 和 eDTU/vCore)Compute (DTUs and eDTUs / vCores)

如果数据库计算资源利用率(衡量依据为 DTU 和 eDTU 或 vCore)变高,查询延迟会增加,甚至可能会出现查询超时的现象。在上述情况下,服务可能对查询排队,并在资源可用时向查询提供资源以用于执行。When database compute utilization (measured by DTUs and eDTUs, or vCores) becomes high, query latency increases, and queries can even time out. Under these conditions, queries may be queued by the service and are provided resources for execution as resources become free. 计算使用率变高时,风险缓解选项包括:When encountering high compute utilization, mitigation options include:

存储Storage

当使用的数据库空间到达上限时,将无法进行增加数据大小的数据库插入和更新操作,客户端会收到错误消息When database space used reaches the max size limit, database inserts and updates that increase the data size fail and clients receive an error message. SELECT 和 DELETE 语句不受影响。SELECT and DELETE statements continue to succeed.

空间使用率变高时,风险缓解选项包括:When encountering high space utilization, mitigation options include:

会话和辅助角色(请求)Sessions and workers (requests)

会话和辅助角色的数目上限由服务层级和计算大小(DTU/eDTU 或 vCore)决定。The maximum numbers of sessions and workers are determined by the service tier and compute size (DTUs/eDTUs or vCores). 当到达会话或辅助角色上限时,新的请求将被拒绝,客户端将收到错误消息。New requests are rejected when session or worker limits are reached, and clients receive an error message. 虽然应用程序可以轻松地控制可用的连接数,但并行辅助角色数通常更难以估计和控制。While the number of connections available can be controlled by the application, the number of concurrent workers is often harder to estimate and control. 在负荷高峰期,当数据库资源达到上限,辅助角色由于查询运行时间较长、阻塞链大或查询并行度过高而堆积时,这种情况尤其突出。This is especially true during peak load periods when database resource limits are reached and workers pile up due to longer running queries, large blocking chains, or excessive query parallelism.

会话或辅助角色使用率变高时,风险缓解选项包括:When encountering high session or worker utilization, mitigation options include:

  • 提高数据库或弹性池的服务层级或计算大小。Increasing the service tier or compute size of the database or elastic pool. 请参阅缩放单一数据库资源缩放弹性池资源See Scale single database resources and Scale elastic pool resources.
  • 如果争用计算资源造成了辅助角色使用率上升,请优化查询,以降低每项查询的资源使用率。Optimizing queries to reduce the resource utilization of each query if the cause of increased worker utilization is due to contention for compute resources. 有关详细信息,请参阅查询优化/提示For more information, see Query Tuning/Hinting.
  • 减小 MAXDOP(最大并行度)设置。Reducing the MAXDOP (maximum degree of parallelism) setting.
  • 优化查询工作负荷,以减少查询受阻的发生次数和持续时间。Optimizing query workload to reduce number of occurrences and duration of query blocking.

用户工作负荷和内部进程的资源消耗量Resource consumption by user workloads and internal processes

将在 sys.dm_db_resource_statssys.resource_stats 视图的 avg_cpu_percentavg_memory_usage_percent 列中报告每个数据库中的用户工作负荷的 CPU 和内存消耗量。CPU and memory consumption by user workloads in each database is reported in the sys.dm_db_resource_stats and sys.resource_stats views, in avg_cpu_percent and avg_memory_usage_percent columns. 对于弹性池,将在 sys.elastic_pool_resource_stats 视图中报告池级别的资源消耗量。For elastic pools, pool-level resource consumption is reported in the sys.elastic_pool_resource_stats view. 对于池级别的单一数据库弹性池,还会通过 Azure Monitor 指标 cpu_percent 报告用户工作负荷的 CPU 消耗量。User workload CPU consumption is also reported via the cpu_percent Azure Monitor metric, for single databases and elastic pools at the pool level.

Azure SQL 数据库需要使用计算资源来实现核心服务功能,例如高可用性和灾难恢复、数据库备份和还原、监视、查询存储、自动优化,等等。对于这些内部进程,系统会从总体资源中为其留出有限的一部分特定资源,使剩余的资源可供用户工作负荷使用。Azure SQL Database requires compute resources to implement core service features such as high availability and disaster recovery, database backup and restore, monitoring, Query Store, Automatic tuning, etc. The system sets aside a certain limited portion of the overall resources for these internal processes, making the remainder of resources available for user workloads. 当内部进程不使用计算资源时,系统会将其提供给用户工作负荷使用。At times when internal processes are not using compute resources, the system makes them available to user workloads.

将在 sys.dm_db_resource_statssys.resource_stats 视图的 avg_instance_cpu_percentavg_instance_memory_percent 列内,报告托管单一数据库或弹性池的 SQL Server 实例上用户工作负荷与内部进程的 CPU 和内存总消耗量。Total CPU and memory consumption by user workloads and internal processes on the SQL Server instance hosting a single database or an elastic pool is reported in the sys.dm_db_resource_stats and sys.resource_stats views, in avg_instance_cpu_percent and avg_instance_memory_percent columns. 对于池级别的单一数据库弹性池,还会通过 Azure Monitor 指标 sqlserver_process_core_percentsqlserver_process_memory_percent 报告此数据。This data is also reported via the sqlserver_process_core_percent and sqlserver_process_memory_percent Azure Monitor metrics, for single databases and elastic pools at the pool level.

在性能监视和故障排除上下文中,必须考虑用户 CPU 消耗量(avg_cpu_percentcpu_percent),以及用户工作负荷和内部进程的 CPU 总消耗量(avg_instance_cpu_percentsqlserver_process_core_percent) 。In the context of performance monitoring and troubleshooting, it is important to consider both user CPU consumption (avg_cpu_percent, cpu_percent), and total CPU consumption by user workloads and internal processes (avg_instance_cpu_percent,sqlserver_process_core_percent).

用户 CPU 消耗量的计算值为每个服务目标中用户工作负荷限制的一个百分比 。User CPU consumption is calculated as a percentage of the user workload limits in each service objective. 用户 CPU 利用率为 100% 表示用户工作负荷达到了服务目标的限制 。User CPU utilization at 100% indicates that the user workload has reached the limit of the service objective. 但是,当 CPU 总消耗量达到 70-100% 范围时,即使报告的用户 CPU 消耗量明显低于 100%,也可能会看到用户工作负荷吞吐量保持平稳,但查询延迟增大 。However, when total CPU consumption reaches the 70-100% range, it is possible to see user workload throughput flattening out and query latency increasing, even if reported user CPU consumption remains significantly below 100%. 对适度分配的计算资源但相对密集的用户工作负荷使用较小的服务目标时,更有可能会发生这种情况。This is more likely to occur when using smaller service objectives with a moderate allocation of compute resources, but relatively intense user workloads. 当内部进程临时需要更多的资源时(例如,在创建数据库的新副本时),使用较小的服务目标也可能发生这种情况。This can also occur with smaller service objectives when internal processes temporarily require additional resources, for example when creating a new replica of the database.

当 CPU 总消耗量较高时,缓解措施与前面所述相同,也包括增大服务目标和/或优化用户工作负荷 。When total CPU consumption is high, mitigation options are the same as noted earlier, and include service objective increase and/or user workload optimization.

后续步骤Next steps