Conditions for memory optimized tables stored in off-row data table

Question

Memory optimized tables store LOB types in internal tables.

I have a table with no explicit LOB types but when I run a query against sys.memory_optimized_tables_internal_attributes, I see varchar(255) columns listed with a type_desc value of INTERNAL OFF-ROW DATA TABLE.

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_PADDING ON;
GO
CREATE TABLE dbo.DERP
(
 RECORD_KEY numeric(30, 0) NOT NULL
, COL_1 bigint NOT NULL
, COL_2 datetime2(0) NOT NULL
, COL_3 datetime2(0) NOT NULL
, COL_4 varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_5 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_6 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_7 date NULL
, COL_8 bigint NULL
, COL_9 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_10 int NULL
, COL_11 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_12 int NULL
, COL_13 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_14 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_15 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_16 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_17 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_18 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_19 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_20 char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_21 bigint NULL
, COL_22 bigint NULL
, COL_23 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_24 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_25 int NULL
, COL_26 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_27 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_28 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_29 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_30 decimal(7, 4) NULL
, COL_31 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_32 decimal(8, 4) NULL
, COL_33 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_34 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_35 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_36 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_37 int NULL
, COL_38 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_39 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_40 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_41 int NULL
, COL_42 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_43 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_44 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_45 int NULL
, COL_46 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_47 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_48 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_49 int NULL
, COL_50 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_51 int NULL
, COL_52 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_53 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_54 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_55 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_56 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_57 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_58 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_59 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_60 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_61 date NULL
, COL_62 char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_63 int NULL
, COL_64 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_65 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_66 int NULL
, COL_67 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_68 int NULL
, COL_69 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_70 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_71 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_72 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_73 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_74 char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_75 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_76 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_77 char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_78 char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_79 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, COL_80 bigint NULL
, CONSTRAINT dbo_DERP
 PRIMARY KEY NONCLUSTERED
 (
 RECORD_KEY ASC
 )
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
SET ANSI_PADDING OFF;
GO

Running the following query yields 10 columns (COL_58, COL_64, COL_65, COL_67, COL_70, COL_72, COL_73, COL_75, COL_76, COL_79) listed as off-row data table

SELECT
 QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(moa.object_id)) AS 'table'
, c.name AS 'column'
, c.max_length
, moa.type_desc
FROM
 sys.memory_optimized_tables_internal_attributes moa
 INNER JOIN
 sys.columns c
 ON moa.object_id = c.object_id
 AND moa.minor_id = c.column_id
 INNER JOIN
 sys.objects o
 ON moa.object_id = o.object_id
WHERE
 o.name = 'DERP'
ORDER BY
 1;

I assume this storage decision is based on this table being super wide (nearly 10,400 bytes wide), yes?

SQL Server 2016 SP1, CU2, aka 13.0.4422.0

Answer 1

Bill you are absolutely correct and this is a new feature added in SQL 2016.

Table and Row Size in Memory-Optimized Tables

A memory-optimized table consists of a collection of rows and indexes that contain pointers to rows. In a memory-optimized table, in-row data cannot be longer than 8,060 bytes. However, starting SQL Server 2016 it is possible to create a table with multiple large columns (e.g., multiple varbinary(8000) columns) and LOB columns (i.e., varbinary(max), varchar(max), and nvarchar(max)). Columns that exceed the max size for in-row data are placed off-row, in special internal tables.

To prove that I took out 10 of you varchar(255) data type columns and recreated the table. Now I get zero record for the 2nd query. Because you row maximum row length becomes 7824 MB.

Now if you add one more column with varchar(255) you will see a single entry for internal off-row data table because maximum row length is over 8060 MB (8079 MB)

You can use this query to check your maximum row length.

SELECT OBJECT_NAME (id) tablename
 , COUNT (1) nr_columns
 , SUM (length) maxrowlength
FROM syscolumns
WHERE ID =OBJECT_ID('[dbo].[DERP]')
GROUP BY OBJECT_NAME (id)
ORDER BY OBJECT_NAME (id)