Database Reference
In-Depth Information
Summary
SQL Server uses native compilation to minimize the processing overhead of 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.
In-memory OLTP is an Enterprise Edition feature that requires monitoring and tuning of systems in the post-
deployment stage. It makes in-memory OLTP a bad choice for Independent Software Vendors who develop systems
that need to be deployed to multiple customers.
Migration of existing systems could be a very time-consuming and expensive process, which requires you
to address various limitations and differences in the behavior of memory-optimized and on-disk tables and
indexes. You should perform a cost/benefit analysis, making sure that the benefits of migration overweigh its
implementation costs.
