使用記憶體最佳化加快暫存資料表與資料表變數的速度

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

如果您使用臨時表、數據表變數或數據表值參數，請考慮轉換它們以使用記憶體優化數據表和數據表變數來改善效能。對程式碼所做的變更通常很少。

本文章說明：

有利於轉換成記憶體內部的案例。
實作轉換成記憶體內部的技術性步驟。
轉換成記憶體內部前的必要條件。
強調記憶體最佳化之效能優點的程式碼範例

A。記憶體最佳化資料表變數的基本概念

記憶體最佳化資料表變數使用記憶體最佳化資料表所使用的相同記憶體最佳化演算法和資料結構，來提供最佳效率。當資料表變數從原生編譯模組內部存取時，其效率最高。

記憶體最佳化資料表變數：

只會儲存在記憶體中，在磁碟上沒有任何元件。
不需要進行 IO 活動。
不tempdb進行使用或爭用。
可傳入預存程序作為資料表值參數 (TVP)。
至少必須有一個雜湊或非叢集索引。
- 若是雜湊索引，值區計數在理想情況下應該是預期唯一索引鍵數目的 1-2 倍，不過高估值區計數通常沒什麼問題 (最多 10 倍)。如需詳細資訊，請參閱記憶體最佳化資料表的索引。

物件類型

記憶體內部 OLTP 提供可用於記憶體最佳化暫存資料表和資料表變數的下列物件：

經記憶體最佳化的資料表
- DURABILITY = SCHEMA_ONLY
經記憶體最佳化的資料表變數
- 必須分兩個步驟 (而非內嵌) 宣告：
  - CREATE TYPE my_type AS TABLE ...; ，則
  - DECLARE @mytablevariable my_type;。

B. 案例：取代全域臨時資料表

使用經記憶體最佳化的 SCHEMA_ONLY 資料表取代全域暫存資料表相當簡單。最大的變更是在部署階段 (而非執行階段) 建立資料表。因為編譯時間最佳化，所以建立經記憶體最佳化的資料表會花費比建立傳統資料表更長的時間。在在線工作負載中建立和卸除記憶體優化數據表會影響工作負載的效能，以及 Always On 可用性群組次要和資料庫復原的重做效能。

假設您有下列全域暫存資料表。

CREATE TABLE ##tempGlobalB
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000)
);

請考慮使用 DURABILITY = SCHEMA_ONLY 的下列記憶體最佳化資料表，來取代全域暫存資料表。

CREATE TABLE dbo.soGlobalB
(
    Column1 INT NOT NULL INDEX ix1 NONCLUSTERED,
    Column2 NVARCHAR (4000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

步驟

從全域暫存轉換成 SCHEMA_ONLY 的步驟如下：

建立dbo.soGlobalB數據表，就如同您建立任何傳統的磁碟數據表一樣，一次完成。
從您的 Transact-SQL 中 (T-SQL)，移除 ##tempGlobalB 資料表的建立。請務必在部署時間建立記憶體優化數據表，而不是在運行時間建立記憶體優化數據表，以避免數據表建立帶來的編譯額外負荷。
在您的 T-SQL 中，將所有提及 ##tempGlobalB 取代為 dbo.soGlobalB。

C. 案例：取代會話臨時表

取代工作階段暫存資料表的準備工作，需要比先前的全域暫存資料表案例更多的 T-SQL。很高興額外的 T-SQL 並不表示完成轉換所需的更多工作。

如同全域暫存資料表案例，最大的變更是在部署階段 (而非執行階段) 建立資料表，以避免編譯額外負荷。

假設您有下列工作階段暫存資料表。

CREATE TABLE #tempSessionC
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000)
);

首先，建立下表值函式以篩選 @@spid。函數可用於所有您從會話臨時表轉換過來的 "SCHEMA_ONLY" 資料表。

