Native Compilation of Tables and Stored Procedures
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
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 Faster temp table and table variable by using memory optimization.
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.
Note
Memory-optimized tables are recompiled during a server restart. To speed up database recovery, natively compiled stored procedures are not recompiled during a server restart, they are compiled at the time of first execution. As a result of this deferred compilation, natively compiled stored procedures only appear when calling sys.dm_os_loaded_modules (Transact-SQL) after first execution.
Maintenance of In-Memory OLTP DLLs
The following query shows all table and stored procedure DLLs currently loaded in memory on the server:
SELECT
mod1.name,
mod1.description
from
sys.dm_os_loaded_modules as mod1
where
mod1.description = 'XTP Native DLL';
Database administrators do not need to maintain files that are generated by a native compilation. SQL Server automatically removes generated files that are no longer needed. For example, generated files will be deleted when a table and stored procedure is deleted, or if a database is dropped.
Note
If compilation fails or is interrupted, some generated files are not removed. These files are intentionally left behind for supportability and are removed when the database is dropped.
Note
SQL Server compiles DLLs for all tables needed for database recovery. If a table was dropped just prior to a database restart there can still be remnants of the table in the checkpoint files or the transaction log so the DLL for the table might be recompiled during database startup. After restart the DLL will be unloaded and the files will be removed by the normal cleanup process.
Native Compilation of Tables
Creating a memory-optimized table using the CREATE TABLE statement results in the table information being written to the database metadata and the table and index structures created in memory. The table will also be compiled to a DLL.
Consider the following sample script, which creates a database and a memory-optimized table:
USE master;
GO
CREATE DATABASE DbMemopt3;
GO
ALTER DATABASE DbMemopt3
add filegroup DbMemopt3_mod_memopt_1_fg
contains memory_optimized_data
;
GO
-- You must edit the front portion of filename= path, to where your DATA\ subdirectory is,
-- keeping only the trailing portion '\DATA\DbMemopt3_mod_memopt_1_fn'!
ALTER DATABASE DbMemopt3
add file
(
name = 'DbMemopt3_mod_memopt_1_name',
filename = 'C:\DATA\DbMemopt3_mod_memopt_1_fn'
--filename = 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSVR2016ID\MSSQL\DATA\DbMemopt3_mod_memopt_1_fn'
)
to filegroup DbMemopt3_mod_memopt_1_fg
;
GO
USE DbMemopt3;
GO
CREATE TABLE dbo.t1
(
c1 int not null primary key nonclustered,
c2 int
)
with (memory_optimized = on)
;
GO
-- You can safely rerun from here to the end.
-- Retrieve the path of the DLL for table t1.
DECLARE @moduleName nvarchar(256);
SET @moduleName =
(
'%xtp_t_' +
cast(db_id() as nvarchar(16)) +
'_' +
cast(object_id('dbo.t1') as nvarchar(16)) +
'%.dll'
)
;
-- SEARCHED FOR NAME EXAMPLE: mod1.name LIKE '%xtp_t_8_565577053%.dll'
PRINT @moduleName;
SELECT
mod1.name,
mod1.description
from
sys.dm_os_loaded_modules as mod1
where
mod1.name LIKE @moduleName
order by
mod1.name
;
-- ACTUAL NAME EXAMPLE: mod1.name = 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSVR2016ID\MSSQL\DATA\xtp\8\xtp_t_8_565577053_184009305855461.dll'
GO
-- DROP DATABASE DbMemopt3; -- Clean up.
GO
Creating the table also creates the table DLL and loads the DLL in memory. The DMV query immediately after the CREATE TABLE statement retrieves the path of the table DLL.
The table DLL understands the index structures and row format of the table. SQL Server uses the DLL for traversing indexes, retrieving rows, as well as storing the contents of the rows.
Native Compilation of Stored Procedures
Stored procedures that are marked with NATIVE_COMPILATION are natively compiled. This means the Transact-SQL statements in the procedure are all compiled to native code for efficient execution of performance-critical business logic.
For more information about natively compiled stored procedures, see Natively Compiled Stored Procedures.
Consider the following sample stored procedure, which inserts rows in the table t1 from the previous example:
CREATE PROCEDURE dbo.native_sp
with native_compilation,
schemabinding,
execute as owner
as
begin atomic
with (transaction isolation level = snapshot,
language = N'us_english')
DECLARE @i int = 1000000;
WHILE @i > 0
begin
INSERT dbo.t1 values (@i, @i+1);
SET @i -= 1;
end
end;
GO
EXECUTE dbo.native_sp;
GO
-- Reset.
DELETE from dbo.t1;
GO
The DLL for native_sp can interact directly with the DLL for t1, as well as the In-Memory OLTP storage engine, to insert the rows as fast as possible.
The In-Memory OLTP compiler leverages the query optimizer to create an efficient execution plan for each of the queries in the stored procedure. Note that natively compiled stored procedures are not automatically recompiled if the data in the table changes. For more information on maintaining statistics and stored procedures with In-Memory OLTP see Statistics for Memory-Optimized Tables.
Security Considerations for Native Compilation
Native compilation of tables and stored procedures uses the In-Memory OLTP compiler. This compiler produces files that are written to disk and loaded into memory. SQL Server uses the following mechanisms to limit access to these files.
Native Compiler
The compiler executable, as well as binaries and header files required for native compilation are installed as part of the SQL Server instance under the folder MSSQL\Binn\Xtp. So, if the default instance is installed under C:\Program Files, the compiler files are installed in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\Xtp.
To limit access to the compiler, SQL Server uses access control lists (ACLs) to restrict access to binary files. All SQL Server binaries are protected against modification or tampering through ACLs. The native compiler's ACLs also limit use of the compiler; only the SQL Server service account and system administrators have read and execute permissions for native compiler files.
Files Generated by a Native Compilation
The files produced when a table or stored procedure is compiled include the DLL and intermediate files including files with the following extensions: .c, .obj, .xml, and .pdb. The generated files are saved in a subfolder of the default data folder. The subfolder is called Xtp. When installing the default instance with the default data folder, the generated files are placed in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Xtp.
SQL Server prevents tampering with the generated DLLs in three ways:
When a table or stored procedure is compiled to a DLL, this DLL is immediately loaded into memory and linked to the sqlserver.exe process. A DLL cannot be modified while it is linked to a process.
When a database is restarted, all tables and stored procedures are recompiled (removed and recreated) based on the database metadata. This will remove any changes made to a generated file by a malicious agent.
The generated files are considered part of user data, and have the same security restrictions, via ACLs, as database files: only the SQL Server service account and system administrators can access these files.
No user interaction is needed to manage these files. SQL Server will create and remove the files as necessary.