Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
SQL Server 2016 is making a lot of enhancements to In-Memory OLTP to make it easier to use and perform even better. In a previous post I listed all the new features that had been included in SQL Server 2016 up to and including CTP3. But we have added a lot of new features since then, including NULLable index key columns, LOB types and auto-update of statistics. Below are all the new features for In-Memory OLTP that we added between CTP3 and RC0. Underneath the list of new features you will find more detailed descriptions of LOBs and other off-row columns, ALTER TABLE improvements, and statistics improvements.
Transact-SQL Improvements:
Large object (LOB) types varchar(max), nvarchar(max) and varbinary(max) are now supported with memory-optimized tables and table types, as well as natively compiled T-SQL modules, and the size limitations mirror that of disk-based tables (2GB limit on LOB values). In addition, you can have a memory-optimized table with a row size > 8060 bytes, even when no column in the table uses a LOB type. There is no run-time limitation on the size of rows or the data in individual columns; this is part of the table definition. Of course, all data does need to fit in memory.
Even though large columns are now supported, it is still recommended to have most columns fit within 8060 bytes for performance reasons. Details below.
The following T-SQL script illustrates a table with multiple large non-LOB columns and a single LOB column:
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 columns and other columns that do not fit in the 8060 byte in-row storage are stored off-row, while the in-row storage has an 8-byte reference to the off-row data. There is an internal table for each individual off-row column.
The logic that decides whether a given column lives on-row or off-row is as follows, and every ALTER TABLE operation ensures that these rules are followed.
The following query shows all columns that are stored off-row, along with their sizes and memory utilization. A size of -1 indicates a LOB column. All LOB columns are stored off-row.
SELECT object_name(moa.object_id) AS '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
To get more details about the memory consumption of off-row column you can use the following query, which shows the memory consumption of all internal tables and their indexes that are used to store the off-row columns:
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 is used to make schema changes and tune indexes. For details about syntax and examples see the documentation about Altering Memory-Optimizes Tables.
In SQL Server 2016 ALTER TABLE operations on memory-optimized tables are offline, meaning that the table is not available for queries while the operation is ongoing. All operations that make changes to the in-memory data structures, including column and index changes, result in a new copy of the table being created under the hood. An ALTER operation on a 10GB table takes roughly 1 minute when running in parallel on a server with 24 logical processors, and the time taken scales with the size of the table. Good news is that it is possible to combine multiple ADD, DROP, or ALTER operations in a single ALTER TABLE statement. For example, you could add a column, add an index, and add a constraint, all in one ALTER TABLE statement.
Most ALTER TABLE scenarios run in parallel and the operation is log-optimized, meaning that only the metadata changes are written to the transaction log. However, there are some ALTER TABLE operations that run single-threaded and are not log-optimized, meaning that a complete copy of the table is written to the transaction log as part of the ALTER TABLE transaction.
The following ALTER operations run single-threaded and are not log-optimized:
Statistics for memory-optimized tables are now updated automatically, and sampling of statistics is supported. With these changes, statistics management for memory-optimized tables works essentially in the same way as disk-based tables, and comes with the same tradeoffs.
One-time script for statistics: For memory-optimized tables that were created before SQL Server 2016 CTP3.3, you can run the following Transact-SQL script one time to update the statistics of all memory-optimized tables, and enable automatic update of statistics from then onward (assuming AUTO_UPDATE_STATISTICS is enabled for the database).
-- 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];
For all the features added in SQL Server 2016 up to and including CTP3 see What's new for In-Memory OLTP in SQL Server 2016 CTP3
For general information about In-Memory OLTP, see In-Memory OLTP (In-Memory Optimization).
To get started today, download the latest build of SQL Server 2016 or spin up an Azure VM with SQL Server 2016 pre-installed.
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in