閱讀英文版本

分享方式：

記憶體內部 OLTP 的概觀和使用案例

  • 文章

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

記憶體內部 OLTP 是 SQL Server 和 SQL Database 中提供的首要技術，可提高交易處理、資料擷取、資料載入及暫時性資料案例的效能。 本文包含這項技術的概觀，並將概述記憶體內部 OLTP 的使用案例。 使用此資訊來判斷記憶體內部 OLTP 是否適合您的應用程式。 本文隨附的範例會顯示記憶體內部 OLTP 物件、效能示範的參考，以及您可在後續步驟中使用之資源的參考。

記憶體內部 OLTP 概觀

記憶體內部 OLTP 可以為正確的工作負載提供絕佳的效能提升。 雖然有客戶在某些案例中見識到效能提升最多可達 30 倍，但您看到的提升程度會取決於工作負載。

現在，這個效能提升來自何處？ 在本質上，記憶體內部 OLTP 是藉由更有效率的存取資料及執行交易，以及移除並行執行交易之間的鎖定和閂鎖競爭，來提升交易處理的效能。 記憶體內部 OLTP 不是快速的，因為它是記憶體內部;因為記憶體內部數據的優化，所以速度很快。 資料儲存體、存取和處理演算法均已重新設計，可充分利用關於記憶體內部與高度並行運算的最新增強功能。

現在，只是因為資料存留於記憶體內部，不代表您會在發生失敗時遺失資料。 根據預設，所有交易都是完全持久的，這表示您在 SQL Server 中的任何其他數據表都有相同的持久性保證：在交易認可期間，所有變更都會寫入磁碟上的事務歷史記錄。 如果在交易認可之後的任何時間發生失敗，當資料庫重新上線時，您的資料還是會在原處。 此外，記憶體內部 OLTP 可以與 SQL Server 的所有高可用性和災害復原功能搭配運作，例如可用性群組、容錯移轉叢集執行個體、備份/還原等。

若要在資料庫中使用記憶體內部 OLTP，您可以使用一或多個下列類型的物件：

  • 記憶體最佳化資料表 是用於儲存使用者資料。 您可以在建立時將資料表宣告為記憶體最佳化。
  • 非持久性資料表 是用於暫時性資料，可能是快取或中繼結果集 (取代傳統的暫存資料表)。 非持久性資料表是使用 DURABILITY=SCHEMA_ONLY 宣告的記憶體最佳化資料表，這表示對於這些資料表的變更不會造成任何 IO。 這可避免在無須顧慮持久性的情況下耗用了記錄 IO 資源。
  • 記憶體優化數據表類型 用於預存程式中的數據表值參數 （TVP） 和中繼結果集。 記憶體優化數據表類型可用來取代傳統數據表類型。 使用記憶體最佳化資料表類型宣告的資料表變數和 TVP，會繼承非持久性記憶體最佳化資料表的優點︰有效率的資料存取且沒有任何 IO。
  • 原生編譯的 T-SQL 模組 是用來藉由減少 CPU 循環處理作業，進一步減少個別交易所需花費的時間。 您可以在建立時宣告要以原生方式編譯的 Transact-SQL 模組。 目前，下列 T-SQL 模組可以原生方式編譯︰預存程序、觸發程序和純量使用者定義函式。

記憶體內部 OLTP 內建於 SQL Server 和 SQL Database 中。 因為這些物件的行為與其傳統對應項目相類似，您通常只需對資料庫和應用程式進行最少變更，就能獲得效能效益。 此外，您可以在相同資料庫中同時具有記憶體最佳化資料表和傳統以磁碟為基礎的資料表，並跨這兩個資料表執行查詢。 請參閱本文稍後每個類型的 Transact-SQL 腳本範例。

記憶體內部 OLTP 的使用案例

記憶體內部 OLTP 並非神奇的快速按鈕，而且不適用於所有工作負載。 例如，如果大部分的查詢正在執行大範圍資料的彙總，則記憶體最佳化資料表不會縮減您的 CPU 使用量。 資料行存放區索引可協助此案例。

