使用 Azure SQL 資料庫中的記憶體內部技術將效能最佳化

  • 發行項

適用於： Azure SQL 資料庫

記憶體內部技術可讓您改善應用程式的效能，還能降低您的資料庫成本。

何時使用記憶體內部技術

您可以藉由使用記憶體內部技術，來達成各種工作負載的效能改進：

  • 交易式 (線上交易處理 (OLTP))，其中大多數要求會讀取或更新較小的資料集，例如建立/讀取/更新/刪除 (CRUD) 作業。
  • 分析 (線上分析處理 (OLAP))，其中大多數查詢基於報告目的都有複雜的計算，並且會定期排程執行負載 (或大量載入) 作業和/或將資料變更寫入現有資料表的流程。 OLAP 工作負載通常會從 OLTP 工作負載定期更新。
  • 混合式 (混合式交易/分析處理 (HTAP))，其中會在相同資料集上執行 OLTP 和 OLAP 查詢。

記憶體內部技術可以藉由保留應該處理到記憶體的資料、使用查詢的原生編譯，或進階處理 (例如基礎硬體上可用的批次處理和 SIMD 指令)，來改善這些工作負載的效能。

概觀

Azure SQL 資料庫支援下列記憶體內部技術：

  • 記憶體內部 OLTP 會增加每秒的交易數目，並減少交易處理的延遲。 受益於記憶體內部 OLTP 的案例包括：高輸送量的交易處理 (例如交易和遊戲)、來自事件或 IoT 裝置的資料擷取、快取、資料載入，以及暫存資料表和資料表變數案例。
  • 叢集資料行存放區索引可減少儲存體磁碟使用量 (最多 10 倍) 並提升報告和分析查詢的效能。 您可以將它與資料超市中的事實資料表搭配使用，以在資料庫中容納更多資料並改善效能。 此外，您可以將它與操作資料庫中的歷程記錄資料搭配使用以實現封存，並能夠查詢多達 10 倍的資料。
  • HTAP 的非叢集資料行存放區索引可協助您透過直接查詢操作資料庫來即時深入解析業務，而不需要執行昂貴的擷取、轉換和載入 (ETL) 程序並等候資料倉儲填入。 非叢集資料行存放區索引允許在 OLTP 資料庫上快速執行分析查詢，同時降低對作業工作負載的影響。
  • HTAP 的記憶體最佳化叢集資料行存放區索引可讓您執行快速交易處理，並同時對相同資料快速執行分析查詢。

資料行存放區索引和記憶體內部 OLTP 分別於 2012 年和 2014 年被引入到 SQL Server 中。 Azure SQL 資料庫、Azure SQL 受控執行個體和 SQL Server 共用記憶體內部技術的相同實作。

注意

如需詳細的逐步教學課程，使用 AdventureWorksLT 範例資料庫和 ostress.exe 示範記憶體內部 OLTP 技術的效能優勢，請參閱 Azure SQL 資料庫中的記憶體內部範例 (部分機器翻譯)。

記憶體內部技術的優點

由於查詢和交易處理更有效率，記憶體內部技術也能協助您降低成本。 您通常不需要升級資料庫的定價層，便可達到效能提升。 在某些情況下，您甚至可以降低定價層，同時仍會看到記憶體內部技術的效能改進。

透過使用記憶體內部 OLTP，仲裁商務解決方案能夠將其工作負載加倍，同時將 DTU 改善 70%。 如需詳細資訊，請參閱 Azure SQL 資料庫中的記憶體內部 OLTP。

注意

Azure SQL 資料庫進階版 (DTU) 和業務關鍵 (虛擬核心) 服務層級提供了記憶體內部 OLTP。 超大規模資料庫服務層級支援記憶體內部 OLTP 物件的子集。 如需詳細資訊，請參閱超大規模資料庫限制。

除了基本層之外，所有服務層級都可以使用資料行存放區索引，以及服務物件低於 S3 時的標準層。 如需更多資訊，請參閱變更包含資料行存放區索引的資料庫服務層級

本文說明 Azure SQL 資料庫特有的記憶體內部 OLTP 和資料行存放區索引層面，也包含協助您了解下列內容的範例：

  • 這些技術對儲存體和資料大小限制的影響。
  • 如何管理不同定價層之間使用這些技術的資料庫移動。
  • 記憶體內部 OLTP 以及資料行存放區索引的描述用法。

如需 SQL Server 中記憶體內部技術的詳細資訊，請參閱：

記憶體內部 OLTP

