Azure SQL Database 伺服器的 SQL Database 資源限制SQL Database resource limits for Azure SQL Database server

此文章針對管理單一資料庫和彈性集區的 SQL Database 伺服器,提供 SQL Database 資源限制的概觀。This article provides an overview of the SQL Database resource limits for a SQL Database server that manages single databases and elastic pools. 它也提供抵達或超過那些資源限制時所會發生之情況的相關資訊。It also provides information regarding what happens when those resource limits are hit or exceeded.

注意

如需受控執行個體限制,請參閱適用於受控執行個體的 SQL Database 資源限制For managed instances limits, see SQL Database resource limits for managed instances.

資源限制上限Maximum resource limits

ResourceResource 限制Limit
每一伺服器的資料庫Databases per server 50005000
任何區域中每個訂用帳戶的伺服器預設數目Default number of servers per subscription in any region 2020
任何區域中每個訂用帳戶的伺服器最大數目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.

注意

若要獲得大於預設數量的 DTU/eDTU 配額、vCore 配額或伺服器,可以在 Azure 入口網站中,針對訂用帳戶使用問題類型「配額」提交新的支援要求。To obtain more DTU /eDTU quota, vCore quota, or more servers than the default amount, a new support request can be submitted in the Azure portal for the subscription with issue type “Quota”. 每一伺服器的 DTU/eDTU 配額和資料庫限制會限制每一部伺服器的彈性集區數目。The DTU / eDTU quota and database limit per server constrains the number of elastic pools per server.

重要

每當資料庫數量接近每台 SQL Database 伺服器的限制時,可能會出現下列情況:As the number of databases approaches the limit per SQL Database server, the following can occur:

  • 使用 master 資料庫執行查詢時,延遲狀況增加。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.

儲存體大小Storage size

達到資料庫資源限制時,會發生什麼事?What happens when database resource limits are reached

計算 (DTU 和 eDTU / 虛擬核心)Compute (DTUs and eDTUs / vCores)

當資料庫計算使用率 (根據 DTU 和 eDTU 或虛擬核心測量) 變高時,查詢延遲會增加,甚至可能逾時。在下列情況下,查詢可能會透過服務排入佇列,並在資源變成可用時,提供資源以供執行。When database compute utilization (measured by DTUs and eDTUs, or vCores) becomes high, query latency increases and can even time out. Under these conditions, queries may be queued by the service and are provided resources for execution as resource 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. 資料庫 SELECTS 與 DELETES 會繼續執行下去。Database SELECTS and DELETES continue to succeed.

遇到高空間使用率時,緩和選項包括:When encountering high space utilization, mitigation options include:

工作階段和背景工作角色 (要求)Sessions and workers (requests)

工作階段與背景工作角色的數量上限取決於服務層級和計算大小 (DTU 與 eDTU)。The maximum number of sessions and workers are determined by the service tier and compute size (DTUs and eDTUs). 達到工作階段或背景工作角色的限制時,會拒絕新要求,而且用戶端會收到錯誤訊息。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.

當工作階段或背景工作角色出現高使用率時,緩和選項包括:When encountering high session or worker utilization, mitigation options include:

交易記錄速率治理Transaction Log Rate Governance

交易記錄速率治理是 Azure SQL Database 中的程式, 用來限制大量插入、選取 INTO 和索引組建等工作負載的高內嵌速率。Transaction log rate governance is a process in Azure SQL Database used to limit high ingestion rates for workloads such as bulk insert, SELECT INTO, and index builds. 這些限制會在子秒層級追蹤並強制執行, 以產生記錄檔記錄的速率, 而不論可能針對資料檔案發出多少 Io, 都能限制輸送量。These limits are tracked and enforced at the sub-second level to the rate of log record generation, limiting throughput regardless of how many IOs may be issued against data files. 交易記錄產生速率目前已線性相應增加至與硬體相依的點, 且最大記錄速率允許使用 vCore 購買模型 96 MB/s。Transaction log generation rates currently scale linearly up to a point that is hardware dependent, with the maximum log rate allowed being 96 MB/s with the vCore purchasing model.