注意

已知問題：對於具有經記憶體最佳化的資料表之資料庫，執行具有不復原功能的交易記錄備份，並稍後執行具有復原功能的交易記錄還原，此種做法可能會導致資料庫還原程序沒有回應。 此問題也會影響記錄傳送功能。 若要解決此問題，可以先重新啟動 SQL Server 執行個體再開始還原程序。

以下是我們曾見到客戶成功使用記憶體內部 OLTP 的案例和應用程式模式清單。

高輸送量且低延遲的交易處理

這是我們建置記憶體內部 OLTP 的核心案例︰為個別交易提供一致的低延遲，以支援大量交易。

常見的工作負載案例包括︰金融工具、體育博彩、行動遊戲及廣告投放的交易。 另一個常見的模式是經常讀取和/或更新的「目錄」。 有個範例是您具有大型檔案，每個檔案均分散於多個叢集節點上，而您會在記憶體最佳化資料表中，將每個檔案的每個分區位置編入目錄。

實作考量

針對您的核心交易資料表 (亦即，含有效能最嚴重不足之交易的資料表)，使用記憶體最佳化資料表。 使用原生編譯的預存程序，以最佳化方式執行與商務交易相關聯的邏輯。 您可以向下推送到預存程序的邏輯越多，您可從記憶體內部 OLTP 中看見的效益就越多。

開始使用現有的應用程式：

  1. 使用交易效能分析報表，以識別您想要移轉的物件。
  2. 使用記憶體最佳化建議程式和原生編譯建議程式來協助移轉。

擷取資料，包括 IoT (Internet of Things)

記憶體內部 OLTP 很適合一次從許多不同來源擷取大量資料。 而且，相較於其他目的地，將資料擷取到 SQL Server 資料庫通常較有利，因為 SQL 會讓資料的執行查詢速度變快，以便即時進行深入解析。

常見的應用程式模式為：

  • 擷取感應器讀數和事件，允許通知以及歷史資料分析。
  • 管理批次更新 (即使是從多個來源也一樣)，同時降低並行讀取工作負載的影響。

實作考量

使用記憶體最佳化資料表進行資料擷取。 如果擷取包含大部分的插入 (而非更新)，而且顧慮到資料的記憶體內部 OLTP 資料儲存體使用量，則可

  • 使用工作，利用進行 INSERT INTO <disk-based table> SELECT FROM <memory-optimized table> 的工作，定期將資料批次卸載到含有叢集資料行存放區索引，以磁碟為基礎的資料表；或
  • 使用暫時的記憶體最佳化資料表管理歷程記錄資料 - 在此模式中，歷程記錄資料會存留於磁碟上，而資料移動是由系統所管理。

SQL Server 範例儲存機制包含智慧型格線應用程式，其會使用暫時的記憶體最佳化資料表、記憶體最佳化資料表類型及原生編譯的預存程序來加速資料擷取，同時管理感應器資料的記憶體內部 OLTP 儲存體使用量：

快取和工作階段狀態

記憶體內部 OLTP 技術讓 SQL Server 或 Azure SQL 資料庫中的資料庫引擎成為十分吸引人的工作階段狀態維護 (例如，ASP.NET 應用程式) 和快取平台。

ASP.NET 工作階段狀態是成功的記憶體內部 OLTP 使用案例。 有一位客戶利用 SQL Server 達成了每秒 120 萬個要求數。 在此同時，他們已開始針對企業內所有中層應用程式的快取需求，使用所有記憶體內部 OLTP。 詳細資料：bwin 如何使用 SQL Server 2016 (13.x) 的記憶體內部 OLTP 來達到前所未有的效能和延展性

實作考量

您可以將 BLOB 儲存在 varbinary(max) 資料行中，藉以使用非持久性記憶體最佳化資料表作為簡單的索引鍵/值存放區。 或者，您可以在 SQL Server 和 SQL Database 中，使用 JSON 支援 實作半結構化的快取。 最後，您可以透過具有完整關聯式結構描述 (其中包括各種資料類型和條件約束) 的非持久性資料表來建立完整關聯的快取。

