In-Memory OLTP 概觀和使用案例

適用于:yesSQL Server (所有支援的版本) Yes Azure SQL Database Yes Azure SQL 受控執行個體

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

本文涵蓋SQL Server和SQL Database中的In-Memory OLTP 技術。 如需Azure SQL中記憶體內資料的特定詳細資訊,請參閱在Azure SQL Database 和 Azure SQL 受控執行個體 中使用記憶體內部技術優化效能和部落格:Azure SQL Database 中的 In-Memory OLTP

記憶體內部 OLTP 概觀

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

現在,這個效能提升來自何處? 基本上,In-Memory OLTP 可藉由讓資料存取和交易執行更有效率,以及移除同時執行交易之間的鎖定和閂鎖競爭,來改善交易處理的效能。 In-Memory OLTP 的速度並不快,因為它位於記憶體中;速度很快,因為它已針對記憶體中的資料進行優化。 資料儲存體、存取和處理演算法均已重新設計,可充分利用關於記憶體內部與高度並行運算的最新增強功能。

現在,因為資料存在於記憶體內部並不表示您在發生失敗時遺失。 根據預設,所有交易都是完全持久的,表示您具有針對 SQL Server 中任何其他資料表所取得的相同持久性保證︰在交易認可時,所有變更都會寫入磁碟上的交易記錄檔。 如果在交易認可之後隨時發生失敗,當資料庫恢復上線時,您的資料就會存在。 此外,In-Memory OLTP 適用于SQL Server的所有高可用性和災害復原功能,例如Always On可用性群組Always On容錯移轉叢集實例 (SQL Server) 、備份/還原等。

若要在資料庫中使用 In-Memory OLTP,您可以使用下列一或多個物件類型:

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

In-Memory OLTP 內建于SQL Server和SQL Database中。 由於這些物件的行為類似于其傳統對應專案,因此您通常可以取得效能優勢,同時只對資料庫和應用程式進行最少的變更。 此外,您可以在相同資料庫中同時具有記憶體最佳化資料表和傳統以磁碟為基礎的資料表,並跨這兩個資料表執行查詢。 您會找到 Transact-SQL 腳本,其中每個類型的物件都位於本文底部。

In-Memory OLTP 的使用案例

In-Memory OLTP 不是魔術快速按鈕,不適用於所有工作負載。 例如,如果大部分的查詢在大量資料上執行匯總,記憶體優化資料表不會降低 CPU 使用率。 資料行存放區索引可協助處理該案例。

以下是一份案例和應用程式模式清單,其中我們已看到客戶在 OLTP In-Memory成功。

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

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

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

實作考量

針對核心事務表使用記憶體優化資料表,也就是具有最高效能關鍵交易的資料表。 使用原生編譯的預存程序,以最佳化方式執行與商務交易相關聯的邏輯。 您可以向下推送到預存程序的邏輯越多,您可從記憶體內部 OLTP 中看見的效益就越多。

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

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

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

記憶體內部 OLTP 很適合一次從許多不同來源擷取大量資料。 相較于其他目的地,將資料內嵌至SQL Server資料庫通常很有説明,因為SQL Server快速對資料執行查詢,並可讓您取得即時見解。

常見的應用程式模式為:

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

實作考量

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

  • 使用作業,使用執行 INSERT INTO <disk-based table> SELECT FROM <memory-optimized table> 的作業,定期批次將資料卸載至具有叢集資料行存放區索引的磁片資料表;或
  • 使用暫時的記憶體最佳化資料表管理歷程記錄資料 - 在此模式中,歷程記錄資料會存留於磁碟上,而資料移動是由系統所管理。

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

快取和工作階段狀態

In-Memory OLTP 技術讓SQL Server或Azure SQL資料庫中的資料庫引擎成為維護會話狀態 (的吸引人的平臺,例如,ASP.NET 應用程式) 和快取。

ASP.NET 會話狀態是In-Memory OLTP 的成功使用案例。 有一位客戶利用 SQL Server 達成了每秒 120 萬個要求數。 在此同時,他們已開始針對企業內所有中層應用程式的快取需求,使用所有記憶體內部 OLTP。 詳細資料:bwin 如何使用 2016 SQL Server 2016 (13.x) In-Memory OLTP,以達到超前的效能和規模

實作考量

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

使用GitHub上發佈的腳本取代內建SQL Server會話狀態提供者所建立的物件,開始記憶體優化 ASP.NET會話狀態:aspnet-session-state

客戶案例研究

tempdb 物件取代

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

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

實作考量

若要開始使用,請參閱:Improving temp table and table variable performance using memory optimization (使用記憶體最佳化改善暫存資料表與資料表變數效能)。

客戶案例研究

ETL (擷取轉換載入)

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

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

實作考量

如果您在暫存資料表上將轉換當成工作流程的一部分來執行,您可以使用原生編譯的預存程序來加速這些轉換。 如果您可以平行執行這些轉換,您可以從記憶體優化獲得額外的調整優勢。

範例指令碼

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

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

下列範例腳本說明您可以在資料庫中建立In-Memory OLTP 物件。

首先,請先設定In-Memory OLTP 的資料庫。

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

您可以建立具有不同持久性的資料表:

-- 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

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

-- 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

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

-- 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

值得深入了解的資源

另請參閱

後續步驟