This chapter focuses on the programmability aspects of the In-Memory OLTP Engine in SQL Server. It describes the process of native compilation, and it provides an overview of the natively compiled stored procedures and T-SQL features that are supported in In-Memory OLTP. Finally, this chapter compares the performance of several use cases that access and modify data in memory-optimized tables using natively compiled stored procedures and interpreted T-SQL with the interop engine.

Native Compilation

As you already know, memory-optimized tables can be accessed from regular T-SQL code using the query interop engine. This approach is very flexible. As long as you work within the supported feature set, the location of the data is transparent. The code does not need to know, nor does it need to worry about, if it works with on-disk or with memory-optimized tables.

Unfortunately, this flexibility comes at a cost. T-SQL is an interpreted and CPU-intensive language. Even a simple T-SQL statement requires thousands, and sometimes millions, of CPU instructions to execute. Even though the in-memory data location speeds up data access and eliminates latching and locking contentions, the overhead of T-SQL interpretation sets limits on the level of performance improvements achievable with In-Memory OLTP.

In practice, it is common to see a 2X-4X system throughput increase when memory-optimized data is accessed through the interop engine. To improve performance even further, In-Memory OLTP utilizes native compilation. As a first step, it converts any row-data manipulation and access logic into C code, which is compiled into DLLs and loaded into SQL Server’s process memory. These DLLs (one per table) consist of native CPU instructions, and they execute without any further code interpretation overhead of T-SQL statements.

Consider the simple situation where you need to read the value of a fixed-length column from a data row. In the case of on-disk tables, SQL Server obtains the starting offset and length of the column from the system catalogs, and it performs the required manipulations to convert the sequence of bytes to the required data type. With memory-optimized tables, the DLL already knows the column offset and data type. SQL Server can read data from a pre-defined offset in a row using a pointer of the correct data type without any further overhead involved. As you can guess, this approach dramatically reduces the number of CPU instructions required for the operation.

On the flip side, this approach brings some limitations. You cannot change the format of a row after the DLL is generated. The compiled code would not know anything about the changes. This problem is more complicated than it seems, and a simple recompilation of the DLL does not address it.

Again, consider the situation where you need to add another nullable column to a table. This is a metadata-level operation for on-disk tables, which does not change the data in existing table rows. T-SQL would be able to detect that column data is not present by analyzing the various data row properties at runtime.

The situation is far more complicated in the case of memory-optimized tables and natively compiled code. It is easy to generate a new version of the DLL that knows about the new data column; however, that is not enough. The DLL needs to handle different versions of rows and different data formats depending on the presence of column data. While this is technically possible, it adds extra logic to the DLL, which leads to additional processing instructions, which slows data access. Moreover, the logic to support multiple data formats remains in the code forever, degrading performance even further with each table alteration.

While, technically speaking, it is possible to convert all existing data rows to the new format, this operation requires exclusive access to the table, which violates In-Memory OLTP lock- and latch-free principles and is not supported in SQL Server 2014.

To reduce the overhead of the T-SQL interpretation even further, the In-Memory OLTP Engine allows you to perform native compilation of the stored procedures. These stored procedures are compiled in the same way as table-related DLLs and are also loaded to the SQL Server process memory.

Native compilation utilizes both the SQL Server and In-Memory OLTP Engines. As a first step, SQL Server parses the T-SQL code and, in the case of stored procedures, it generates an execution plan using the Query Optimizer. At the end of this stage, SQL Server generates a structure called MAT (Mixed Abstract Tree), which represents metadata, imperative logic, expressions, and query plans. I will discuss how SQL Server optimizes natively compiled stored procedures later in this chapter.

As a next step, In-Memory OLTP transforms MAT to another structure called PIT (Pure Imperative Tree), which is used to generate source code that is compiled and linked into the DLL.

Figure 6-1 illustrates the process of native compilation in SQL Server.

The code generated for native compilation uses the plain C language and is very efficient. It is very hard to read, however. For example, every method is implemented as a single function, which does not call other functions but rather implements its code inline using GOTO as a control flow statement. The intention has never been to generate human-readable code; it is used as the source for native compilation only.

