New improvements after in-memory OLTP relay CTP3 in SQL Server 2016

New improvements after in-memory OLTP relay CTP3 in SQL Server 2016

Translated from: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/25/ whats-new-for-in-memory-oltp-in-sql-server-2016-since-ctp3/

SQL Server 2016 is making a series of enhancements to the In-memory OLTP feature, which makes it easier to use and better performing.
In the previous article, I've summarized the new features of SQL Server 2016 and later versions of CTP3.
Since then, we have added new features on top of the original functionality, including NULL index key columns, LOB type fields, and automatic Update statistics.
Here are the new features we added for In-memory OLTP between the CTP3 and RC0 versions.
In the list of new features below, you will see a more detailed description of lobs and other Off-row columns, table structure modifications, and statistical improvements.

New In-memory OLTP features between CTP3 and RC0 functional layers

Transact-SQL improvements:
1, the local module query surface:
2. LOB data type: You can now use [varchar (max), nvarchar (max) and varbinary (max)] as input parameters and variables.
3. OUTPUT clause: Currently in the local compilation stored procedure, insert,update and delete also contain the OUTPUT clause.
4, @ @SPID: This built-in functionality is supported by a locally compiled T-SQL module, with the constraints shown in memory-optimized tables.
5. The features supported by the memory-optimized table are added as follows:
6, nullable index key column. The nullable column is now allowed to be added to the index key in the memory-optimized table.
7. Large row: The columns of the memory-optimized table can use LOB data types [varchar (max), nvarchar (max and varbinary (max)]. In addition, the row size of a memory-optimized table can exceed 8060 bytes when there is no LOB data type in the column. See below for detailed instructions.
8. A unique index in the memory-optimized table. The index can now be specified as unique.
9. Heap Scan: The query handler can scan the rows in the heap data structure table directly in memory. This method is more efficient than full-index scanning when a full table scan is required.
10, parallel scanning: All index types and basic heap performance are supported in parallel scanning. Analytic queries can be enhanced to scan the performance of large datasets.
11. Reduce the downtime required for updates: updating from an earlier version of SQL Server 2016 to the latest version no longer requires database recovery to run. Therefore, the data size no longer affects the upgrade time. For SQL Server 2014 upgrade and attach/restore, the database needs to be restarted, so the downtime required for the SQL2014 database upgrade is approximately [the time required for database recovery].
12. Log optimization and parallel ALTER TABLE: Currently, most of the ALTER table is parallel and optimized to write to the transaction log. Optimization refers to a write-only metadata change. A detailed discussion of the exceptions is shown below.

Statistical information Improvements:
1. Automatic update of statistics is now supported. You no longer need to manually update statistics.
2. Statistical information sampling is now supported. You can improve the performance of statistical information collection.
Note that automatic recompilation of local modules is not supported. Manual recompilation is required using sp_recompile.

LOB and other Off-row columns
Memory-optimized tables and locally compiled T-SQL modules now support large object (LOB) data type varchar (max), nvarchar (max) and varbinary (max), and size limits as disk-based tables (LOB data type data cannot exceed 2GB )。 In addition, the row size of a memory-optimized table can exceed 8060 bytes even when there are no LOB data type columns in the table. Depending on the table definition, the row size or row data does not have a run time limit. Of course, all data also needs to be loaded into memory.
Even if the LOB type column is now supported, it is recommended that the column size is less than 8060 bytes for optimal performance. See below for more information.
The following T-SQL script can describe a table with multiple Non-lob columns and a single LOB column:

CREATE TABLEdbo. Largetablesample (Idint IDENTITY PRIMARY KEY nonclustered, C1nvarchar(4000), C2nvarchar(4000), C3nvarchar(4000), C4nvarchar(4000), Miscnvarchar(Max))  with(memory_optimized=  on);GO

LOB columns and other columns, such as 8060-byte storage that cannot be loaded into In-row, Off-row,in-row only store Off-row 8-byte references. There will also be an internal table to store each off-row column separately.
The logic for loading columns into On-row or Off-row is as follows, and each alter TABLE operation must ensure that the following rules are followed.
1, if the data column exceeds the row size limit of 8060 bytes, then the largest column will be stored in Off-row. For example, if a column containing varbinary (8000) in a table is to be added to the varbinary (2000) column, the varbinary (8000) column that was originally in In-row will be moved to Off-row.
2. All index key columns must be stored in In-row, and indexes cannot be added if the index key is listed as a table that cannot exist in In-row. Consider the table in the previous example. If you create an index in the varbinary (8000) column, the varbinary (8000) columns are moved into In-row, and the varbinary (2000) column is moved to Off-row because the index key columns must be stored in In-row.
The following query shows that all columns are stored in Off-row, depending on their column size and memory usage.

SELECT object_name(MOA.object_id) AD'Table', C.name as 'column', C.max_length fromsys.memory_optimized_tables_internal_attributes MoaJOINSys.columns C onMoa.object_id =C.object_id  andmoa.minor_id=c.column_idWHEREMoa.type=5

Using the following query to learn more about the memory consumption of row off-row, the query shows the memory consumption of all off-row columns and off-row indexes stored in the internal table:

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 fromsys.memory_optimized_tables_internal_attributes MoaJOINSys.columns C onMoa.object_id =C.object_id  andmoa.minor_id=c.column_idJOINSys.dm_db_xtp_memory_consumers MC onmoa.xtp_object_id=mc.xtp_object_idWHEREMoa.type=5

ALTER Table Optimization
ALTER TABLE is typically used to change schemas and tune indexes. See the altering Memory-optimizes tables documentation for detailed syntax and examples.
In SQL Server 2016, the ALTER table operation in a memory-optimized table is done offline, which means that table queries cannot be made during the operation. All changes and operations to memory-optimized table data structures, including column and index changes, are done by creating a new table and copying the old table data. An alter operation in a 10GB table runs in parallel on a server with 24 logical processors, which takes about a minute to complete, which varies with the size of the table. Another good news is that you can now combine multiple add, drop, or alter operations in an ALTER TABLE statement. For example, you can now add a column, an index, and another constraint to an ALTER TABLE statement.
Most of the ALTER table scenarios are run in parallel and are transaction log optimizations, and transaction log optimization refers to only metadata changes written in the transaction log. However, some ALTER TABLE operations are single-threaded and cannot be optimized for logging, which means that the complete table is copied into the transaction log as part of the ALTER TABLE transaction.
The alter operations listed below are single-threaded and cannot be optimized for logging:
1. Add/alter A column that uses a large object (LOB) data type: nvarchar (max), varchar (max), or varbinary (max).
2. Add/drop a columnstore columnstore index.
3. Add/alter a off-row column, the add/alter/drop operation causes the In-row column to move to Off-row, or Off-row column to In-row.
Note : Using the ALTER statement to increase the length of a off-row column can be log optimized.

Improvement of statistical information
Statistics for memory-optimized tables are now automatically updated, and statistical information sampling is supported. Because of this well, the management of statistical information in memory-optimized tables is the same as that of disk-based statistics management, and there is the same tradeoff.
1, whether the logic to update the statistics is the same as the logic of the disk table, but with one exception: The disk table Modify counter Mod-counter is in each data column, and the memory-optimized table Mod-counter is at the row level. The modify counter is typically used to track how much data has changed in the table, and will be activated once the threshold auto-Update STATISTICS function is reached. The TF2453 and (recompile recompile) options are supported in table variables.
2, Support Auto_update_statistics_async.
3, the statistical information sampling rate is the same as the disk-based table, and supports parallel sampling.
4. For most statistical information improvements, make sure that the database options set compatibility level is 130.
5, in order to automatically update the existing statistics, a manual update is required (see script below).
6. Manually recompile the local compilation module. Recompile the local compilation module with Sp_recompile.
A one-time script for statistics: You can run the following Transact-SQL script once to update the statistics for all memory-optimized tables, and then enable automatic updating of statistics (assuming the database is turned on auto_update_statistics).

--assuming Auto_update_statistics is already on for your database:--ALTER DATABASE current SET auto_update_statistics on;GOALTER DATABASE  Current SETCompatibility_level=  the;GODECLARE @sql NVARCHAR(MAX)=N"';SELECT      @sql +=N'UPDATE STATISTICS'         + QuoteName(Schema_name (t.schema_id))+N'.'         + QuoteName(T.name)+ ';' + CHAR( -)+ CHAR(Ten)    fromSys.tables asTWHERET.is_memory_optimized= 1;EXECUTEsp_executesql@sql;GO--Each row appended to @sql looks roughly like:--UPDATE STATISTICS [dbo]. [Mymemoryoptimizedtable];

These are the new improvements to in-memory OLTP in SQL Server 2016

New improvements after in-memory OLTP relay CTP3 in SQL Server 2016