Azure SQL 数据库和 Azure Synapse Analytics 服务器的资源限制Resource limits for Azure SQL Database and Azure Synapse Analytics servers

适用于:是Azure SQL 数据库是Azure Synapse Analytics (SQL DW)APPLIES TO: yesAzure SQL Database yesAzure Synapse Analytics (SQL DW)

本文概述了 Azure SQL 数据库和 Azure Synapse Analytics 所使用的逻辑服务器的资源限制。This article provides an overview of the resource limits for the logical server used by Azure SQL Database and Azure Synapse Analytics. 其中提供了有关在达到或超出这些资源限制时会发生什么情况的信息。It provides information on what happens when those resource limits are hit or exceeded.

备注

有关 Azure SQL 托管实例限制,请参阅托管实例的 SQL 数据库资源限制For Azure SQL 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.

重要

随着数据库的数量接近每个服务器的限制,可能出现以下情况:As the number of databases approaches the limit per 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.

备注

若要获取更高的 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

计算 CPUCompute CPU

当数据库计算 CPU 使用率变高时,查询延迟也会增加,查询甚至可能超时。在上述情况下,服务可能对查询排队,并在资源可用时向查询提供资源以用于执行。When database compute CPU utilization 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.

内存Memory

与其他资源(CPU、辅助角色、存储)不同,达到内存限制不会对查询性能产生负面影响,也不会导致错误和失败。Unlike other resources (CPU, workers, storage), reaching the memory limit does not negatively impact query performance, and does not cause errors and failures. 正如内存管理体系结构指南中详细描述的那样,SQL Server 数据库引擎通常使用所有可用内存,这是设计使然。As described in detail in Memory Management Architecture Guide, the SQL Server database engine often uses all available memory, by design. 内存主要用于缓存数据,以避免更昂贵的存储访问。Memory is used primarily for caching data, to avoid more expensive storage access. 因此,较高的内存利用率通常会提高查询性能,因为从内存中读取的速度更快,而从存储中读取的速度更慢。Thus, higher memory utilization usually improves query performance due to faster reads from memory, rather than slower reads from storage.

在数据库引擎启动之后,当工作负载开始从存储中读取数据时,数据库引擎会积极地在内存中缓存数据。After database engine startup, as the workload starts to read data from storage, the database engine aggressively caches data in memory. 经过这一初始增长期之后,通常可看到 sys.dm_db_resource_stats 中的 avg_memory_usage_percentavg_instance_memory_percent 列接近或等于 100%,尤其是对于非空闲且不能完全装入内存的数据库。After this initial ramp-up period, it is common and expected to see the avg_memory_usage_percent and avg_instance_memory_percent columns in sys.dm_db_resource_stats to be close or equal to 100%, particularly for databases that are not idle, and do not fully fit in memory.

除了数据缓存之外,内存还用于数据库引擎的其他组件。Besides the data cache, memory is used in other components of the database engine. 当有内存需求且所有可用内存已被数据缓存占用时,数据库引擎将动态收缩数据缓存大小以使内存可供其他组件使用,并在其他组件释放内存时动态增加数据缓存。When there is demand for memory and all available memory has been used by the data cache, the database engine will dynamically shrink data cache size to make memory available to other components, and will dynamically grow data cache when other components release memory.

在极少数情况下,要求十分高的工作负载可能会导致内存不足,从而导致内存不足错误。In rare cases, a sufficiently demanding workload may cause an insufficient memory condition, leading to out-of-memory errors. 这可能发生在内存使用率介于 0% 和 100% 之间的任何级别。This can happen at any level of memory utilization between 0% and 100%.

遇到内存不足错误时,缓解选项包括:When encountering out-of-memory errors, mitigation options include:

解决方案Solution 说明Description
减少内存授予的大小Reduce the size of memory grants 有关内存授予的详细信息,请参阅了解 SQL Server 内存授予博客文章。For more information about memory grants, see the Understanding SQL Server memory grant blog post. 避免过大内存授予的一个常见解决方案是使统计信息保持最新状态。A common solution for avoiding excessively large memory grants is keeping statistics up to date. 这可使查询引擎更准确地估计内存消耗,从而避免内存授予过大。This results in more accurate estimates of memory consumption by the query engine, avoiding unnecessarily large memory grants.

在使用兼容级别 140 及更高级别的数据库中,数据库引擎可使用批处理模式内存授予反馈自动调整内存授予大小。In databases using compatibility level 140 and later, the database engine may automatically adjust memory grant size using Batch mode memory grant feedback. 在使用兼容级别 150 及更高级别的数据库中,数据库引擎类似地使用行模式内存授予反馈,用于更常见的行模式查询。In databases using compatibility level 150 and later, the database engine similarly uses Row mode memory grant feedback, for more common row mode queries. 此内置功能有助于避免由于内存授予过大而导致的内存不足错误。This built-in functionality helps avoid out-of-memory errors due to unnecessarily large memory grants.
减小查询计划缓存的大小Reduce the size of query plan cache 数据库引擎在内存中缓存查询计划,以避免为每次查询执行编译查询计划。The database engine caches query plans in memory, to avoid compiling a query plan for every query execution. 若要避免由于仅使用一次的缓存计划而导致的查询计划缓存膨胀,请启用 OPTIMIZE_FOR_AD_HOC_WORKLOADS 数据库范围内的配置To avoid query plan cache bloat caused by caching plans that are only used once, enable the OPTIMIZE_FOR_AD_HOC_WORKLOADS database-scoped configuration.
减小锁定内存的大小Reduce the size of lock memory 数据库引擎将内存用于锁定The database engine uses memory for locks. 如果可能,请避免可能获取大量锁定并导致高锁定内存消耗的大型事务。When possible, avoid large transactions that may acquire a large number of locks and cause high lock memory consumption.

用户工作负荷和内部进程的资源消耗量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 aren't 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 列中报告用户工作负载和内部进程的总 CPU 和内存消耗量。Total CPU and memory consumption by user workloads and internal processes 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's 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's 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