Edit

Native compilation of tables and stored procedures

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

In-memory online transaction processing (OLTP) uses native compilation to provide faster data access and more efficient query execution than interpreted (traditional) Transact-SQL. Native compilation of tables and stored procedures produces dynamic link libraries (DLLs).

The SQL Server Database Engine can natively compile memory-optimized tables, stored procedures that access memory-optimized tables, and memory-optimized table types. For more information, see Faster temp table and table variable by using memory optimization.

In-memory OLTP compiles memory-optimized tables when you create them, and natively compiles stored procedures into native DLLs when they're loaded. In addition, DLLs are recompiled after a database or server restart. The database stores the information necessary to recreate the DLLs in metadata. The DLLs aren't part of the database, though they're associated with the database. For example, the DLLs aren't included in database backups.

Note

Memory-optimized tables are recompiled after a server restarts. To speed up database recovery, the server doesn't recompile natively compiled stored procedures during the restart itself. Instead, it compiles them 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 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 don't need to maintain files generated by native compilation. The Database Engine automatically removes generated files that are no longer needed. For example, generated files are deleted when a table and stored procedure are deleted, or if a database is dropped.

Note

If compilation fails or is interrupted, some generated files aren't removed. These files are intentionally left behind for supportability and are removed when the database is dropped.

The Database Engine compiles DLLs for all tables needed for database recovery. If a table is dropped right before a database restart, remnants of the table might exist in the checkpoint files or the transaction log. As a result, the DLL for the table might be recompiled during database startup. If this process occurs after the database restart, the normal cleanup process unloads the DLL and removes the files.

Native compilation of tables

When you create a memory-optimized table using the CREATE TABLE statement, the table information is written to the database metadata, and the table and index structures are created in memory. The table is then compiled to a DLL.

The following sample script creates a database and a memory-optimized table. Make sure you set the FILENAME path to where your DATA subdirectory is.

USE master;
GO

CREATE DATABASE DbMemOpt3;
GO

ALTER DATABASE DbMemOpt3
ADD FILEGROUP DbMemOpt3_mod_memopt_1_fg
    CONTAINS MEMORY_OPTIMIZED_DATA;
GO

-- Change the FILENAME path to where your DATA subdirectory is located,
-- 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'
)
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

-- Retrieve the path of the DLL for table t1.
DECLARE @moduleName AS NVARCHAR (256);

SET @moduleName = ('%xtp_t_'
    + CAST (db_id() AS NVARCHAR (16))
    + '_' + CAST (object_id('dbo.t1') AS NVARCHAR (16))
    + '%.dll');

-- Search for the name: 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;
GO

-- Clean up.
-- DROP DATABASE DbMemOpt3;
-- 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. The Database Engine uses the DLL for traversing indexes, retrieving rows, and storing the contents of the rows.

Native compilation of stored procedures

Mark stored procedures with NATIVE_COMPILATION to natively compile them. 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 A Guide to Query Processing for Memory-Optimized Tables.

The following sample stored procedure 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 AS 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 dbo.t1;
GO

The DLL for native_sp can interact directly with the DLL for t1, and with the in-memory OLTP storage engine, to insert the rows as fast as possible. The query optimizer creates an efficient execution plan for each of the queries in the stored procedure.

Natively compiled stored procedures aren't 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. The compiler produces files that are written to disk and loaded into memory. The Database Engine uses the following mechanisms to limit access to these files.

Compiler

The compiler executable, binaries, and header files required for native compilation are installed as part of the Database Engine instance, under the folder MSSQL\Binn\Xtp. If you install the default instance under C:\Program Files, the compiler files are in C:\Program Files\Microsoft SQL Server\MSSQL\<version>.MSSQLSERVER\MSSQL\Binn\Xtp.

To limit access to the compiler, the Database Engine uses access control lists (ACLs) to restrict access to binary files. ACLs protect all Database Engine binaries against modification or tampering. The native compiler's ACLs also limit use of the compiler; only the Database Engine 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 the xtp subfolder of the default data folder defined as InstanceDefaultDataPath in SERVERPROPERTY. An example file path is: C:\Program Files\Microsoft SQL Server\MSSQL\<version>.MSSQLSERVER\MSSQL\DATA\Xtp.

The Database Engine prevents tampering with the generated DLLs in three ways:

  • When a table or stored procedure is compiled to a DLL, the DLL is immediately loaded into memory, and linked to the sqlserver.exe process. You can't modify a DLL while it's linked to a process.

  • When a database restarts, all tables and stored procedures are recompiled (that is, removed and recreated) from the database metadata, with stored procedures compiled at first execution. Recompilation discards any changes made to a generated file, such as tampering by a malicious agent.

  • The generated files are treated as user data and have the same security restrictions, via ACLs, as database files. Only the Database Engine service account and system administrators can access these files.

You don't need to manage these files. The Database Engine creates and removes the files as necessary.

Related content

  • Last updated on