3 C H A P T E R 1 | Faster queries
beneficial when you have queries that scan large data sets. It also supports parallel plans when you
are using interop access with memory-optimized tables.
SQL Server 2016 also added multithreaded capabilities to the following operations affecting memory-
optimized tables:
Persistence of changes to memory-optimized tables in checkpoint files.
Log apply in a recovery operation.
MERGE operation.
Note Because of the change to the checkpoint operation, the sys.dm_db_xtp_checkpoint_stats and
sys.dm_db_xtp_checkpoint_files dynamic management views (DMVs) are significantly different from
the versions in SQL Server 2014. See "sys.dm_db_xtp_checkpoint_stats" at
https://msdn.microsoft.com/en-us/library/dn133197.aspx and "sys.dm_db_xtp_checkpoint_files" at
https://msdn.microsoft.com/en-us/library/dn133201.aspx for more information.
Introducing native client enhancements
You can now enable Multiple Active Result Sets (MARS) when connecting to a memory-optimized
table or running a natively compiled stored procedure. This way, the database engine can begin
fetching rows from a new result set before completely retrieving rows from an earlier request. To use
MARS, you must enable it explicitly in the connection string, like this:
Data Source=; Initial Catalog=; Integrated Security=SSPI;
MultipleActiveResultSets=True
For the most part, you can use MARS with memory-optimized tables just as you do with disk-based
tables, but there are a few differences:
If two statements attempt to modify the same row, a write-write conflict occurs and the new
operation fails.
Because each statement runs under SNAPSHOT isolation and is in a batch-scoped transaction, the
result of an operation performed by one statement is not visible to another statement. However, a
rollback of one batch-scoped transaction does affect other transactions in the same batch.
A user transaction cannot perform Data Definition Language (DDL) operations.
When you use a MARS-enabled connection, you can also execute natively compiled stored
procedures. If a stored procedure contains a SELECT statement, that statement can yield execution to
another statement; otherwise, the stored procedure runs completely without yielding to other
statements.
By using the BEGIN TRANSACTION statement in a T-SQL statement, you can begin a new user
transaction within the current user transaction. You can also create a save point in a transaction by
using the SAVE TRANSACTION statement or by making a call to transaction.Save().
However, you cannot use these features in a natively compiled stored procedure.
Exploring T-SQL enhancements
New enhancements to the query surface area in SQL Server 2016 make it easier to implement
memory-optimized tables. In particular, the ALTER TABLE statement supports more functionality so
that you can make changes to existing memory-optimized tables without needing to first drop a table
