Accessing IN-MEMORY OLTP Tables

In-Memory OLTP introduces the concept of native compilation. SQL Server can natively compile stored procedures that access memory-optimized tables. SQL Server is also able to natively compile memory-optimized tables. Native compilation allows faster data access and more efficient query execution than interpreted (traditional) Transact-SQL. Native compilation of tables and stored procedures produce DLLs.

Native compilation of memory optimized table types is also supported. For more information, see Memory-Optimized Table Variables.

Native compilation refers to the process of converting programming constructs to native code, consisting of processor instructions without the need for further compilation or interpretation.

In-Memory OLTP compiles memory-optimized tables when they are created, and natively compiled stored procedures when they are loaded to native DLLs. In addition, the DLLs are recompiled after a database or server restart. The information necessary to recreate the DLLs is stored in the database metadata. The DLLs are not part of the database, though they are associated with the database. For example, the DLLs are not included in database backups.

--In-Memory OLTP Tables 
select is_memory_optimized, [object_id] ObjectID from Adventureworks2014.sys.tables where is_memory_optimized !=0 
--Compiled DLL of In-Memory Tables-- 
SELECT name, description,base_address FROM sys.dm_os_loaded_modules WHERE description = 'XTP Native DLL'

Where is my data?

A memory-optimized file group internally uses filestream files to store inserted and deleted rows for in-memory tables. There are two types of files. A data file contains inserted rows while a delta file contains deleted rows. Each data file is pre-allocated to a size of 128 MBs but can get larger if there is a long running transaction or when a manual merge forces the resultant target file to be larger than 128MB.These files are also called checkpoint files and they come in pairs of DATA and DELTA.

<Use DatabaseName> 
GO
SELECT * FROM sys.dm_db_xtp_checkpoint_files 
GO

CPU : 1 socket : 2 core : 4 logical processor (When the database is configured for MEMORY_OPTIMIZED_DATA ,18 checkpoint files were created with pre-allocated to a size of 16 MB)

use AdventureWorks2012 
GO
select * from sys.dm_db_xtp_checkpoint_files cf where state=1

Checkpoint file and location