記憶體內部 OLTP 技術藉由將所有資料保留在記憶體中，提供極快速的資料存取作業。 它也會使用特製化索引、查詢的原生編譯，以及無閂鎖資料存取，來改善 OLTP 工作負載的效能。 有兩種方式可以組織記憶體內部 OLTP 資料：

  • 記憶體最佳化的資料列存放區格式，其中每個資料列都是個別的記憶體物件。 這是針對高效能 OLTP 工作負載最佳化的傳統記憶體內部 OLTP 格式。 可以按記憶體最佳化資料列存放區格式使用兩種類型的經記憶體最佳化的資料表：

    • 持久性資料表 (SCHEMA_AND_DATA)，其中在伺服器重新開機後會保留放置於記憶體中的資料列。 這種類型的資料表的行為類似於具有記憶體內部最佳化之額外優點的傳統資料列存放區資料表。
    • 非持久性資料表 (SCHEMA_ONLY)，其中的資料列在重新開機後不會保留。 這種類型的資料表適用於暫時性資料 (例如，取代暫存資料表)，或您需要先快速載入資料再將其移至某些持久性資料表的資料表 (因此稱為「暫存資料表」)。

  • 記憶體最佳化資料行存放區格式，其中的資料會以單欄式格式組織。 此結構適用於 HTAP 案例，其中，您必須在執行 OLTP 工作負載的相同資料結構上執行分析查詢。

注意

記憶體內部 OLTP 技術是針對可完全位於記憶體中的資料結構所設計。 由於記憶體內部資料無法卸載至磁碟，請確定您使用的資料庫具有足夠的記憶體。 如需詳細資訊，請參閱記憶體內部 OLTP 的資料大小和儲存體上限。

記憶體內部 OLTP 的資料大小和儲存體上限

記憶體內部 OLTP 包括經記憶體最佳化的資料表，該資料表用於儲存使用者資料。 這些資料表必須納入記憶體中。 每個服務物件記憶體最佳化資料表的記憶體配額或上限，稱為 記憶體內部 OLTP 儲存體。

每個支援的單一資料庫服務物件和每個彈性集區服務物件都包含一定數量的記憶體內部 OLTP 儲存體：

下列項目計入記憶體內部 OLTP 儲存體上限：

  • 經記憶體最佳化的資料表中的作用中使用者資料列和資料表變數。 舊的資料列版本不會計入上限。
  • 經記憶體最佳化的資料表上的索引。
  • ALTER TABLE 作業的作業額外負荷。

如果您達到上限，您會收到配額不足的錯誤，且無法再插入或更新資料。 若要緩和此錯誤，請刪除資料或增加資料庫或彈性集區的服務物件。

如需監視記憶體內部 OLTP 儲存體使用率和設定即將達到上限的警示的詳細資料，請參閱監視記憶體內部儲存體。

關於彈性集區

使用彈性集區時，記憶體內部 OLTP 儲存體會跨集區中的所有資料庫共用。 因此，一個資料庫中的使用方式可能會影響其他資料庫。 此問題的兩個風險降低措施如下：

  • 針對低於整個集區之 eDTU 或虛擬核心計數的資料庫，設定 Max eDTU 或 Max vCore。 此最大值也會依比例限制集區中任何資料庫中的記憶體內部 OLTP 儲存體使用率。
  • 設定大於 0 的 Min eDTU 或 Min vCore。 此下限保證集區中的每個資料庫都有對應至所設定 Min eDTU 或 Min vCore 的可用記憶體內部 OLTP 儲存體數量。

變更使用記憶體內部 OLTP 技術之資料庫的服務層級

Azure SQL 資料庫一般用途、標準或基本服務層級不支援記憶體內部 OLTP。 因此，無法將具有任何記憶體內部 OLTP 物件的資料庫調整至這其中一層。 如果您想要將資料庫調整為下列其中一個服務層級，請移除所有記憶體最佳化資料表和資料表類型，以及所有原生編譯的 T-SQL 模組，或將其轉換成以磁碟為基礎的物件和一般 T-SQL 模組。

當您縮小業務關鍵或進階資料庫時，經記憶體最佳化資料表中的資料必須符合資料庫或彈性集區目的地服務物件中可用的記憶體內部 OLTP 儲存體。 如果您嘗試縮小資料庫或彈性集區，或將資料庫移至彈性集區，如果沒有足夠可用記憶體內部 OLTP 儲存體的目的地，則作業會失敗。

判斷記憶體內部 OLTP 物件是否存在

有一種程式設計方式可探索指定的資料庫是否支援記憶體內部 OLTP。 您可以執行下列 Transact-SQL 查詢：

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported');

如果查詢傳回 1，則此資料庫中支援記憶體內部 OLTP。

下列查詢會識別在將資料庫調整為超大規模、一般用途、標準或基本服務層級之前，所有需要移除的物件：

