Natively Compiled Procedures, Supported T-SQL, Compilation, and Performance Model

Learning outcomes

The dispatch table is now memory-optimized, but its busiest stored procedure still executes interpreted T-SQL. A teammate proposes recompiling every procedure “because native is always faster.” Native compilation removes portions of the general-purpose interpreted execution path, but it also imposes a deployment contract: the procedure is schema-bound, uses one ATOMIC block, must stay within the supported native T-SQL surface, and is not a vehicle for parallel analytical queries. Native compilation is therefore a workload-specific optimization, not a new default coding style.

1. Native table metadata and native procedure code are related but distinct

Memory-optimized tables themselves are compiled into internal native structures. A natively compiled stored procedure goes further: its supported T-SQL statements are compiled into native machine code and stored as a DLL associated with the database. The DLL is generated from database metadata; it is not itself the durable backup artifact. After a server/database restart, SQL Server can re-create native DLLs, so the first use after bringing the database online can include compilation work.

Interpreted T-SQL can access memory-optimized tables. That fact is operationally important because migration does not require converting every application procedure on day one. You can move a hot table first, keep ordinary stored procedures, measure, then selectively native-compile only stable, high-frequency units of work that fit the supported surface.

USE master;GOIF DB_ID(N'ServiceHubXtpLab') IS NULLBEGIN    CREATE DATABASE ServiceHubXtpLab;END;GOALTER DATABASE ServiceHubXtpLab SET RECOVERY SIMPLE;ALTER DATABASE ServiceHubXtpLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubXtpLab;GOIF NOT EXISTS (SELECT 1 FROM sys.filegroups WHERE type = 'FX')BEGIN    ALTER DATABASE ServiceHubXtpLab      ADD FILEGROUP ServiceHubXtpFG CONTAINS MEMORY_OPTIMIZED_DATA;    DECLARE @base nvarchar(4000) = CONVERT(nvarchar(4000), SERVERPROPERTY('InstanceDefaultDataPath'));    IF @base IS NULL        THROW 50001, 'InstanceDefaultDataPath is unavailable. Supply a writable SQL Server data path manually.', 1;    DECLARE @folder nvarchar(4000) = @base + N'ServiceHubXtpContainer';    DECLARE @sql nvarchar(max) =      N'ALTER DATABASE ServiceHubXtpLab ADD FILE '      + N'(NAME=N''ServiceHubXtpContainer'', FILENAME=N'''      + REPLACE(@folder,'''','''''')      + N''') TO FILEGROUP ServiceHubXtpFG;';    EXEC sys.sp_executesql @sql;END;GOUSE ServiceHubXtpLab;GOIF OBJECT_ID(N'dbo.DispatchQueue') IS NULLBEGIN  CREATE TABLE dbo.DispatchQueue  (    dispatch_id bigint NOT NULL,    region_code char(3) NOT NULL,    due_at datetime2(0) NOT NULL,    status varchar(16) NOT NULL,    payload nvarchar(200) NULL,    CONSTRAINT PK_DispatchQueue PRIMARY KEY NONCLUSTERED HASH(dispatch_id)      WITH (BUCKET_COUNT=65536),    INDEX IX_DispatchQueue_Due NONCLUSTERED(due_at,region_code)  ) WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA);END;GO

2. Build the same operation in interpreted and native forms

USE ServiceHubXtpLab;GOCREATE OR ALTER PROCEDURE dbo.usp_SetDispatchStatus_Interpreted  @dispatch_id bigint,  @new_status varchar(16)ASBEGIN  SET NOCOUNT ON;  UPDATE dbo.DispatchQueue  SET status=@new_status  WHERE dispatch_id=@dispatch_id;  RETURN CASE WHEN @@ROWCOUNT=1 THEN 0 ELSE 1 END;END;GO

This procedure uses the normal SQL Server procedure execution machinery, but its target table remains memory-optimized. That already avoids rowstore locks/latches on the target structure. The native form removes more interpreter/query-processing overhead for supported code paths.

DROP PROCEDURE IF EXISTS dbo.usp_SetDispatchStatus_Native;GOCREATE PROCEDURE dbo.usp_SetDispatchStatus_Native  @dispatch_id bigint,  @new_status varchar(16)WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNERASBEGIN ATOMICWITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  UPDATE dbo.DispatchQueue  SET status=@new_status  WHERE dispatch_id=@dispatch_id;END;GO

