Database Reference
In-Depth Information
Natively-compiled stored procedures can access only memory-optimized tables. Moreover, they support a
smaller set of T-SQL features as compared to the query interop engine. We will talk about those limitations in more
detail shortly after we discuss when SQL Server compiles and how it optimizes natively-compiled stored procedures.
Optimization of Natively-Compiled Stored Procedures
Interpreted T-SQL stored procedures are compiled at 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 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, those 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 the 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—
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 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.
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 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. Those 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.
Creating Natively-Compiled Stored Procedures
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 became doomed and uncommittable.
Let's look at an example and create a memory-optimized table and natively-compiled stored procedure, as
shown in Listing 33-2. Do not focus on unfamiliar constructs in the stored procedure body. I will explain those shortly.