SELECT * FROM sys.tables WHERE is_memory_optimized = 1;
SELECT * FROM sys.table_types WHERE is_memory_optimized = 1;
SELECT * FROM sys.sql_modules WHERE uses_native_compilation = 1;

記憶體內部資料行存放區

記憶體內部資料行存放區技術可讓您在資料表中儲存和查詢大量資料。 資料行存放區技術使用以資料行為基礎的資料儲存體格式和批次查詢處理，相較於傳統的資料列導向儲存體，最多可在您的 OLAP 工作負載中達到 10 倍的查詢效能改善。 相較於未壓縮的資料大小，您也可以將資料壓縮提升高達 10 倍。

有兩種類型的資料行存放區索引可用來組織資料：

  • 叢集資料行存放區，以單欄式格式組織資料表中所有資料。 在此類型的索引中，資料表中的所有資料列都會以單欄式格式放置，該格式會高度壓縮資料，並可讓您在資料表上執行快速分析查詢和報表。 視資料的性質而定，您的資料大小可能會減少 10-100 倍。 叢集式資料行存放區索引也可讓您快速擷取大量資料 (大量載入)，因為大於 10 萬個資料列的大型批次會先壓縮，再儲存到磁碟。 此類型的索引是傳統資料倉儲案例的不錯選擇。
  • 非叢集資料行存放區，其中資料會儲存在傳統資料列存放區資料表中，而且資料行存放區格式中有用於分析查詢的額外索引。 此類型的索引可啟用混合式交易分析處理 (HTAP)：能夠在交易式工作負載上執行高速即時分析。 OLTP 查詢會在資料列存放區資料表上執行，該資料表已針對存取一小組資料列進行最佳化，而 OLAP 查詢則會在資料行存放區索引上執行，該索引比較適合用於掃描和分析。 查詢最佳化工具會根據查詢動態選擇資料列存放區或資料行存放區格式。 非叢集式資料行存放區索引不會減少資料的大小，因為原始資料集會保留在原始資料列存放區資料表中，而不會有任何變更。 不過，其他資料行存放區索引的大小應該比對等 B 型樹狀結構索引小許多層數量級。

注意

記憶體內部資料行存放區技術只會保留記憶體中處理所需的資料，而無法納入記憶體中的資料則會儲存在磁碟上。 因此，資料行存放區結構中的資料量可能會超過可用記憶體數量。

資料行存放區索引的資料大小和儲存體

資料行存放區索引不需要完全納入記憶體中。 因此，索引大小的唯一上限是整體資料庫大小上限，記載於以 DTU 為基礎的購買模型和以虛擬核心為基礎的購買模型文章中。

當您使用叢集資料行存放區索引時，會針對基底資料表存儲體使用單欄式壓縮。 此壓縮可大幅減少使用者資料的儲存體磁碟使用量，這表示您可以在資料庫中容納更多資料。 使用單欄式封存壓縮可以進一步增加壓縮。 您可以達成的壓縮量取決於資料的本質，但 10 倍的壓縮並不罕見。

例如，如果您的資料庫大小上限為 1 TB，而且使用資料行存放區索引達到了 10 倍壓縮，則資料庫中總共可以容納 10 TB 的使用者資料。

當您使用非叢集資料行存放區索引時，基底資料表仍會以傳統的資料列存放區格式儲存。 因此，儲存體節省不如叢集資料行存放區索引那麼顯著。 不過，如果您要以單一資料行存放區索引取代多個傳統的非叢集索引，則仍然可以在資料表的儲存體磁碟使用量中看到整體節省。 您也可以針對基底資料表使用資料列存放區資料壓縮。

變更包含資料行存放區索引的資料庫服務層級

如果您使用 DTU 購買模型，且資料庫包含資料行存放區索引，如果您的資料庫縮放為 S3 服務目標，您的應用程式可能會停止運作。 只有在超大規模、商務關鍵和進階版服務層級，以及使用S3和更新版本時，標準服務層級才支援資料行存放區索引。 基本服務層級不支持資料行存放區索引。 當您將資料庫縮放至不支援的服務層級或服務目標時，資料行存放區索引會變成離線。 當您執行資料操作語言 (DML) 陳述式時，系統會維護索引子，但永遠不會使用索引子。 如果您稍後升級回支援的服務層級或服務目標，資料行存放區索引會立即準備好供再次使用。

如果您有叢集資料行存放區索引，如果資料庫調整為不支援的服務層級或服務目標，整個資料表就會變成離線。 卸除所有叢集資料行存放區索引，並將其取代為資料列存放區叢集索引或堆積，再進行縮放操作。

相關內容