CREATE FUNCTION dbo.fn_SpidFilter
(@SpidFilter SMALLINT)
RETURNS TABLE
WITH SCHEMABINDING, NATIVE_COMPILATION
AS
RETURN
    SELECT 1 AS fn_SpidFilter
    WHERE @SpidFilter = @@spid

第二步，建立 SCHEMA_ONLY 資料表，以及資料表上的安全性原則。

每個記憶體優化數據表都必須至少有一個索引。

針對資料表 dbo.soSessionC，如果我們計算出正確的 BUCKET_COUNT，雜湊索引可能更適合。但針對本範例，我們將簡化為非叢集索引。

CREATE TABLE dbo.soSessionC
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000) NULL,
    SpidFilter SMALLINT DEFAULT (@@spid) NOT NULL,
    CONSTRAINT CHK_soSessionC_SpidFilter CHECK (SpidFilter = @@spid),
    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter)
    -- INDEX ix_SpidFilter HASH
    --    (SpidFilter) WITH (BUCKET_COUNT = 64),
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

CREATE SECURITY POLICY dbo.soSessionC_SpidFilter_Policy
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter) ON dbo.soSessionC
    WITH (STATE = ON);
GO

第三步，在您的一般 T-SQL 程式碼中：

將 Transact-SQL 陳述式中之暫存資料表的所有參考都變更為新的經記憶體最佳化的資料表︰
- 舊：#tempSessionC
- 新：dbo.soSessionC
將程式代碼中的 CREATE TABLE #tempSessionC 語句替換為 DELETE FROM dbo.soSessionC，以確保當前會話的數據表內容不會被使用相同 "session_id" 的上一個會話公開。請務必在部署時間建立記憶體優化數據表，而不是在運行時間建立記憶體優化數據表，以避免數據表建立帶來的編譯額外負荷。
從您的程式代碼中移除DROP TABLE #tempSessionC語句。您可以選擇性地插入 DELETE FROM dbo.soSessionC 語句，以防記憶體大小是潛在的問題。

D. 案例：資料表變數可設為 MEMORY_OPTIMIZED=ON

傳統的數據表變數代表資料庫中的 tempdb 數據表。為了加快效能，您可以將數據表變數記憶體優化。

以下是傳統數據表變數的 T-SQL。其範圍結束於批次或工作階段結束時。

DECLARE @tvTableD TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR (10));

將內嵌轉換成明確

上述語法適用於建立資料表變數 inline。內嵌語法不支援記憶體優化。因此請將 TYPE 的內嵌語法轉換成明確語法。

範圍： 即使伺服器關閉並重新啟動，第一個以 go 分隔的批次所建立的 TYPE 定義仍會持續存在。但在第一個 go 分隔符號之後，宣告的資料表 @tvTableC 只會保存至達到下一個 go 且批次結束時。

CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR (10));
GO

SET NOCOUNT ON;

DECLARE @tvTableD AS dbo.typeTableD;
INSERT INTO @tvTableD (Column1) VALUES (1), (2);
SELECT * FROM @tvTableD;
GO

D.2 將明確磁碟轉換成記憶體最佳化

記憶體優化的表格變數不在tempdb中。記憶體最佳化會導致速度加快，通常會加快 10 倍以上。

只需要一個步驟，就能轉換成記憶體最佳化。增強明確的 TYPE 建立，如下所示，這會新增：

一個索引。同樣地，每個記憶體最佳化資料表至少必須有一個索引。
MEMORY_OPTIMIZED = ON。

CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR (10))
    WITH (MEMORY_OPTIMIZED = ON);

大功告成。

E. SQL Server 的必要條件 FILEGROUP

在Microsoft SQL Server 上，若要使用記憶體優化功能，您的資料庫必須具有以 MEMORY_OPTIMIZED_DATA宣告的 FILEGROUP。

Azure SQL Database 不需要建立此 FILEGROUP。

必要條件：FILEGROUP 的下列 Transact-SQL 程式碼是本文稍後章節中很長之 T-SQL 程式碼範例的必要條件。

