We will now take a look at exactly what LOB data types are supported in SQL Server from SQL Server 2016 and higher, and what aspects of these data types need to be considered when using them with memory-optimized objects.

Let's begin by adding a LOB column to our test table using the following code:

USE InMemoryTest 
GO  
ALTER TABLE dbo.InMemoryTable Add NewColumnMax VARCHAR(MAX) NULL 
GO 

This follows the same scheme as the previous code examples: adding a LOB column is no different with a memory-optimized table than a disk-based table. At least on the surface, anyway!

Let's take another look at our index meta-data to see how exactly LOB columns are handled by the storage engine, using the following code:

USE InMemoryTest 
GO  
SELECT COALESCE(i.name, '--HEAP--') AS [index_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('InMemoryTable') 
      AND i.index_id IS NULL; 
GO 

There are a number of things to note here:

• We are filtering on the table/heap only (AND i.index_id IS NULL). This will allow us to concentrate on the base table.
• Two additional columns have been added, showing what type of memory consumer each row represents.
• We now see two additional rows displayed.

The previous listing returns two additional rows: one marked LOB Page Allocator and one marked Table heap, both with an internal table attribute of Internal off row data table. These are references to how LOB data is stored for memory-optimized tables.

LOB data is not stored along with the other data pages for a memory-optimized table. Rather, the LOB data is stored in a separate data structure that is optimized for LOB style data. LOB Page Allocator stores the actual LOB data, while  Table heap stores references to the LOB data, referring back to the original data pages. This arrangement has been designed so that the row-versioning mechanism, which is the foundation of the multi-version concurrency control inside the In-Memory OLTP engine (described in Chapter 11, Introducing SQL Server In-Memory OLTP), doesn't need to keep multiple versions of the LOB data in the same way as it must for regular columns. By decoupling the two data storage classes, SQL Server can be much more efficient with the storage of these two relatively different data types. In particular, LOB data has been regarded as a data type that is likely to be modified less often compared to non-LOB columns. As such, processing LOB data separately will greatly reduce memory resource usage, which will in turn reduce the overhead of storing LOB data in a memory-optimized table while still affording the ability of processing data types through one interface.

At the moment, the LOB column is empty (allocated = 0 and used = 0). If we now run a simple update to copy the UserName column data into the LOB column, we can run the same query, as shown in the following code, to investigate how the data is stored in the LOB data structures:

USE InMemoryTest 
GO  
UPDATE dbo.InMemoryTable 
SET NewColumnMax = UserName 
GO 
SELECT COALESCE(i.name, '--HEAP--') AS [index_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('InMemoryTable') 
      AND i.index_id IS NULL; 
GO 

Here, we are observing a similar behavior as seen earlier in the chapter, where allocation is occurring in base—2 steps. The LOB Page Allocator has allocated 128 MB and the supporting table heap has allocated 64 MB—although both are using much less space. This is an efficient step that avoids having to perform unnecessary additional allocations too often. Despite this, we notice a behavior that could cause memory resource contention if large amounts of LOB data were to be processed in this way.

Please note, if you run the index meta-data queries shortly after modifying the table structure you may notice that the memory allocations of the table are larger than shown in the example results here. This has to do with how ALTER statements are processed by the In-Memory OLTP engine. Earlier in the chapter, this mechanism was described, where it was stated that an ALTER makes a copy of the original table and incorporates the desired changes into the new copy. This leads to a doubling of the memory allocation until ghost record cleanup can remove the old copy of the data from memory.

When SQL Server processes LOB data in the In-Memory OLTP engine, the decision whether or not to push the data from in-row to off-row storage is made according to the table schema. This is a different mechanism than the disk-based storage engine uses, which bases the decision on the data being processed at execution time. Disk-based storage only places data off-row when it won't fit on a data page, while memory-optimized tables place data off-row when the table schema describes a column as being either a max data type or that the row could store more than 8060 bytes when completely full. When LOB data is stored off-row for a memory-optimized table, there is a significant overhead associated with that storage. A reference must be stored for the in-row data (the base memory-optimized table), the off-row data (the LOB data itself in LOB Page Allocator), and the leaf level of the range index (the intermediary heap referencing between the on-row and off-row data). In addition to this, the actual data must be stored in LOB Page Allocator. This overhead can go beyond 50 bytes per row. This additional overhead is created and must be maintained for each and every LOB column that a memory-optimized table has. The more LOB columns there are, the more overhead and bloated referencing is required. As you may see, writing an additional 50+ bytes for each row with a LOB column (or multiples of this) can quickly cause significant resource and performance issues.