SQL Server 2016中In-Memory OLTP继CTP3之后的新改进

内存优化表与本地编译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];

 

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

SQL Server 2016中In-Memory OLTP继CTP3之后的新改进

标签：