Binary DLL files are not persisted in a database backup. SQL Server recreates table-related DLLs on database startup and stored procedures-related DLLs at the time of the first call. This approach mitigates security risks from hackers, who can substitute DLLs with malicious copies. It is important to remember this behavior because it can add overhead at database startup time and change the execution plans of natively compiled stored procedures after a database restart.

SQL Server places binary DLLs and all other native compilation-related files in an XTP subfolder under the main SQL Server data directory. It groups files on a per-database basis by creating another level of subfolders. Figure 6-2 shows the content of the folder for the database (with ID=5), which contains the memory-optimized tables and a natively compiled stored procedures you created in previous chapters of this book.

All of the file names start with the prefix xtp_ followed either by the p (stored procedure) or t (table) character, which indicates the object type. The two last parts of the name include the database and object IDs for the object.

File extensions determine the type of the file, such as:

• *.mat.xml files store an XML representation of the MAT structure.
• *.c files are the source file generated by the C code generator.
• *.obj are the object files generated by the C compiler.
• *.pub are symbol files produced by the C compiler.
• *.out are log files from the C compiler.
• *.dll are natively compiled DLLs generated by the C linker. Those files are loaded into SQL Server memory and used by the In-Memory OLTP Engine.

Listing 6-1 shows how to obtain a list of the natively compiled objects loaded into SQL Server memory. It also returns the list of tables and stored procedures from the database to show the correlation between a DLL file name and object IDs.

select
    s.name + ’.’ + o.name as [Object Name]
    ,o.object_id
from
     (
        select schema_id, name, object_id
        from sys.tables
        where is_memory_optimized = 1
        union all
        select schema_id, name, object_id
        from sys.procedures
    ) o join sys.schemas s on
        o.schema_id = s.schema_id;

select base_address, file_version, language, description, name
from sys.dm_os_loaded_modules
where description = ’XTP Native DLL’;

Figure 6-3 illustrates the output of the code.

Natively Compiled Stored Procedures

Natively compiled stored procedures are the stored procedures that are compiled into native code. They are extremely efficient, and they can provide major performance improvements when working with memory-optimized tables, compared to interpreted T-SQL statements, which access those tables through the query interop component.

Creating Natively Compiled Stored Procedures

As you already know, you can create natively compiled stored procedures using the regular CREATE PROCEDURE statement and T-SQL language. However, those procedures have several additional options that need to be specified. Listing 6-2 shows the structure of natively compiled stored procedures along with those options.

create proc dbo.NativelyCompiledProc
(
    /* Parameters */
    @Param1 int not null = 1
    ,@Param2 int
)
with
    native_compilation    -- Indicates natively compiled SP
    ,schemabinding    -- Required
    ,execute as owner    -- execute as OWNER/SELF/USER is required
as
-- Natively compiled SPs are executed as atomic blocks (all or nothing)
begin atomic with
(
    transaction isolation level = snapshot    -- Isolation Level is required
    ,language = N’English’    -- Required langugage setting for SP
    ,delayed_durability = off    -- Optional
    ,datefirst = 7    -- Optional
    ,dateformat = ’mdy’    -- Optional
)
    /* Stored Procedure Body */
end

You can define parameters of natively compiled stored procedures the same way as with T-SQL procedures. However, natively compiled stored procedures allow you to specify if parameters are required and must be provided at the time of a call using the NOT NULL construct in the definition. SQL Server raises an error if you do not provide their values at the time of the call.

All natively compiled stored procedures must be schema bound and have the security context EXECUTE AS OWNER/SELF/USER specified. The default EXECUTE AS CALLER context is not supported to avoid the overhead of per-statement permission checks during the execution.

Two other required options include the transaction isolation level and the language setting, which controls a message’s language and default date format. Natively compiled stored procedures do not use the runtime SET LANGUAGE session option, relying on the LANGUAGE setting instead.

You can control date format, first day of the week, and delayed durability of a stored procedure using the DATEFORMAT, DATEFIRST, and DELAYED_DURABILITY settings, respectively.

Natively compiled stored procedures are executed as the atomic blocks, which is all or nothing approach; either all statements in the procedure succeed or all of them fail. I will discuss how atomic blocks work later in the chapter.

As mentioned, you can define the natively compiled stored procedure body pretty much the same way as regular T-SQL procedures. However, the natively compiled stored procedures support only a limited set of T-SQL constructs. Let’s look at the supported features and limitations in different T-SQL areas in detail.

