文章出處

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_length
FROM 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
WHERE 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_bytes
FROM 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_id
WHERE 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;
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130;
GO
DECLARE @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];