您必須使用 SSMS.exe 或可提交 T-SQL 的其他工具。
將範例 FILEGROUP T-SQL 程式碼貼到 SSMS 中。
編輯 T-SQL，將其特定名稱和目錄路徑變更為您慣用的名稱和目錄路徑。

除了最後一個目錄不能預先存在之外，FILENAME 值中的所有目錄都必須預先存在。

執行您所編輯的 T-SQL。

即使您在下一個子區段重複調整並重新執行速度比較 T-SQL，也不需要多次執行 FILEGROUP T-SQL。

ALTER DATABASE InMemTest2
    ADD FILEGROUP FgMemOptim3 CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE InMemTest2
    ADD FILE (NAME = N'FileMemOptim3a', FILENAME = N'C:\DATA\FileMemOptim3a'
    -- C:\DATA\ preexisted.
) TO FILEGROUP FgMemOptim3;
GO

下列指令碼會為您建立檔案群組，並進行建議之資料庫的設定︰ enable-in-memory-oltp.sql

如需 FILE 和 FILEGROUP 之 ALTER DATABASE ... ADD 的詳細資訊，請參閱：

F. 快速測試以證明速度提升

本節提供 Transact-SQL 程式碼，您可以執行以測試並比較使用記憶體最佳化資料表變數進行 INSERT-DELETE 所提升的速度。此程式碼是由兩個幾乎相同的部分所組成，不同之處在於前半部分的資料表類型為記憶體最佳化。

比較測試會持續約 7 秒。若要執行範例：

必要條件：您必須已執行上一節中的 FILEGROUP T-SQL。
執行下列 T-SQL INSERT-DELETE 指令碼。

GO 5001請注意，這個語句會重新提交 T-SQL 5,001 次。您可以調整次數，然後重新執行。

在 Azure SQL Database 中執行指令碼時，請務必從相同區域的 VM 執行。

PRINT ' ';
PRINT '---- Next, memory-optimized, faster. ----';
DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

CREATE TYPE dbo.typeTableC_mem -- !!  Memory-optimized.
AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR(10)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _mem.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_mem;-- !!

INSERT INTO @tvTableC (Column1) VALUES (1), (2);
INSERT INTO @tvTableC (Column1) VALUES (3), (4);

DELETE @tvTableC;GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _mem.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

---- End memory-optimized.
-------------------------------------------------
---- Start traditional on-disk.
PRINT ' ';
PRINT '---- Next, tempdb based, slower. ----';

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

CREATE TYPE dbo.typeTableC_tempdb -- !!  Traditional tempdb.
AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR(10)
);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _tempdb.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_tempdb;-- !!

INSERT INTO @tvTableC (Column1) VALUES (1), (2);
INSERT INTO @tvTableC (Column1) VALUES (3), (4);

DELETE @tvTableC;
GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _tempdb.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

PRINT '---- Tests done. ----';
GO

結果集如下所示。

---- Next, memory-optimized, faster. ----
2016-04-20 00:26:58.033  = Begin time, _mem.
Beginning execution loop
Batch execution completed 5001 times.
2016-04-20 00:26:58.733  = End time, _mem.

---- Next, tempdb based, slower. ----
2016-04-20 00:26:58.750  = Begin time, _tempdb.
Beginning execution loop
Batch execution completed 5001 times.
2016-04-20 00:27:05.440  = End time, _tempdb.
---- Tests done. ----

G. 預測使用中記憶體耗用量

您可以透過下列資源，了解如何預測記憶體最佳化資料表的使用中記憶體需求：

針對較大的資料表變數，非叢集索引會使用比記憶體最佳化「資料表」更多的記憶體。資料列計數和索引鍵愈大，所增加的差異愈多。

如果每次存取只能使用一個索引鍵值來存取記憶體資料表變數，則雜湊索引可能比非叢集索引更適合。然而，如果您無法估算出適當的 BUCKET_COUNT，NONCLUSTERED 索引是一個很好的次要選擇。

意見反應

此頁面對您有幫助嗎？

Last updated on 2025-05-31