Supported T-SQL Features

One of the biggest limitations of natively compiled stored procedures is that they can access only memory-optimized tables. The only option to join data from memory-optimized and on-disk tables is to use the interpreted T-SQL and interop engine.

The following T-SQL features and constructs are supported and can be used in natively compiled stored procedures.

Control Flow

The following control flow options are supported:

• IF and WHILE.
• Assigning a value to a variable with the SELECT and SET operators.
• RETURN.
• TRY/CATCH/THROW (RAISERROR is not supported). It is recommended that you use a single TRY/CATCH block for the entire stored procedure for better performance.
• It is possible to declare variables as NOT NULL as long as they have an initializer as part of the DECLARE statement.

Query Surface Area

The following query surface area functions are supported:

• SELECT, INSERT, UPDATE, and DELETE. However, you cannot use multiple VALUE clauses with the single INSERT statement.
• CROSS JOIN and INNER JOIN are the only join types supported. Moreover, you can use joins only with SELECT operators.
• Expressions in the SELECT list and the WHERE and HAVING clauses are supported as long as they use supported operators.
• IS NULL and IS NOT NULL.
• GROUP BY is supported with the exception of grouping by string or binary data.
• TOP and ORDER BY. However, you cannot use WITH TIES and PERCENT in the TOP clause. Moreover, the TOP operator is limited to 8,192 rows when the TOP <constant> is used, or even a lesser number of rows in the case of joins. You can address this last limitation by using a TOP <variable> approach. However, it is less efficient in terms of performance.
• INDEX, FORCESCAN, FORCESEEK, FORCE ORDER, INNER LOOP JOIN, and OPTIMIZE FOR hints.

Note that the DISTINCT operator is not supported.

Operators

The following operators are supported:

• Comparison operators, such as =, <, <=, >, >=, <> and BETWEEN.
• Unary and binary operators, such as +, -, *, /, %. Note that + operators are supported for both numbers and strings.
• Bitwise operators, such as &, |, ~, ^.
• Logical operators, such as AND, OR, and NOT. However, the OR and NOT operators are not supported in the WHERE and HAVING clauses of the query.

Build-In Functions

The following build-in functions are supported:

• Math functions: ACOS, ASIN, ATAN, ATN2, COS, COT, DEGREES, EXP, LOG, LOG10, PI, POWER, RAND, SIN, SQRT, SQUARE, and TAN.
• Date/time functions: CURRENT_TIMESTAMP, DATEADD, DATEDIFF, DATEFROMPARTS, DATEPART, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DAY, EOMONTH, GETDATE, GETUTCDATE, MONTH, SMALLDATETIMEFROMPARTS, SYSDATETIME, SYSUTCDATETIME, and YEAR.
• String functions: LEN, LTRIM, RTRIM, and SUBSTRING.
• Error functions: ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE.
• NEWID and NEWSEQUENTIALID.
• CAST and CONVERT. However, it is impossible to convert between a non-Unicode and a Unicode string.
• ISNULL.
• SCOPE_IDENTITY.
• You can use @@ROWCOUNT within a natively-compiled stored procedure; however, its value is reset to 0 at the beginning and end of the procedure.

Atomic Blocks

Natively compiled stored procedures execute as atomic blocks, which is an all or nothing approach; either all statements in the procedure succeed or all of them fail.

When a natively compiled stored procedure is called outside of the context of an active transaction, it starts a new transaction and either commits or rolls it back at the end of the execution.

In cases where a procedure is called in the context of an active transaction, SQL Server creates a savepoint at the beginning of the procedure’s execution. In case of an error in the procedure, SQL Server rolls back the transaction to the created savepoint. Based on the severity and type of the error, the transaction is either going to be able to continue and commit or become doomed and uncommittable.

Let’s create a memory-optimized table and natively compiled stored procedure, as shown in Listing 6-3.

create table dbo.MOData
(
    ID int not null
        primary key nonclustered
        hash with (bucket_count=10),
    Value int null
)
with (memory_optimized=on, durability=schema_only);

insert into dbo.MOData(ID, Value)
values(1,1), (2,2)
go