Three clauses matter. NATIVE_COMPILATION requests native code generation. SCHEMABINDING prevents incompatible object changes from silently invalidating the compiled module. BEGIN ATOMIC defines the procedure's single transaction boundary and requires a transaction isolation level and language. You do not write a normal BEGIN TRAN/COMMIT pair inside the native procedure.

SELECT  p.name,  m.uses_native_compilation AS is_natively_compiled,  m.is_schema_bound,  m.execute_as_principal_idFROM sys.procedures AS pJOIN sys.sql_modules AS m ON m.object_id=p.object_idWHERE p.name IN (N'usp_SetDispatchStatus_Interpreted',N'usp_SetDispatchStatus_Native');GO

3. Performance model: remove overhead only where overhead matters

Native compilation is most compelling for short, high-frequency transactional logic where procedure interpretation, expression evaluation and storage-engine call overhead are a material fraction of total latency. It does not make network round trips disappear, fix a poor business algorithm, remove logging for durable data, increase available memory, or turn an analytical scan into a parallel warehouse query. Microsoft documents that native procedures are single-threaded and have a more constrained operator/T-SQL surface than ordinary modules.

A fair comparison executes equivalent logic against the same data under the same concurrency, durability, client connection, logging and hardware conditions. First execute each procedure enough times to separate first-use compilation/warm-up from steady state. Then measure client-side throughput and latency percentiles under representative concurrency. Record failures and retries too: a design that looks fast with one session but collapses under validation conflicts has not solved the workload.

IF NOT EXISTS (SELECT 1 FROM dbo.DispatchQueue WHERE dispatch_id=100)  INSERT dbo.DispatchQueue(dispatch_id,region_code,due_at,status,payload)  VALUES(100,'N01',DATEADD(minute,30,SYSUTCDATETIME()),'READY',N'benchmark-key');GOEXEC dbo.usp_SetDispatchStatus_Interpreted @dispatch_id=100,@new_status='CLAIMED';EXEC dbo.usp_SetDispatchStatus_Native      @dispatch_id=100,@new_status='READY';SELECT dispatch_id,status FROM dbo.DispatchQueue WHERE dispatch_id=100;GO-- For performance testing, repeat these calls from the same client harness,-- with the same concurrency and payload, and record client-side elapsed time.-- Do not report a speedup without measuring your environment.

4. Deliberately wrong approach: native-compile code that needs disk tables

A native procedure cannot simply reach into arbitrary disk-based tables as though native compilation were a decoration on an ordinary module. A common migration mistake is to mark an existing procedure NATIVE_COMPILATION even though it joins a memory-optimized hot table to a conventional reference table. The create/alter operation fails because the native module's supported data-access boundary is different.

DROP TABLE IF EXISTS dbo.RegionReferenceDisk;GOCREATE TABLE dbo.RegionReferenceDisk(  region_code char(3) NOT NULL CONSTRAINT PK_RegionReferenceDisk PRIMARY KEY,  region_name nvarchar(80) NOT NULL);INSERT dbo.RegionReferenceDisk VALUES('N01',N'North'),('E03',N'East');GO-- Deliberately invalid design: a natively compiled proc should not reference-- dbo.RegionReferenceDisk, which is disk-based. Keep the attempted DDL in-- source control/review rather than executing it on a shared server.

The repair depends on semantics, not on forcing compilation: keep this mixed query interpreted, split the operation so the native transaction touches only memory-optimized objects, or migrate the reference table only if it independently qualifies. Do not duplicate reference data merely to satisfy the compiler without defining synchronization and consistency rules.

5. Deployment and rollback

Schema binding means DDL deployment order matters. If a table change conflicts with a natively compiled module, alter/drop the dependent module in the same governed release plan, change the table, then recreate/alter the module and run contract tests. Current SQL Server supports ALTER PROCEDURE for native modules, but the supported native T-SQL surface still differs from interpreted T-SQL and should be re-checked for the target build.

Rollback is straightforward when you retain the interpreted contract: application callers can be redirected to the interpreted procedure while you investigate native compilation or concurrency behavior. Keep output/result/error semantics compatible so rollback is an operational choice rather than an emergency rewrite.

Authoritative references

• Native compilation of tables and stored procedures
• Supported DDL for natively compiled T-SQL modules
• Creating natively compiled stored procedures
• Plan adoption of In-Memory OLTP
• Query processing for memory-optimized tables