最佳化資料庫儲存體

  • 5 分鐘

若要最佳化資料庫儲存，您應該考慮比例填滿和 tempdb 設定。

瞭解 I/O 效能

I/O 效能對資料庫應用程式而言很重要。 Azure SQL 使你免於放置實體檔案，但有一些方法可確保取得所需的 I/O 效能。

對應用程式而言，每秒的輸入/輸出 (IOPS) 可能很重要。 請確定您已針對您的 IOPS 需求，選擇正確的服務層級和 V 核心。 如果您要移轉至 Azure，瞭解如何測量內部部署查詢的 IOPS。 如果限制 IOPS，則可能會看到長時間的 I/O 等候。 在 vCore 購買模型中，如果 IOPS 不足，您可以擴充 vCores 或升級到 Business Critical 或 Hyperscale 服務層。 針對生產工作負載，當使用 DTU 時，建議您移至進階層。

I/O 延遲是 I/O 效能的另一個重要元件。 如需加快 Azure SQL Database 的 I/O 延遲，請考慮使用業務關鍵或超大規模資料庫。 如需加快 SQL 受控執行個體的 I/O 延遲，請移至業務關鍵或增加資料庫的檔案大小或檔案數目。 改善交易記錄延遲可能會要求您使用多重陳述式交易。

檔案與檔案群組

SQL Server 專業人員通常會透過實體檔案放置的方式，使用檔案和檔案群組來改善 I/O 效能。 Azure SQL 不允許使用者將檔案放在特定的磁碟系統上。 不過，Azure SQL 對於有關速率、IOPS 和延遲的 I/O 效能具有資源承諾。 透過這種方式，使使用者不需關心實體檔案的放置位置可能會帶來優勢。

Azure SQL Database 只有一個資料庫檔案 (超大規模資料庫通常會有很多個)，而大小上限會透過 Azure 介面來設定。 沒有建立更多檔案的功能。

Azure SQL 受控執行個體支援新增資料庫檔案和設定大小，但不支援實際放置檔案。 您可以使用 SQL 受控執行個體的檔案數目和檔案大小來改善 I/O 效能。 此外，基於管理目的，SQL 受控執行個體可支援使用者定義的檔案群組。

描述比例填滿

將 1 GB 的數據插入具有兩個數據檔的 SQL Server 資料庫中時，您可能會預期每個檔案會增加大約 512 MB。 不過，情況不一定如此。 SQL Server 會根據每個檔案的大小來散發數據。 例如，如果這兩個數據檔都是 2 GB，則數據會平均散發。 但是，如果一個檔案是 10 GB，另一個是 1 GB，則大約 900 MB 會進入較大的檔案，而 100 MB 則進入較小的檔案。 此行為在任何資料庫中很常見，但在大量寫入tempdb中，不平均寫入模式可能會造成最大檔案中的瓶頸，因為它會處理更多寫入。

在 SQL Server 中設定 Tempdb

SQL Server 會在安裝期間偵測可用的 CPU 數目，並設定適當的檔案數目，最多 8 個，且大小均等。 此外，追蹤旗標 1117 和 1118 的行為會整合到資料庫引擎中，但僅適用於 tempdb。 針對 tempdb 繁重的工作負載，將 tempdb 檔案數目增加至超過八個，並與機器上的 CPU 數目相符，可能會帶來效益。

您在 SQL Server 和 Azure SQL 中使用 tempdb 的方式皆相同。 但請注意，設定 tempdb 的方式不同，包括檔案的放置、檔案的數目和大小，以及 tempdb 設定選項。

SQL Server 會針對儲存使用者定義的臨時表以外的各種工作使用tempdb。 這會用於工作資料表，可儲存中繼查詢結果、排序作業，以及用於資料列版本設定的版本存放區等用途。 由於這種廣泛的使用率，請務必將tempdb放在可用的最低延遲記憶體上，並正確地設定其數據檔。

tempdb 的資料庫檔案一律會自動儲存在本機的 SSD 磁碟機上，因此 I/O 效能不會有問題。

SQL Server 專業人員通常會使用一個以上的資料庫檔案來分割 tempdb 資料表的配置。 針對 Azure SQL Database，檔案數目會隨著虛擬核心數目進行調整（例如，兩個虛擬核心等於四個檔案），最多 16 個。 您無法透過 T-SQL 來針對 tempdb 設定檔案數目，而是藉由變更部署選項來進行設定。 tempdb 的大小上限會根據虛擬核心數目進行調整。 您可獨立於虛擬核心來使用 SQL 受控執行個體以取得 12 個檔案，。

資料庫選項 MIXED_PAGE_ALLOCATION 設定為 OFF，且 AUTOGROW_ALL_FILES 設定為 ON。 您無法進行此設定，但因為與 SQL Server 相同，這些都是建議的預設。

目前，SQL Server 2019 中可減少大量閂鎖競爭的 tempdb 中繼資料最佳化功能無法在 Azure SQL 資料庫或 Azure SQL 受控執行個體中使用。

資料庫設定

通常，您會使用 T-SQL ALTER DATABASE 和 ALTER DATABASE SCOPED CONFIGURATION 語句來設定資料庫。 Azure SQL 提供許多效能設定選項。 若要了解 SQL Server、Azure SQL Database 和 Azure SQL 受控執行個體之間的差異，請參閱 ALTER DATABASE 和 ALTER DATABASE SCOPED CONFIGURATION T-SQL 參考。

在 Azure SQL Database 中，預設恢復模式是完整復原，可確保您的資料庫可以符合 Azure 服務等級協定（SLA）。 這表示不支援批次作業的最小記錄，但允許最小記錄的 tempdb 情況除外。

MAXDOP 設定

平行處理原則的最大程度 (MAXDOP) 可能會影響個別查詢的效能。 SQL Server 和 Azure SQL 處理 MAXDOP 方式相同。 當 設定為較高的值時 MAXDOP ，每個查詢會使用更多平行線程，可能會加速查詢執行。 不過，這種增加的平行處理原則需要額外的記憶體資源，這可能會導致記憶體壓力並影響記憶體效能。 例如，將行群組壓縮到列儲存區時，平行處理需要更多記憶體，這可能會導致記憶體壓力和行群組修剪。

相反地，將 MAXDOP 設定為較低的值可能會降低記憶體壓力，讓記憶體系統更有效率地執行。 這在記憶體資源有限或記憶體需求高的環境中很重要。 藉由仔細設定 MAXDOP，您可以平衡查詢效能和記憶體效率，確保CPU和記憶體資源的最佳使用。

如同 SQL Server，您可透過下列技術設定 Azure SQL 中的 MAXDOP：

  • Azure SQL 支援 ALTER DATABASE SCOPED CONFIGURATION 設定 MAXDOP。
  • 「最大平行度」的預存程序 sp_configure 受 SQL 受控實例支援。
  • 完全支援 MAXDOP 查詢提示。
  • SQL 受控執行個體支援使用 Resource Governor 來設定 MAXDOP。