create proc dbo.AtomicBlockDemo
(
    @ID1 int not null
    ,@Value1 bigint not null
    ,@ID2 int
    ,@Value2 bigint
)
with native_compilation, schemabinding, execute as owner
as
begin atomic
with (transaction isolation level = snapshot, language=N’English’)

    update dbo.MOData set Value = @Value1 where ID = @ID1;

    if @ID2 is not null
        update dbo.MOData set Value = @Value2 where ID = @ID2;
end;

At this point, the MOData table has two rows with values (1,1) and (2,2). As a first step, let’s start the transaction and call a stored procedure twice, as shown in Listing 6-4.

begin tran
    exec dbo.AtomicBlockDemo 1, -1, 2, -2
    exec dbo.AtomicBlockDemo 1, 0, 2, 999999999999999

The first call of the stored procedure succeeds, while the second call triggers an arithmetic overflow error as shown:

Msg 8115, Level 16, State 0, Procedure AtomicBlockDemo, Line 49

Arithmetic overflow error converting bigint to data type int.

You can check that the transaction is still active and committable with this select: SELECT @@TRANCOUNT as [@@TRANCOUNT], XACT_STATE() as [XACT_STATE()]. It returns the following results:

@@TRANCOUNT    XACT_STATE()
-----------    ------------
1              1

If you commit the transaction and check the content of the table, you will see that the data reflects the changes caused by the first stored procedure call. Even though the first update statement from the second call succeeded, SQL Server rolled it back because the natively compiled stored procedure executed as an atomic block. You can see the data in the MOData table:

ID          Value
----------- -----------
1           -1
2           -2

As a second example, let’s trigger a critical error, which dooms the transaction, making it uncommittable. One such situation is a write/write conflict, when multiple sessions are trying to update the same rows. You can trigger it by executing the code in Listing 6-5 in two different sessions.

begin tran
    exec dbo.AtomicBlockDemo 1, 0, null, null

When you run the code in the second session, it triggers the following exception:

Msg 41302, Level 16, State 110, Procedure AtomicBlockDemo, Line 13

The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.

Msg 3998, Level 16, State 1, Line 1

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

If you check @@TRANCOUNT in the second session, you will see that SQL Server terminates the transaction.

@@TRANCOUNT
-----------
0

Finally, it is worth mentioning that atomic blocks are an In-Memory OLTP feature and are not supported in T-SQL stored procedures.

Optimization of Natively Compiled Stored Procedures

Interpreted T-SQL stored procedures are compiled at the time of first execution. Additionally, they can be recompiled after they are evicted from plan cache and in a few other cases, such as outdated statistics, changes in database schema, or recompilation, which are explicitly requested in the code.

This behavior is different from natively compiled stored procedures, which are compiled at creation time. They are never recompiled, only with the exception of SQL Server or a database restart. In these cases, recompilation occurs at the time of the first stored procedure call.

SQL Server does not sniff parameters at the time of compilation, optimizing statements for UNKNOWN values. It uses memory optimized table statistics during optimization. However, as you already know, these statistics are not updated automatically, and they can be outdated at that time.

Fortunately, cardinality estimation errors have a smaller impact on performance in the case of natively compiled stored procedures. Contrary to on-disk tables, where such errors can lead to highly inefficient plans due to an incorrect index choice and, therefore, a high number of Key or RID Lookup operations, all indexes in memory-optimized tables reference the same data row and, in a nutshell, are covering indexes. Moreover, errors will not affect the choice of join strategy—the inner nested loop is the only physical join type supported in natively compiled stored procedures in the first release of In-Memory OLTP.

Outdated statistics at the time of compilation, however, can still lead to inefficient plans. One such example is a query with multiple predicates on indexed columns. SQL Server needs to know the index’s selectivity to choose the most efficient one. Another example is the incorrect choice of inner and outer input for the nested loop join, which you saw in Chapter 4.

It is better to recompile natively compiled stored procedures if the data in the table has significantly changed. You can do it with the following actions:

1. Update the statistics to reflect the current data distribution in the table(s).
2. Script permissions assigned to natively compiled stored procedures.
3. Drop and recreate procedures. These actions force recompilation.
4. Assign required permissions to the procedures.

