Recently, I wrote an article about beginning In-Memory OLTP in SQL Server. One of the questions I received was about why I referred to the stored procedure as a natively coded stored procedure when the entire code is in T-SQL. In this article, I will explain the concept of natively compiled stored procedures and why they are called so.

When we create a natively compiled stored procedure, the SQL Server compiler converts the interpreted T-SQL, query plans, and expressions into native code. This native code is then executed when the stored procedure is called, resulting in improved performance.

To see the location of the natively compiled stored procedure, you can execute the following query in SQL Server Management Studio:

SELECT name, description
FROM sys.dm_os_loaded_modules
WHERE description = 'XTP Native DLL'

This query will display the name and description of the loaded modules, and you can identify the natively compiled stored procedure by the description ‘XTP Native DLL’.

In order to see this in action, you can create a sample database and table using the following code:

-- Create database
CREATE DATABASE InMemory
ON PRIMARY (
    NAME = InMemoryData,
    FILENAME = 'd:\data\InMemoryData.mdf',
    size = 200MB
),
FILEGROUP [InMem_FG] CONTAINS MEMORY_OPTIMIZED_DATA (
    NAME = [InMemory_InMem_dir],
    FILENAME = 'd:\data\InMemory_InMem_dir'
)
LOG ON (
    name = [InMem_demo_log],
    Filename = 'd:\data\InMemory.ldf',
    size = 100MB
)
GO

-- Create table
USE InMemory
GO
-- Create a Memory Optimized Table
CREATE TABLE DummyTable_Mem (
    ID INT NOT NULL,
    Name VARCHAR(100) NOT NULL CONSTRAINT ID_Clust_DummyTable_Mem PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT = 1000000)
) WITH (MEMORY_OPTIMIZED = ON)
GO

-- Create stored procedure
-- Inserting same 100,000 rows using InMemory Table
CREATE PROCEDURE ImMemory_Insert_test
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'english')
    DECLARE @counter AS INT = 1
    DECLARE @start DATETIME
    SELECT @start = GETDATE()

    WHILE (@counter <= 100000)
    BEGIN
        INSERT INTO dbo.DummyTable_Mem VALUES (@counter, 'SQLAuthority')
        SET @counter = @counter + 1
    END

    SELECT DATEDIFF(SECOND, @start, GETDATE()) AS [InMemory_Insert in sec]
END
GO

By executing the above script, you will be able to observe the two different DLL files in the result. These DLL files contain various parts of the natively compiled stored procedure. The database ID and object ID can be found within these files.

It is important to note that when creating a natively compiled stored procedure, two sets of file information are created – one for the stored procedure and one for the table.

For a more detailed explanation of this concept, you can refer to my friend Balmukund’s blog post on this topic.

Understanding natively compiled stored procedures in SQL Server can greatly enhance the performance of your database operations. By leveraging the power of native code execution, you can achieve significant improvements in query performance and overall system efficiency.