Let's now take a look at the difference in storage and processing between two tables with variable length character columns. One table has varchar(5) columns, which will be small enough to fit in the in-row storage. The other table will have a series of varchar(max) columns, which will automatically be stored in the off-row storage. These tables will be created and filled with 100,000 rows each to demonstrate both the storage and also the performance differences between the two storage types. We will create the two tables using the following code:

USE InMemoryTest 
GO 
DROP TABLE IF EXISTS dbo.InMemoryTableMax 
DROP TABLE IF EXISTS dbo.InMemoryTableNotMax 
GO 
 
CREATE TABLE dbo.InMemoryTableMax 
( 
   UserId INT NOT NULL IDENTITY (1,1), 
   MaxCol1 VARCHAR(max) COLLATE Latin1_General_CI_AI NOT NULL , 
   MaxCol2 VARCHAR(max) COLLATE Latin1_General_CI_AI NOT NULL , 
   MaxCol3 VARCHAR(max) COLLATE Latin1_General_CI_AI NOT NULL , 
   MaxCol4 VARCHAR(max) COLLATE Latin1_General_CI_AI NOT NULL , 
   MaxCol5 VARCHAR(max) COLLATE Latin1_General_CI_AI NOT NULL , 
   CONSTRAINT PK_InMemoryTableMax  PRIMARY KEY NONCLUSTERED (UserId), 
 
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY=SCHEMA_AND_DATA) 
GO 
 
CREATE TABLE dbo.InMemoryTableNotMax 
( 
   UserId INT NOT NULL IDENTITY (1,1), 
   Col1 VARCHAR(5) COLLATE Latin1_General_CI_AI NOT NULL , 
   Col2 VARCHAR(5) COLLATE Latin1_General_CI_AI NOT NULL , 
   Col3 VARCHAR(5) COLLATE Latin1_General_CI_AI NOT NULL , 
   Col4 VARCHAR(5) COLLATE Latin1_General_CI_AI NOT NULL , 
   Col5 VARCHAR(5) COLLATE Latin1_General_CI_AI NOT NULL , 
   CONSTRAINT PK_InMemoryTableNotMax  PRIMARY KEY NONCLUSTERED (UserId), 
 
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY=SCHEMA_AND_DATA) 
GO 

The only difference between these two tables is the column data type: one uses varchar(max), while the other uses a varchar(5) data type. We are leaving the tables with a primary key and no other indexes because we only want to investigate the on-row and off-row storage differences.

If we now run our memory consumers query from earlier on in this section, we can investigate how LOB Page Allocator and the corresponding Table heap objects are created for each table, using the following code:

 
SELECT OBJECT_NAME(c.object_id) AS [table_name], 
    c.allocated_bytes AS allocated, 
    c.used_bytes AS used, 
    c.memory_consumer_desc AS memory_consumer, 
    a.type_desc 
FROM sys.dm_db_xtp_memory_consumers c 
    JOIN sys.memory_optimized_tables_internal_attributes a 
        ON a.object_id = c.object_id 
           AND a.xtp_object_id = c.xtp_object_id 
    LEFT JOIN sys.indexes i 
        ON c.object_id = i.object_id 
           AND c.index_id = i.index_id 
WHERE 
( 
    c.object_id = OBJECT_ID('InMemoryTableNotMax') 
    OR c.object_id = OBJECT_ID('InMemoryTableMax') 
) 
AND i.index_id IS NULL 

The results of the memory consumer query show that we have a single table heap for the InMemoryTableNotMax table (the table with the varchar(5) columns) and that we have several internal off-row data tables for the table InMemoryTableMax. In fact, we have LOB Page Allocator and a matching Table heap for each varchar(max) column in the table.

We then fill each table with 100,00 rows of data while running a basic timing comparison to see what performance overhead LOB Page Allocator and Table heap maintenance causes. We do this using the following code:

SET NOCOUNT ON  
GO 
 
SET STATISTICS TIME ON 
GO 
 
INSERT INTO dbo.InMemoryTableMax 
        ( MaxCol1 , 
          MaxCol2 , 
          MaxCol3 , 
          MaxCol4 , 
          MaxCol5 
        ) 
SELECT TOP 100000 
    'Col1', 
    'Col2', 
    'Col3', 
    'Col4', 
    'Col5' 
FROM sys.columns a 
    CROSS JOIN sys.columns; 
GO 
 
INSERT INTO dbo.InMemoryTableNotMax 
        ( Col1 , 
          Col2 , 
          Col3 , 
          Col4 , 
          Col5 
        ) 
SELECT TOP 100000 
    'Col1', 
    'Col2', 
    'Col3', 
    'Col4', 
    'Col5' 
FROM sys.columns a 
    CROSS JOIN sys.columns 
GO 
 
SET STATISTICS TIME OFF 
GO  
SQL Server Execution Times: 
   CPU time = 1797 ms,  elapsed time = 1821 ms. 
 
SQL Server Execution Times: 
   CPU time = 281 ms,  elapsed time = 382 ms. 

The results at the end of this listing show that the elapsed time for inserting 100,000 rows into the table with varchar(max) columns took roughly five times longer than it did to insert the same rows into the table with varchar(5) columns. This timing difference is down to the overhead of inserting data into off-row storage ( LOB Page Allocator and matching Table heap).

If we also take another look at the memory consumption statistics, we will see that there is also a significant difference in memory consumption between the two tables, as shown in the following code:

SELECT OBJECT_NAME(c.object_id) AS [table_name], 
    SUM(c.allocated_bytes) / 1024. AS allocated, 
    SUM(c.used_bytes) / 1024. AS used 
FROM sys.dm_db_xtp_memory_consumers c 
    JOIN sys.memory_optimized_tables_internal_attributes a 
        ON a.object_id = c.object_id 
           AND a.xtp_object_id = c.xtp_object_id 
    LEFT JOIN sys.indexes i 
        ON c.object_id = i.object_id 
           AND c.index_id = i.index_id 
WHERE 
( 
    c.object_id = OBJECT_ID('InMemoryTableNotMax') 
    OR c.object_id = OBJECT_ID('InMemoryTableMax') 
) 
AND i.index_id IS NULL 
GROUP BY c.object_id; 


table_name          allocated       used 
------------------- ------------   ------------ 
InMemoryTableMax    59392.000000   58593.750000 
InMemoryTableNotMax 7104.000000    7031.250000 

We can see that we should very carefully consider the use of LOB data types for memory-optimized tables. If LOB data types are required, then the performance and resource consumption overhead should be noted, especially where high performance is the driving factor for the adoption of a memory-optimized solution.