利用 GitHub 上發佈的指令碼來取代內建 SQL Server 工作階段狀態提供者所建立的物件，藉以開始使用記憶體最佳化的 ASP.NET 工作階段狀態：aspnet-session-state

客戶案例研究

tempdb 物件取代

使用非持久性資料表和記憶體最佳化資料表類型，來取代傳統的 tempdb 型結構，例如暫存資料表、資料表變數和資料表值參數 (TVP)。

相較於傳統資料表變數和 #temp 資料表，記憶體最佳化資料表變數和非持久性資料表通常會減少 CPU，並完全移除記錄檔 IO。

實作考量

若要開始使用： 使用記憶體優化改善臨時表和數據表變數效能。

客戶案例研究

ETL (擷取轉換載入)

ETL 工作流程通常包含將資料載入暫存資料表、轉換資料，然後載入最終資料表。

針對資料暫存，使用非持久性記憶體最佳化資料表。 它們會完全移除所有的 IO，並讓資料存取更具效率。

實作考量

如果您在暫存資料表上將轉換當成工作流程的一部分來執行，您可以使用原生編譯的預存程序來加速這些轉換。 如果您可以平行執行這些轉換，您就能從記憶體最佳化中取得其他擴充性效益。

範例指令碼

開始使用記憶體內部 OLTP 之前，您需要建立 MEMORY_OPTIMIZED_DATA 檔案群組。 此外，我們建議使用資料庫相容性層級 130 (或更高層級)，並將資料庫選項 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 設為 ON。

您可以在下列位置使用指令碼，在預設資料夾中建立檔案群組，並設定建議的設定：

下列範例指令碼將說明您可以在資料庫中建立的記憶體內部 OLTP 物件。

首先，請先設定記憶體內部 OLTP 的資料庫。

SQL 
-- configure recommended DB option
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

您可以建立不同持久性的資料表：

SQL 
-- memory-optimized table
CREATE TABLE dbo.table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON);
GO
-- non-durable table
CREATE TABLE dbo.temp_table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON,
      DURABILITY=SCHEMA_ONLY);
GO

您可以將資料表類型建立為記憶體內部資料表。

SQL 
-- memory-optimized table type
CREATE TYPE dbo.tt_table1 AS TABLE
( c1 INT IDENTITY,
  c2 NVARCHAR(MAX),
  is_transient BIT NOT NULL DEFAULT (0),
  INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
WITH (MEMORY_OPTIMIZED=ON);
GO

您可以建立原生編譯的預存程序。 如需詳細資訊，請參閱從資料存取應用程式呼叫原生編譯預存程序。

SQL 
-- natively compiled stored procedure
CREATE PROCEDURE dbo.usp_ingest_table1
  @table1 dbo.tt_table1 READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,
          LANGUAGE=N'us_english')

  DECLARE @i INT = 1

  WHILE @i > 0
  BEGIN
    INSERT dbo.table1
    SELECT c2
    FROM @table1
    WHERE c1 = @i AND is_transient=0

    IF @@ROWCOUNT > 0
      SET @i += 1
    ELSE
    BEGIN
      INSERT dbo.temp_table1
      SELECT c2
      FROM @table1
      WHERE c1 = @i AND is_transient=1

      IF @@ROWCOUNT > 0
        SET @i += 1
      ELSE
        SET @i = 0
    END
  END

END
GO
-- sample execution of the proc
DECLARE @table1 dbo.tt_table1;
INSERT @table1 (c2, is_transient) VALUES (N'sample durable', 0);
INSERT @table1 (c2, is_transient) VALUES (N'sample non-durable', 1);
EXECUTE dbo.usp_ingest_table1 @table1=@table1;
SELECT c1, c2 from dbo.table1;
SELECT c1, c2 from dbo.temp_table1;
GO

相關內容