Finally, it is worth mentioning that the presence of natively compiled stored procedures requires you to adjust the deployment process in the system. It is common to create all database schema objects, including tables and stored procedures, at the beginning of deployment. While the time of deployment does not matter for T-SQL procedures, such a strategy compiles natively compiled stored procedures at a time when database tables are empty. You should recompile (recreate) natively compiled procedures later, after the tables are populated with data and statistics are up to date.

Interpreted T-SQL and Memory-Optimized Tables

The query interop component provides transparent, memory-optimized table access to interpreted T-SQL code. In the interpreted mode, SQL Server treats memory-optimized tables pretty much the same way as on-disk tables. It optimizes queries and caches execution plans, regardless of where the table is located. The same set of operators is used during query execution. From a high level, when the operator’s GetRow() method is called, it is routed either to the Storage Engine or to the In-Memory OLTP Engine, depending on the underlying table type.

Most T-SQL features are supported in interpreted mode. There are still a few exceptions, however:

• TRUNCATE TABLE.
• MERGE operator with memory-optimized table as the target.
• Context connection from CLR code.
• Referencing memory-optimized tables in indexed views. You can reference memory-optimized tables in partitioned views, combining data from memory-optimized and on-disk tables.
• DYNAMIC and KEYSET cursors, which are automatically downgraded to STATIC.
• Cross-database queries and transactions.
• Linked servers.

As you can see, the list of limitations is pretty small. However, the flexibility of query interop access comes at a cost. Natively compiled stored procedures are usually more efficient compared to their interpreted T-SQL counterparts. In some cases, such as joins between memory-optimized and on-disk tables, query interop is the only choice; however, it is usually preferable to use natively compiled stored procedures when possible.

Performance Comparison

Let’s run several tests comparing performance of several use cases that work with memory-optimized tables using natively compiled stored procedures and the interop engine.

Let’s create two memory-optimized tables using a schema_only durability option to avoid any I/O and transaction logging overhead during the tests. You can see the code in Listing 6-6, which also creates a numbers’ table and populates it with the values.

create table dbo.Customers
(
    CustomerId int not null
        primary key nonclustered
        hash with (bucket_count=200000),
    Name nvarchar(255)
        collate Latin1_General_100_BIN2 not null,
    CreatedOn datetime2(0) not null
        constraint DEF_Customers_CreatedOn
        default sysutcdatetime(),
    Placeholder char(200) not null,

    index IDX_Name nonclustered(Name)
)
with (memory_optimized=on, durability=schema_only);

create table dbo.Orders
(
    OrderId int not null
        primary key nonclustered
        hash with (bucket_count=5000000),
    CustomerId int not null,
    OrderNum varchar(32)
        collate Latin1_General_100_BIN2 not null,
    OrderDate datetime2(0) not null
        constraint DEF_Orders_OrderDate
        default sysutcdatetime(),
    Amount money not null,
    Placeholder char(200) not null,

    index IDX_CustomerId
    nonclustered hash(CustomerId)
    with (bucket_count=200000),

    index IDX_OrderNum nonclustered(OrderNum)
)
with (memory_optimized=on, durability=schema_only);

create table dbo.Numbers
(
    Num int not null
        Constraint PK_Numbers
        primary key clustered
);

;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as t1 cross join N1 as t2) -- 4 rows
,N3(C) as (select 0 from N2 as t1 cross join N2 as t2) -- 16 rows
,N4(C) as (select 0 from N3 as t1 cross join N3 as t2) -- 256 rows
,N5(C) as (select 0 from N4 as t1 cross join N4 as t2) -- 65,536 rows
,N6(C) as (select 0 from N5 as t1 cross join N3 as t2) -- 1,048,576 rows
,Ids(Id) as (select row_number() over (order by (select null)) from N6)
insert into dbo.Numbers(Num)
    select Id from Ids;

As the first step, we will measure INSERT performance using three different approaches and batches of different sizes. The first two stored procedures, InsertCustomers_Row and InsertCustomers_NativelyCompiled, will run INSERT statements on per-row basis using the interop engine and native compilation, respectively. The third stored procedure, InsertCustomers_Batch, will insert all rows in the single batch through the interop engine. Listing 6-7 shows the implementation of the stored procedures.

create proc dbo.InsertCustomers_Row
(
    @NumCustomers int
)
as
begin
    set nocount on
    set xact_abort on

    declare
        @I int = 1;

    begin tran
        while @I <= @NumCustomers
        begin
            insert into dbo.Customers(CustomerId,Name,Placeholder)
            values(@I,N’Customer ’ + convert(nvarchar(10),@I),’Data’);

            set @I += 1;
        end;
    commit