注意

實際的實體 IOs 到交易記錄檔不受管理或限制。The actual physical IOs to transaction log files are not governed or limited.

記錄速率的設定可讓您在各種情況下達成和持續, 而整體系統可以維持其功能, 並將對使用者負載的影響降至最低。Log rates are set such that they can be achieved and sustained in a variety of scenarios, while the overall system can maintain its functionality with minimized impact to the user load. 記錄速率治理可確保交易記錄備份保留在已發佈的復原能力 Sla 內。Log rate governance ensures that transaction log backups stay within published recoverability SLAs. 這種治理也會防止次要複本上有過多的待處理專案。This governance also prevents an excessive backlog on secondary replicas.

產生記錄檔記錄時, 系統會評估每個作業並評估是否應延遲, 以維持最大所需的記錄速率 (每秒 MB/秒)。As log records are generated, each operation is evaluated and assessed for whether it should be delayed in order to maintain a maximum desired log rate (MB/s per second). 當記錄檔記錄排清至儲存體時, 不會新增延遲, 而是在產生記錄速率時套用記錄速率治理。The delays are not added when the log records are flushed to storage, rather log rate governance is applied during log rate generation itself.

在執行時間加諸的實際記錄產生速率可能也會受到意見反應機制的影響, 暫時減少允許的記錄檔速率, 讓系統能夠穩定。The actual log generation rates imposed at run time may also be influenced by feedback mechanisms, temporarily reducing the allowable log rates so the system can stabilize. 記錄檔空間管理, 避免遇到記錄空間不足的情況, 以及可用性群組複寫機制, 可以暫時降低整體系統限制。Log file space management, avoiding running into out of log space conditions and Availability Group replication mechanisms can temporarily decrease the overall system limits.

記錄速率管理員流量成形是透過下列等候類型 (在_db_wait_stats DMV 中公開) 來呈現:Log rate governor traffic shaping is surfaced via the following wait types (exposed in the sys.dm_db_wait_stats DMV):

等候類型Wait Type 注意Notes
LOG_RATE_GOVERNORLOG_RATE_GOVERNOR 資料庫限制Database limiting
POOL_LOG_RATE_GOVERNORPOOL_LOG_RATE_GOVERNOR 集區限制Pool limiting
INSTANCE_LOG_RATE_GOVERNORINSTANCE_LOG_RATE_GOVERNOR 實例層級限制Instance level limiting
HADR_THROTTLE_LOG_RATE_SEND_RECV_QUEUE_SIZEHADR_THROTTLE_LOG_RATE_SEND_RECV_QUEUE_SIZE 意見反應控制, Premium/Business Critical 中的可用性群組實體複寫未趕上Feedback control, availability group physical replication in Premium/Business Critical not keeping up
HADR_THROTTLE_LOG_RATE_LOG_SIZEHADR_THROTTLE_LOG_RATE_LOG_SIZE 意見反應控制, 限制速率以避免發生記錄空間不足的狀況Feedback control, limiting rates to avoid an out of log space condition

當遇到阻礙所需擴充性的記錄速率限制時, 請考慮下列選項:When encountering a log rate limit that is hampering desired scalability, consider the following options:

  • 相應增加至較大的層級, 以取得最大 96 MB/秒的記錄速率。Scale up to a larger tier in order to get the maximum 96 MB/s log rate.
  • 如果載入的資料是暫時性的, 也就是在 ETL 進程中暫存資料, 則可以將其載入至 tempdb (這是最低限度記錄)。If data being loaded is transient, i.e. staging data in an ETL process, it can be loaded into tempdb (which is minimally logged).
  • 針對分析案例, 載入叢集資料行存放區涵蓋的資料表。For analytic scenarios, load into a clustered columnstore covered table. 這會減少因為壓縮而需要的記錄速率。This reduces the required log rate due to compression. 這項技術會增加 CPU 使用率, 而且僅適用于從叢集資料行存放區索引獲益的資料集。This technique does increase CPU utilization and is only applicable to data sets that benefit from clustered columnstore indexes.

後續步驟Next steps