top of page

Natively compiled stored procedures

A memory-optimized table is a highly optimized data structure that SQL Server uses to store data completely in memory without paging to disk. It uses hash and nonclustered ordered indexes to support faster data access from memory than traditional B-tree indexes. SQL Server maintains a copy of the data on disk to guarantee transaction durability and to reload tables during database recovery.

To further optimize query performance, you can implement natively compiled stored procedures as long as the stored procedure accesses memory-optimized tables only. A natively compiled stored procedure is a stored procedure compiled into machine language for faster execution, lower latency, and lower CPU utilization.

The use of memory-optimized tables in this scenario eliminates contention between read and write operations and retrieves data with lower latency, while the use of natively compiled stored procedures enables code to execute faster.

 

With memory-optimized tables, the database engine retrieves data more efficiently with reduced contention and natively compiled stored procedures execute code more efficiently and faster.

Natively compiled stored procedures are compiled at the time of creation, unlike interpreted stored procedures that compile at first execution. Furthermore, natively compiled stored procedures can access memory-optimized tables only. Native compilation translates the stored procedure code first into C code, and then into machine language, which enables the business logic to both execute and access data faster and more efficiently.

you can use natively compiled stored procedures to get better performance when operating on memory-optimized tables. You use them for:

  • Applications for which obtaining the best possible performance is a requirement

  • Queries that execute frequently

  • Tasks that must perform as fast as possible

If you have a lot of rows to process and a lot of logic to apply, the natively compiled stored procedure performs faster than an interpreted stored procedure. It is also good when you need to perform any of the following tasks:

  • Aggregation

  • Nested loop join

  • Multi-statement SELECT, INSERT, UPDATE, or DELETE operations

  • Complex expressions

  • Procedural logic, such as conditional statements and loops

It is not typically the best option when you need to process only a single row.

 

you need to specifically enable the collection of execution statistics by using one of the following system stored procedures:

  • sys.sp_xtp_control_proc_exec_stats Use this system stored procedure to enable statistics collection for your SQL Server instance at the procedure level.

  • sys.sp_xtp_control_query_exec_stats Use this system stored procedure to enable statistics collection at the query level for selected natively compiled stored procedures.

Keep in mind that enabling the collection of execution statistics can have an adverse effect on the performance of natively compiled stored procedures. Rather than collect statistics globally for an instance, you should collect statistics for selected natively compiled stored procedures only to reduce this impact.

 

the following portions of the code are specific to native compilation:

  • WITH NATIVE_COMPILATION This clause is required to create a natively compiled stored procedure.

  • SCHEMABINDING This option is required to bind the natively compiled stored procedure to the object that it references. Consequently, you cannot drop tables referenced in the procedure code. Furthermore, you cannot use the wildcard (*)operator, and instead must reference column names explicitly in a SELECTstatement.

  • BEGIN ATOMIC...END You use this option to create an atomic block, which is ablock of T-SQL statements that succeed or fail together. A natively compiled storedprocedure can have only one atomic block. Starting an atomic block creates atransaction if one does not yet exist or creates a savepoint if there is an existingtransaction. An atomic block must include options defining the isolation level andlanguage like this: WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT,LANGUAGE = N’English’).

You can also specify delayed durability for a natively compiled stored procedure.

ALTER DATABASE DB_IMOLTP SET DELAYED_DURABILITY = FORCED; BEGIN TRANSACTION; INSERT INTO Examples.Order_IM_Hash

(OrderId, OrderDate, CustomerCode) VALUES (1, getdate(), 'cust1'); COMMIT TRANSACTION WITH (DELAYED_DURABILITY = OFF); GO ALTER DATABASE DB_IMOLTP SET DELAYED_DURABILITY = ALLOWED; BEGIN TRANSACTION; INSERT INTO Examples.Order_IM_Hash (OrderId, OrderDate, CustomerCode) VALUES (2, getdate(), 'cust2'); COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON); CREATE PROCEDURE Examples.OrderInsert_NC_DD @OrderID INT, @CustomerCode NVARCHAR(10) WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN

ATOMIC WITH (DELAYED_DURABILITY = ON, TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English') DECLARE @OrderDate DATETIME = getdate(); INSERT INTO Examples.Order_IM

(OrderId, OrderDate,CustomerCode) VALUES (@OrderID, @OrderDate, @CustomerCode); END; GO ALTER DATABASE DB_IMOLTP SET DELAYED_DURABILITY = DISABLED;

If you set delayed durability at the database level, every transaction that commits on the database is delayed durable by default, although you can override this behavior at the transaction level.

Similarly, if the database is durable, you can configure the database to allow delayed durable transactions and then explicit define a transaction as delayed durable.

If you prefer, you can disable delayed durability and prevent delayed durable transactions entirely regardless of the transaction’s commit level.

 

Example:

Case:

Question:

Answer:

bottom of page