end
go

create proc dbo.InsertCustomers_Batch
(
    @NumCustomers int
)
as
begin
    set nocount on
    set xact_abort on

    if @NumCustomers > 1048576
    begin
        raiserror(’@NumCustomers should not exceed 1,048,576’,10,1);
        return;
    end;

    begin tran
        insert into dbo.Customers(CustomerId,Name,Placeholder)
            select Num, N’Customer ’ + convert(nvarchar(10),Num),’Data’
            from dbo.Numbers
            where Num <= @NumCustomers
    commit
end
go

create proc dbo.InsertCustomers_NativelyCompiled
(
    @NumCustomers int not null
)
with native_compilation, schemabinding, execute as owner
as
begin atomic with
(
    transaction isolation level = snapshot
    ,language = N’English’
)
    declare
        @I int = 1;

    while @I <= @NumCustomers
    begin
        insert into dbo.Customers(CustomerId,Name,Placeholder)
        values(@I,N’Customer ’ + convert(nvarchar(10),@I), ’Data’);

        set @I += 1;
    end;
end;

Table 6-1 shows the execution time of each stored procedure for the batches of 10,000, 50,000, and 100,000 rows in my environment. As you can see, the natively compiled stored procedure is about four times faster at row-by-row inserts and about 30-40 percent faster even compared to batch inserts through the interop engine.

As the next step, let’s compare performance of UPDATE operations. Listing 6-8 shows a natively compiled stored procedure that updates 50 percent of the rows in the Customers table.

create proc dbo.UpdateCustomers
(
    @Placeholder char(100) not null
)
with native_compilation, schemabinding, execute as owner
as
begin atomic with
(
    transaction isolation level = snapshot
    ,language = N’English’
)
    update dbo.Customers
    set Placeholder = @Placeholder
    where CustomerId % 2 = 0;
end;

Table 6-2 shows the execution time of the UpdateCustomers stored procedure and the same UPDATE statement executed through the interop engine. As you see, the natively compiled stored procedure is about three times faster than the interop approach.

In the next step, let’s compare the performance of a SELECT query that joins data from the Customers and Orders tables and performs sorting and aggregations. I have populated the  Orders table with 1,000,000 rows evenly distributed between 100,000 customers before the test. Listing 6-9 shows the natively compiled stored procedure with the query.

create proc dbo.GetTopCustomers
with native_compilation, schemabinding, execute as owner
as
begin atomic with
(
    transaction isolation level = snapshot
    ,language = N’English’
)
    select top 10
        c.CustomerId, c.Name, count(o.OrderId) as [Order Cnt]
        ,max(o.OrderDate) as [Most Recent Order Date]
        ,sum(o.Amount) as [Total Amount]
    from
        dbo.Customers c join dbo.Orders o on
            c.CustomerId = o.CustomerId
    group by
        c.CustomerId, c.Name
    order by
        sum(o.Amount) desc;
end;

Table 6-3 shows the execution times of the stored procedure and the same query executed through the interop engine. As you see, the natively compiled stored procedure is about eight times faster in this scenario.

It is very important to remember, however, that natively compiled stored procedures do not support hash and merge joins, which could outperform nested loop joins on large and unsorted inputs.

Finally, let’s compare the performance of DELETE operations. Listing 6-10 shows a natively compiled stored procedure that deletes the data from both tables.

create proc dbo.DeleteCustomersAndOrders
with native_compilation, schemabinding, execute as owner
as
begin atomic with
(
    transaction isolation level = snapshot
    ,language = N’English’
)
    delete from dbo.Orders;
    delete from dbo.Customers;
end;

Table 6-4 shows the execution times of the stored procedure and DELETE statements executed through the interop engine. In both cases, the Customers and Orders tables were populated with the same data, which is 100,000 and 1,000,000 rows respectively. Again, the natively compiled stored procedure is faster.

As you have seen, native compilation provides significant performance improvements compared to the interop engine. It is beneficial to use it as long as the limitations do not prevent you from implementing the logic, and the additional administration and maintenance overhead is acceptable.

