SQL Server 2016中In-Memory OLTP繼CTP3之後的新改進

標籤：

SQL Server 2016中In-Memory OLTP繼CTP3之後的新改進

轉譯自：https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/25/whats-new-for-in-memory-oltp-in-sql-server-2016-since-ctp3/

SQL Server 2016正在對 In-Memory OLTP 功能作一系列的強化，從而使該功能使用起來更加方便，效能更優。
在之前的文章中，我已經對SQL Server 2016 以及後來的CTP3版本的新增功能進行了匯總說明。
而自那時開始，我們又在原有功能的基礎上增加了一些新功能，包括空索引鍵列、LOB類型欄位和自動更新統計資訊。
以下是我們為處於CTP3和RC0版本之間的In-Memory OLTP增加的新特性。
在下文的新特性列表中，您將會看到關於LOBs和其它off-row 列、表結構修改和統計資訊改進方面的更詳細的說明。

CTP3與RC0功能層之間新增的In-Memory OLTP新特性

Transact-SQL 改進:
1、本地模組中查詢表面:
2、LOB資料類型 ：現在可以使用[varchar(max), nvarchar(max)與varbinary(max)]作為輸入參數與變數.
3、OUTPUT 子句：目前在本地編譯預存程序中， INSERT，UPDATE 與DELETE也已包含OUTPUT 子句。
4、@@SPID：這一內建功能得到本地編譯T-SQL 模組的支援，約束條件見記憶體最佳化表。
5、記憶體最佳化表支援的功能增加如下:
6、NULLable索引鍵列。現在允許在記憶體最佳化表的索引鍵中添加NULLable列。
7、Large row: 記憶體最佳化表的列可使用LOB資料類型[varchar(max), nvarchar(max與varbinary(max)]。此外，列中無LOB資料類型時，記憶體最佳化表的行大小也可超過8060位元組。詳細說明見下文。
8、記憶體最佳化表中的唯一索引。現在索引可指定為UNIQUE。
9、堆掃描: 查詢處理常式可在記憶體中直接掃描堆資料結構表中的各行。需要進行全表掃描時，這種方法比全索引掃描更有效。
10、並行掃描: 所有索引類型及基本堆表現在都支援並行掃描。可以增強分析型查詢掃描大型資料集的效能。
11、縮短更新所需停機時間: 從SQL Server 2016的較早版本更新至最新版本不再需要運行資料庫恢複。因此，資料大小不再影響升級時間。針對SQL Server 2014升級與附加/還原，資料庫需要重啟一次，所以SQL2014 資料庫升級所需的停機時間約為[資料庫恢複所需時間].
12、日誌最佳化與並行ALTER TABLE: 目前，大部分ALTER TABLE都是並行的，並最佳化寫入交易記錄。最佳化指的是唯寫入中繼資料變化。有關例外的詳細討論見下文。

統計資訊的改進:
1、現在支援自動更新統計資訊。不再需要手動更新統計資料。
2、現在支援統計資訊採樣。可以改進統計資訊收集的效能。
請注意，不支援自動重新編譯本地模組。需要使用sp_recompile進行手動重新編譯。

LOB和其他off-row 列
記憶體最佳化表與本地編譯T-SQL模組現在已經支援支援大對象(LOB)資料類型varchar(max), nvarchar(max)與varbinary(max)，且大小限制跟基於磁碟的表一樣(LOB資料類型資料不能超過2GB )。此外，即使表中無LOB資料類型列時，記憶體最佳化表的行大小也可超過8060位元組。根據表的定義，行的大小或各行資料無已耗用時間限制。當然，所有資料也需要裝入記憶體。
即使現在支援LOB類型列，但仍推薦列的大小小於8060位元組來實現最佳效能。詳細資料見下文。
下列T-SQL 指令碼可以說明具有多個non-LOB列與單個LOB列的表：

CREATE TABLE dbo.LargeTableSample(      Id   int IDENTITY PRIMARY KEY NONCLUSTERED,      C1   nvarchar(4000),      C2   nvarchar(4000),      C3   nvarchar(4000),      C4   nvarchar(4000),      Misc nvarchar(max)) WITH (MEMORY_OPTIMIZED = ON);GO

 

LOB列和其他列等無法裝入in-row的8060位元組的儲存在off-row，in-row只儲存off-row的8位元組引用。另外會有一個內部表來單獨儲存每個off-row列。
將列裝入on-row或off-row的邏輯如下所示，每次ALTER TABLE操作都須確保遵循以下規則。
1、如果資料列超過了行大小限制的8060位元組，那麼最大列將被儲存在off-row。例如，在一個表包含varbinary(8000)的列要加入varbinary(2000)列，那麼會將原本在in-row的varbinary(8000)列將被移至off-row。
2、所有索引鍵列都必須儲存在in-row；如果索引鍵列為無法存在在in-row的表，則無法添加索引。考慮之前例子中的那張表。如果在varbinary(8000)列中建立索引，那麼varbinary(8000)列被移入in-row，而varbinary(2000)列被移至off-row，因為索引鍵列必須儲存在in-row。
下列查詢顯示了所有的列都被儲存在off-row，依據它們列的大小與記憶體使用量情況。

SELECT object_name(moa.object_id) AD ‘table‘, c.name AS ‘column‘, c.max_lengthFROM sys.memory_optimized_tables_internal_attributes moa     JOIN sys.columns c ON moa.object_id = c.object_id AND moa.minor_id=c.column_idWHERE moa.type=5

 

使用下列查詢可以瞭解到更多有關行off-row的記憶體消耗，查詢顯示了所有儲存在內部表的off-row列和off-row索引的記憶體消耗：

SELECT  OBJECT_NAME(moa.object_id) AS ‘table‘,  c.name AS ‘column‘,  c.max_length,  mc.memory_consumer_desc,  mc.index_id,  mc.allocated_bytes,  mc.used_bytesFROM sys.memory_optimized_tables_internal_attributes moa   JOIN sys.columns c ON moa.object_id = c.object_id AND moa.minor_id=c.column_id   JOIN sys.dm_db_xtp_memory_consumers mc ON moa.xtp_object_id=mc.xtp_object_idWHERE moa.type=5

ALTER TABLE最佳化
ALTER TABLE一般用於更改架構及調優索引。詳細文法與範例見有關 Altering Memory-Optimizes Tables文檔。
SQL Server 2016中，記憶體最佳化表內的 ALTER TABLE操作是離線完成的，也就是說操作過程中無法進行表的查詢。所有的對記憶體最佳化表資料結構的更改和操作 包括列和索引變更都是利用建立新表並複製舊錶資料來完成的。在一個10GB 的表中進行ALTER操作在採用24個邏輯處理器的伺服器上並行運行，大約需要一分鐘就可以完成，這一時間隨著表的大小而變化。另一個好訊息是現在可以在一個ALTER TABLE 語句中組合多個ADD, DROP或 ALTER操作。 例如，你現在完全可以在一個ALTER TABLE 語句中添加一個列，一條索引，還可以再添加一個約束。
大部分ALTER TABLE情境都是並行啟動並執行，而且都經過交易記錄最佳化，交易記錄最佳化指的是只在交易記錄中寫入中繼資料變化。但部分ALTER TABLE操作是單線程的，而且並不能進行日誌最佳化，也就是說將完整的表複製進交易記錄中，作為ALTER TABLE事務的一部分。
下面列舉的ALTER 操作都是單線程的，而且不能進行日誌最佳化：
1、ADD/ALTER一個使用大對象(LOB)資料類型的列：nvarchar(max), varchar(max)或varbinary(max)。
2、ADD/DROP 一個COLUMNSTORE資料行存放區索引。.
3、ADD/ALTER一個off-row列，那麼ADD/ALTER/DROP操作會引起in-row列移至off-row，或off-row列移至in-row。
注意: 使用ALTER語句增加一個off-row列的長度是可以進行日誌最佳化的。

 

統計資訊的改進
現在對記憶體最佳化表的統計資訊已經可以自動更新，並支援統計資訊採樣。正因為有了這個該井，記憶體最佳化表的統計資訊管理方式和基於磁碟表的統計資訊管理方式是一樣的，而且也有一樣的權衡。
1、是否需要更新統計資料的邏輯跟磁碟表的邏輯是一樣的，但有一個例外：磁碟表的modify計數器mod-counter是在每個資料列裡的，而記憶體最佳化表的mod-counter是在行層級的。Modify計數器通常用於跟蹤表裡面有多少資料發生了變化，一旦達到閥值自動更新統計資訊功能就會啟動。TF2453和（RECOMPILE重新編譯）選項在表變數裡得到支援。
2、支援AUTO_UPDATE_STATISTICS_ASYNC。
3、統計資訊採樣率跟基於硬碟的表一樣，而且支援並行採樣。
4、針對大部分統計資訊改進，請確保資料庫選項設定相容層級為 130。
5、為了自動更新已存在的統計資訊，需進行一次手動更新(見下面指令碼)。
6、手動重新編譯本地編譯模組。使用sp_recompile重新編譯本地編譯模組。
統計資訊的一次性指令碼:  您可以運行一次下面的Transact-SQL指令碼以更新所有記憶體最佳化表的統計資訊，然後啟用統計資訊的自動更新(假設資料庫已經開啟AUTO_UPDATE_STATISTICS)。

-- Assuming AUTO_UPDATE_STATISTICS is already ON for your database:-- ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS ON;GOALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130;GODECLARE @sql NVARCHAR(MAX) = N‘‘;SELECT      @sql += N‘UPDATE STATISTICS ‘         + quotename(schema_name(t.schema_id))         + N‘.‘         + quotename(t.name)         + ‘;‘ + CHAR(13) + CHAR(10)   FROM sys.tables AS t   WHERE t.is_memory_optimized = 1;EXECUTE sp_executesql @sql;GO-- Each row appended to @sql looks roughly like:-- UPDATE STATISTICS [dbo].[MyMemoryOptimizedTable];

 

以上就是SQL Server 2016中In-Memory OLTP的新改進

SQL Server 2016中In-Memory OLTP繼CTP3之後的新改進