InMemory：在記憶體中建立暫存資料表和表變數

標籤：間接   利用   uri   rman   sql   binding   操作   with   提高   

在Disk-Base資料庫中，如果系統頻繁地建立和更新暫存資料表，大量的IO操作集中在tempdb中，tempdb很可能成為系統效能的瓶頸。在SQL Server 2016的記憶體（Memory-Optimized）資料庫中，如果考慮使用記憶體最佳化結構來儲存暫存資料表，表變數，表值參數的資料，那麼將完全消除IO操作的負載消耗，發揮大記憶體的優勢，大幅提高資料庫的效能。

在SQL Server 2016中，能夠直接建立記憶體最佳化的表類型，表變數和表值參數的資料只儲存在記憶體中；不能直接在記憶體中建立暫存資料表，但是，SQL Server提供一個變通方法（Workaround），通過行級安全RLS（Row-Level-Security）控制，指定只有當前Session才能訪問特定的資料，將記憶體最佳化錶轉換為Session層級的暫存資料表，間接實現暫存資料表的局部性和自動清空特性。

一，記憶體最佳化表類型（Memory-Optimized Table Type）

記憶體最佳化表類型定義的表變數，表值參數能夠大幅提高效率（efficiency），有4個顯著的特點：

• 資料僅儲存在記憶體中，在讀寫資料時，不會產生任何的IO消耗，消除了tempdb的競爭和利用率；
• 必須有一個索引，Hash 或 Nonclustered 都行；每一個記憶體最佳化表必須建立一個索引；
• 只需要指定啟用記憶體最佳化：MEMORY_OPTIMIZED = ON，只持久化Schema；
• 必須先建立表類型，後建立表值變數；

1，建立記憶體最佳化表類型

CREATE TYPE dbo.TypeTable  AS TABLE  (  Column1  INT NOT NULL,  Column2  VARCHAR(10) NOT NULL,INDEX idxName　NONCLUSTERED(Column1))  WITH(MEMORY_OPTIMIZED = ON); 

2，建立記憶體最佳化表變數

declare @Table dbo.TypeTable 

二，建立“臨時記憶體最佳化表”

在Disk-Base資料庫中，局部暫存資料表#temp的範圍是session，建立在tempdb中，一旦session生命週期結束，系統自動回收其儲存空間。在SQL Server 2016中，不能直接在tempdb中建立記憶體最佳化表。要使用臨時記憶體最佳化表，有一個變通的方法，在DB中建立記憶體最佳化表，通過Row-Level-Security控制Session能夠訪問的資料行，間接實現Session層級的暫存資料表。

Step1，建立記憶體最佳化表，只持久化Table Schema

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

Step2，建立RLS，控制使用者只能訪問當前Session的資料

CREATE FUNCTION dbo.fn_SpidFilter(@SpidFilter smallint)  RETURNS TABLE  WITH SCHEMABINDING , NATIVE_COMPILATION  AS  RETURN      SELECT 1 AS fn_SpidFilter      WHERE @SpidFilter = @@spid;  goCREATE SECURITY POLICY dbo.soSessionC_SpidFilter_Policy  ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter)  ON dbo.SessionTempTable  WITH (STATE = ON);  go 

Step3，使用記憶體最佳化暫存資料表

• 表名替換：使用 dbo.Temp 代替 #Temp；
• 不能建立和刪除暫存資料表：
  • 移除代碼“create table #temp”，使用“delete from dbo.Temp”子句取代，將舊資料清空；
  • 移除代碼“drop table #temp”，建議使用 “delete from dbo.Temp” 子句，在當前Session結束前將當前Session產生的資料清空，節省記憶體空間；

雖然暫存資料表的使用和管理有點麻煩，但是，這點麻煩和大幅的效能提升來比，微不足道，建議使用記憶體最佳化表來代替暫存資料表，體驗飛一般的速度。

 

參考文檔：

Faster temp table and table variable by using memory optimization

Improving temp table and table variable performance using memory optimization

CREATE TYPE (Transact-SQL)

Indexes for Memory-Optimized Tables

InMemory：在記憶體中建立暫存資料表和表變數