Lastly, you should remember that SQL Server 2014 does not support parallel execution plans for the statements that access memory-optimized tables. It makes In-Memory OLTP the bad candidate for Data Warehouse workload with the large scans and complex aggregations. We will discuss those scenarious in greater details in Chapter 11.

Memory-Optimized Table Types and Variables

SQL Server allows you to create memory-optimized table types. Table variables of these types are called memory-optimized table variables. In contrast to regular disk-based table variables, memory-optimized table variables live in memory only and do not utilize tempdb.

Memory-optimized table variables provide great performance. They can be used as a replacement for disk-based table variables and, in some cases, temporary tables. Obviously, they have the same set of functional limitations as memory-optimized tables.

Contrary to disk-based table types, you can define indexes on memory-optimized table types. The same statistics-related limitations still apply; however, as discussed, due to the nature of indexes on memory-optimized tables, cardinality estimation errors yield a much lower negative impact compared to those of on-disk tables.

SQL Server does not support inline declaration of memory-optimized table variables. For example, the code shown in Listing 6-11 will not compile and it will raise an error. The reason behind this limitation is that SQL Server compiles a DLL for every memory-optimized table type, which will not work in the case of inline declaration.

declare
    @IDList table
     (
        ID int not null
            primary key nonclustered hash
            with (bucket_count=10000)
    )
    with (memory_optimized=on)

Msg 319, Level 15, State 1, Line 91

Incorrect syntax near the keyword ’with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

You should define and use a memory-optimized table type instead, as shown in Listing 6-12.

create type dbo.mtvIDList as table
(
    ID int not null
        primary key nonclustered hash
        with (bucket_count=10000)
)
with (memory_optimized=on)
go

declare
    @IDList dbo.mtvIDList

You can pass memory-optimized table variables as table-valued parameters (TVP) to natively compiled and regular T-SQL procedures. As with on-disk based table-valued parameters, it is a very efficient way to pass a batch of rows to a T-SQL routine.

You can use memory-optimized table variables to imitate row-by-row processing using cursors, which are not supported in natively compiled stored procedures. Listing 6-13 illustrates an example of using a memory-optimized table variable to imitate a static cursor. Obviously, it is better to avoid cursors and use set-based logic if at all possible.

create type dbo.MODataStage as table
(
    ID int not null
        primary key nonclustered
        hash with (bucket_count=1000),
    Value int null
)
with (memory_optimized=on)
go

create proc dbo.CursorDemo
with native_compilation, schemabinding, execute as owner
as
begin atomic
with
(
    transaction isolation level = snapshot
    ,language=N’English’
)
    declare
        @tblCursor dbo.MODataStage
        ,@ID int = -1
        ,@Value int
        ,@RC int = 1

    /* Staging data in temporary table to imitate STATIC cursor */
    insert into @tblCursor(ID, Value)
        select ID, Value
        from dbo.MOData

    while @RC = 1
    begin
        select top 1 @ID = ID, @Value = Value
        from @tblCursor
        where ID > @ID
        order by ID

        select @RC = @@rowcount
        if @RC = 1
        begin
            /* Row processing */
            update dbo.MOData set Value = Value * 2 where ID = @ID
        end
    end
end

Summary

SQL Server uses native compilation to minimize the processing overhead of the interpreted T-SQL language. It generates separate DLLs for every memory-optimized object and loads it into process memory.

SQL Server supports native compilation of regular T-SQL stored procedures. It compiles them into DLLs at creation time or, in the case of a server or database restart, at the time of the first call. SQL Server optimizes natively compiled stored procedures and embeds an execution plan into the code. That plan never changes unless the procedure is recompiled after a SQL Server or database restart. You should drop and recreate procedures if data distribution has been significantly changed after compilation.

While natively compiled stored procedures are incredibly fast, they support a limited set of T-SQL language features. You can avoid such limitations by using interpreted T-SQL code that accesses memory-optimized tables through the query interop component of SQL Server. Almost all T-SQL language features are supported in this mode.

Memory-optimized table types and memory-optimized table variables are the in-memory analog of table types and table variables. They live in memory only, and they do not use tempdb. You can use memory-optimized table variables as a staging area for the data and to pass a batch of rows to a T-SQL routine. Memory-optimized table types allow you to create indexes similar to memory-optimized tables.