In-Memory Optimization and the Buffer Pool Extension in SQL Server 2014

This chapter is from the book

Microsoft SQL Server 2014 Unleashed

Learn More Buy

This chapter is from the book

This chapter is from the book 

Microsoft SQL Server 2014 Unleashed

Learn More Buy

Using Memory-Optimized Tables

Now that we’ve discussed how to create memory-optimized tables and their indexes, you may be wondering how you use them. Memory-optimized tables can be accessed in two different ways: either using interpreted Transact-SQL through interop, or by creating and using natively compiled stored procedures.

Interpreted T-SQL Support for In-Memory OLTP

With just a handful of exceptions, you can access memory-optimized tables just like disk-based tables using interpreted T-SQL queries or DML operations (SELECT, INSERT, UPDATE, or DELETE), ad hoc batches, and SQL modules such as stored procedures, table-value functions, triggers, and views. Interpreted T-SQL refers to any T-SQL batches or stored procedures other than a natively compiled stored procedure.

Interpreted T-SQL access to memory-optimized tables is referred to as interop access. Interpreted T-SQL access is recommended for ad hoc queries and administrative tasks, reporting queries which typically use constructs not available in natively compiled stored procedures (such as windowing functions), or to migrate performance-critical parts of your application to memory-optimized tables, with minimal (or no) application code changes.

However, the following T-SQL constructs are not supported for interop access to data in memory-optimized tables:

• TRUNCATE TABLE
• MERGE with memory-optimized table as the target
• DYNAMIC and KEYSET cursors (they will automatically be downgraded to STATIC cursors)
• Access from CLR modules using context connection
• Referencing memory-optimized tables in an indexed view
• Locking hints: HOLDLOCK, PAGLOCK, ROWLOCK, TABLOCK, TABLOCKX, UPDLOCK, XLOCK, NOWAIT
• Isolation level hints: READCOMMITTED, READCOMMITTEDLOCK, READPAST, READUNCOMMITTED
• Cross-database queries and transactions
• Linked servers

As you can see, there are not a whole lot of restrictions on what you can do against memory-optimized tables from interpreted SQL and you may see performance improvements in certain types of applications just from migrating tables from disk-based to memory-optimized tables and reaping the benefits of the improved data access speed and elimination of latching and locking contention. However, if you then migrate your interpreted stored procedures to natively compiled stored procedures, you should see even further performance improvement.

Native Compilation

Native compilation allows faster data access and more efficient query execution than normal T-SQL stored procedures which are processed as interpreted Transact-SQL. Although you may see a 2X-3X performance improvement accessing memory-optimized tables using the interop engine, the overhead of using interpreted SQL is the limiting factor in the performance improvements that can be achieved with In-Memory OLTP. Even simple interpreted T-SQL statements may still require thousands, and possibly millions, of CPU instructions to execute. Significantly greater performance can be achieved utilizing native compilation.

The first step in native compilation is to convert the row-data manipulation and access logic into C code, which is then compiled into DLLs (one per each memory-optimized table) and loaded into SQL Server process memory. These DLLs consist of native CPU instructions which execute without any additional overhead of T-SQL code interpretation.

To see how performance benefits from native compilation, consider the simple operation where you need to read the value of a fixed-length column from a data row. For on-disk tables, SQL Server queries the system catalogs to obtain the starting offset and length of the column in the data row, and then performs the necessary operations to convert the sequence of bytes stored in the column to the required data type. With memory-optimized tables, the compiled DLL already knows the column offset and data type and the memory-optimized OLTP engine can read the data from the pre-defined offset in the row using a pointer of the proper data type without any further overhead required. This method significantly reduces the number of CPU instructions required for the operation.

To reduce the overhead of interpreted T-SQL even further, the In-Memory OLTP engine allows you to natively compile stored procedures. Natively compiled stored procedures are compiled to DLLs and loaded into SQL Server process memory in the same way as the table-related DLLs. The main difference between interpreted stored procedures and natively compiled stored procedures is that an interpreted stored procedure isn’t compiled until its first execution whereas a natively compiled stored procedure is compiled when it is created. The compile process first translates the stored procedure T-SQL code into C code, and then into machine language. The code is stored as a DLL in a special folder on the SQL Server machine.

In addition to compiling memory-optimized tables and natively compiled stored procedures to native DLLs at create time, the table DLLs are recompiled after the database or server is restarted and natively compiled stored procedure DLLs are recompiled upon the first execution after restart. The information necessary to recreate the DLLs is stored in the database metadata. The binary DLLs are not stored in the database nor persisted in database backups. The binary DLLs and other native compilation-related files (which are kept for troubleshooting and supportability purposes) are stored in the XTP subfolder under the main SQL Server data directory. Files are grouped together in subfolders at the database level (the folder names match the database ID).

You do not need to maintain the files that are generated by native compilation. SQL Server automatically removes generated files that are no longer needed. However, if you are curious, you can view the name and location of the DLL files created for your memory-optimized tables and natively compiled procedures with the following query:

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

/* output
name                                         description
-------------------------------------------- ---------------
d:\SQL2014\Data\xtp\6\xtp_t_6_277576027.dll  XTP Native DLL
d:\SQL2014\Data\xtp\6\xtp_t_6_341576255.dll  XTP Native DLL
d:\SQL2014\Data\xtp\6\xtp_p_6_565577053.dll  XTP Native DLL
*/

To match the DLL files with the underlying table or stored procedure, the last bit of the file name is the object ID. You can identify the object ID of your memory-optimized tables and natively compiled stored procedures with the following query:

SELECT s.name + '.' + o.name as ObjectName, 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
              ) as o
  JOIN sys.schemas s
  ON o.schema_id = s.schema_id
go

/* output
ObjectName      object_id
--------------- -----------
dbo.InMemTab    341576255
dbo.InMemInsert 565577053
*/

For example, you can see that memory-optimized table InMemTab has object ID 341576255, which corresponds to DLL xtp_t_6_341576255.dll.

Natively Compiled Stored Procedures

Natively compiled stored procedures can only access memory-optimized tables and currently support a smaller subset of T-SQL features as compared to the query interop engine.

The performance benefit of using natively compiled stored procedures increases with the number of rows and the complexity of the procedure code, or if the number of executions per second of the stored procedure is very high. If a procedure needs to process just a single row only occasionally, it’s unlikely to benefit much from native compilation. A natively compiled stored procedure will likely exhibit much better performance improvement than an interpreted procedure if it uses aggregation, nested-loop joins, multi-statement SELECT, INSERT, UPDATE, and DELETE operations, complex expressions, or procedural logic, such as conditional statements and loops.

As stated previously, natively compiled stored procedures are compiled into a DLL when they are created, or are re-compiled upon first execution after a server or database restart. The query plan is generated during compilation based upon the data in the memory-optimized tables at the time the procedure is compiled into memory. If the table was empty at the time of compilation, or the data in the tables has changed significantly since the procedure was compiled, you can update the table and index statistics, but unlike interpreted procedures, you cannot simply recompile the natively compiled stored procedure. You will have to explicitly drop and recreate them after updating the table statistics. You’ll want to be sure not to forget to script out the permissions on the natively compiled stored procedures before dropping them.

Creating Natively Compiled Stored Procedures

The following is an example of a natively compiled stored procedure that inserts a record into the InMemTab memory-optimized table:

create procedure dbo.InMemInsert(@Name nvarchar(32), @City nvarchar(50), @State
char(2))
with native_compilation, schemabinding, execute as owner
as
begin atomic with
(transaction isolation level = snapshot,
language = N'English')

  declare @OrdDate datetime = getdate();
  insert into dbo.InMemTab(Name, City, State, LastModified) values (@Name, @City, @
State, GETDATE());
end
go

The NATIVE_COMPILATION keyword in the WITH clause indicates that the Transact-SQL stored procedure is a natively compiled stored procedure. The SCHEMABINDING and EXECUTE AS clauses are required syntax, as well as the new BEGIN ATOMIC clause. Natively compiled stored procedures must run as a single all or nothing atomic block. Atomic blocks are blocks of T-SQL statements that are executed (atomically) within the transaction. Either all statements in the block succeed or the entire block will be rolled back.

In the current release, there are a number of restrictions on what sort of T-SQL operations can be performed in natively compiled stored procedures. A number of the restrictions are listed in Chapter 25, ”Creating and Managing Stored Procedures.” Still, there are so many restrictions, it’s almost easier to list the supported features. A complete list can be found in SQL Server 2014 Books Online at this URL: http://msdn.microsoft.com/en-us/library/dn452279.aspx.

However, instead of trying to keep track of all of the supported features and restrictions, you can use the Native Compilation Advisor in SSMS. The Advisor provides a report of which features used in a stored procedure are not supported in natively compiled procedures. To invoke the Native Compilation Advisor, right click on the procedure name in the Object Explorer in SSMS and select Native Compilation Advisor (see Figure 33.12). This will bring up the Native Compilation Advisor. If the procedure contains elements that disqualify it from being natively compiled, you’ll see a screen similar to the one shown in Figure 33.13 which provides a list of the elements within the procedure code that are not supported.

Figure 33.12 Invoking the Native Compilation Advisor in SSMS.

Figure 33.13 Native Compilation Advisor report for the uspUpdateEmployeeHireInfo Stored Procedure.

Memory-Optimized Table Variables

You can create memory-optimized table variables in SQL Server 2014. Memory-optimized table variables behave the same as regular table variables, but are stored in your database’s memory space only and do not utilize tempdb. You can consider using memory-optimized table variables anywhere, as they are not transactional and can help relieve tempdb contention.

SQL Server 2014 does not support the inline creations of table variables however. It must be based on a memory-optimized table type. The reason for this limitation is because SQL Server needs to compile a DLL for the memory-optimized table type which wouldn’t be possible with dynamic in-line creation of a table variable.

The following example creates a memory-optimized table type:

USE AdventureWorks2012
GO
CREATE TYPE SalesOrderDetailType_inmem
  AS TABLE ( OrderQty smallint NOT NULL,
             ProductID int NOT NULL,
             SpecialOfferID int NOT NULL,
             LocalID int NOT NULL,
             INDEX IX_ProductID NONCLUSTERED HASH (ProductID)
             WITH (BUCKET_COUNT = 131072),
             INDEX IX_SpecialOfferID NONCLUSTERED (SpecialOfferID)
             ) WITH (MEMORY_OPTIMIZED = ON );
GO

Note that, just like memory-optimized tables, memory-optimized table variables must be defined with at least one index.

Once you have defined the memory-optimized table type, you can create table variables based upon the type:

DECLARE @SalesDetail SalesOrderDetailType_inmem;

The memory-optimized table variables can be used as table valued parameters to both natively compiled and interpreted T-SQL stored procedures. They provide an efficient way of passing a bunch of rows to a stored procedure.

Transactions and Memory-Optimized Tables

As described previously in the sections describing the in-memory data row and index structures, SQL Server In-Memory OLTP uses row-versioning to determine which row versions are visible to which transactions. This is accomplished by maintaining an internal Transaction ID that serves the purpose of a timestamp. The timestamps are monotonically increasing every time a transaction commits. A transaction’s start time is the timestamp in the database at the time the transaction starts. When the transaction commits, it generates a new timestamp, the Global Transaction Timestamp, which is used to uniquely identify that transaction. Timestamps are used to specify the following:

• Commit/End Time—The distinct point in time that represents the commit or end timestamp of the transaction. The commit time effectively identifies a transaction’s location in the serialization history.
• Validity Interval—All rows in memory-optimized tables contain two timestamps: the begin timestamp (Begin-Ts) and the end timestamp (End-Ts). The validity interval for a record version denotes the range of timestamps where the version is visible to other transactions.
• Logical Read Time—This can be any value between the transaction’s begin time and the current time. Only the row versions where the logical read time falls within the valid time interval are visible to the read.

The concepts of row versioning and version visibility are fundamental to providing proper concurrency control in In-Memory OLTP. A transaction executing at logical read time X must only see versions of rows where X is greater than the begin timestamp of the row and less than the end timestamp of a row.

Row versioning on disk-based tables (using SNAPSHOT isolation or READ_COMMITTED_SNAPSHOT) provides a form of optimistic concurrency control. Reads are not blocked by writes, but a transaction that locks a row for update blocks other concurrent transactions attempting to update that row. With memory-optimized tables, there is no locking or blocking of writes—writers do not block writers. Instead, if two transactions attempt to update the same row, a write/write conflict (error 41302) will occur.

Additionally, memory-optimized tables allow optimistic concurrency control with the higher isolation levels, REPEATABLE READ and SERIALIZABLE. Locks are not taken to enforce the isolation levels. Instead, validation checks done at the end of the transaction ensure the repeatable read or serializability assumptions and if the assumptions were violated, the transaction is aborted.

Conflicts and failures tend to be rare and transactions on memory-optimized tables assume there will be no conflicts with concurrent transactions. Transactions proceed under the (optimistic) assumption that there will be no conflicts with other transactions. Not using locks and latches and not waiting for other transactions to finish processing the same rows is one of the factors that improves performance of memory-optimized tables. However, if any conflicts occur that may cause inconsistencies in the database or that may violate transaction isolation, SQL Server has a mechanism in place to detect and validate conflicts.

Conflict Detection and Validation

Prior to the final commit of transactions involving memory-optimized tables, SQL Server performs a validation step to determine if data changes could result in invalid data based on the requested isolation level. The validation phase of the commit processing makes sure that there is no invalid data.

Table 33.1 summarizes the error conditions that can occur during validation for memory-optimized table transactions.

Table 33.1 Error Conditions for Memory-Optimized Table Transactions

The failure conditions listed in Table 33.1 can occur at different points during a transaction. Figure 33.14 illustrates the phases of a transaction that accesses memory-optimized tables.

Figure 33.14 Lifetime of a transaction on memory-optimized tables.

As the start of the regular workload phase, the TransactionID and the Current Global Timestamp are obtained. The Current Global Timestamp value determines what row versions are visible to the transaction (i.e, those rows where the Global Timestamp is between the BeginTs and EndTs of the rows). Writes to tables during this phase are not visible to other transactions except for other row update and delete operations. If an update or delete operation sees that a row has been updated or deleted by another transaction, the operation will fail immediately with error 41302, ”The current transaction attempted to update a record in table X that has been updated since this transaction started. The transaction was aborted.”

Also during the regular workload phase, a transaction can read rows written by other transactions that are in the validation or commit phase, but have not yet committed. The rows are visible because the EndTs of the transaction is assigned at the start of the validation phase. When a transaction reads these as yet uncommitted rows, it takes a commit dependency on that transaction which has two main implications:

• The transaction cannot enter the commit phase until all commit dependencies have cleared.
• Result sets are not returned to the client until all dependencies have cleared which prevents the client from reading uncommitted data.

If any of the dependent transactions fails to commit, a commit dependency failure will be raised and the transaction will fail to commit with error 41301 (”A previous transaction that the current transaction took a dependency on has aborted, and the current transaction can no longer commit.”).

While a memory-optimized table transaction will never be blocked waiting for a lock, commit dependency is technically a case of blocking in In-Memory OLTP. However, the time blocked should be very short and generally, blocking waits for commit dependencies will be minimal.

If no direct update conflicts occur and the transaction issues a commit, SQL Server generates the commit timestamp and the transaction enters the validation phase. It is during the validation phase that it will detect other potential violations of the properties specified by the transaction isolation level.

For example, assume a transaction, T1, was accessing a memory-optimized table using REPEATABLE READ isolation. Transaction T1 reads a row value and then another transaction, T2, updates that row value (which it can do because the row is not locked by T1). T2 then issues a commit before T1 commits. When T1 enters the validation phase, it will fail the validation check and SQL Server will abort the transaction.

The following are the steps performed during the validation phase:

• Check for isolation level violations.
• Wait for all commit dependencies to clear.

A SNAPSHOT isolation level validation error can occur if the current transaction inserted a row with the same primary key value as a row that was inserted by another transaction, and that transaction committed before the current transaction. If this is detected, it will raise error 41325, ”The current transaction failed to commit due to a serializable validation failure” and the transaction will be aborted.

For REPEATABLE READ and SERIALIZABLE isolation transactions, SQL Server maintains a read-set for each transaction. The read-set is a set of pointers to the rows that have been read during the transaction. The read-set is used to check for non-repeatable reads. For SERIALIZABLE transactions, SQL Server also keeps a scan-set which is information about the predicate used to access a set of records. The scan-set is used to check for phantom rows.

If memory-optimized tables are accessed in REPEATABLE READ isolation, a validation error is possible when the current transaction has read any row that was subsequently updated by another transaction and that transaction was committed before the current transaction. This condition will raise error 41305, ”The current transaction failed to commit due to a repeatable read validation failure” and the transaction will be aborted.

If memory-optimized tables are accessed in SERIALIZABLE isolation, validation can also encounter the REPEATABLE READ validation errors described in the preceding paragraph. In addition, if a transaction accesses memory-optimized tables in SERIALIZABLE isolation, SQL Server uses the transaction’s scan-set to determine if any additional rows now meet the predicate’s condition. If any rows in the scan-set can no longer be found, or if phantom rows inserted by other transactions that meet the specified filter conditions are encountered, the transaction will fail with error 41325 generated and the transaction will be aborted.

If the transaction gets through the isolation level validation, it then waits for any commit dependencies to clear. If any of the dependent transactions fails to commit, there is a commit dependency failure as described previously. If there are not commit dependency issues, the transaction moves on to the commit phase.

During the commit phase, if any of the modified tables were created with the SCHEMA_AND_DATA option, SQL Server must log the changes to harden them to disk. During transaction processing on durable tables, SQL Server generates a ”write set” of changes, basically a list of DELETE/ INSERT operations with pointers to the version associated with each operation. Upon commit, this write set is written to the transaction log. Once the log record has been hardened to storage, the state of the transaction is changed to committed in the global transaction table. SQL Server then proceeds with any final post-processing, which is the final phase.

The main operations performed during post-processing are to update the timestamps of each of the rows inserted or deleted by the transaction. For a DELETE operation, the row’s End-Ts value is set to the commit timestamp of the transaction. For an INSERT operation, the row’s Begin-Ts value is set to the commit timestamp of the transaction. If the transaction failed or was explicitly rolled back, any inserted rows will be marked as garbage and deleted rows will have their end-timestamp changed back to infinity. The actual unlinking and deletion of old row versions is handled by the garbage collection system which was described previously in the ”Garbage Collection” topic in this chapter.

In-Memory OLTP Transaction Isolation Levels

As discussed in the previous section, In-Memory OLTP supports three transaction isolation levels: SNAPSHOT, REPEATABLE READ, and SERIALIZABLE. SNAPSHOT isolation is the baseline isolation mode used for memory-optimized tables and is the one that should generally be used, especially for natively compiled stored procedures or when accessing memory-optimized tables using interpreted T-SQL.

The isolation level READ COMMITTED is also supported for accessing memory-optimized tables by interpreted T-SQL, but only for autocommit (single statement) transactions, as shown in the following example:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
go
insert into dbo.InMemTab(Name, City, State, LastModified)
    values ('Tom', 'Baltimore', 'MD', GETDATE());
go

Using the READ COMMITTED isolation level is not supported within multi-statement user transactions:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
go
BEGIN TRAN
  insert into dbo.InMemTab(Name, City, State, LastModified)
     values ('Sue', 'Buffalo', 'NY', GETDATE());
  SELECT * FROM InMemTab
COMMIT TRAN
GO

(1 row(s) affected)
Msg 41368, Level 16, State 0, Line 74
Accessing memory-optimized tables using the READ COMMITTED isolation level is
supported only for autocommit transactions. It is not supported for explicit
or implicit transactions. Provide a supported isolation level for the memory
optimized table using a table hint, such as WITH (SNAPSHOT).

Within interpreted T-SQL multi-statement transactions, you need to explicitly specify the isolation mode for memory-optimized tables via table hints:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
go
BEGIN TRAN
  insert into dbo.InMemTab WITH (SNAPSHOT)
     (Name, City, State, LastModified)
     values ('Sue', 'Buffalo', 'NY', GETDATE());
  SELECT * FROM InMemTab  WITH (SNAPSHOT)
COMMIT TRAN
GO

(1 row(s) affected)
ID    Name  City        State LastModified
----- ----- ----------- ----- ---------------------------
8     Tom   Baltimore   MD    2015-03-29 13:51:38.0170000
10    Sue   Buffalo     NY    2015-03-29 13:53:42.0430000

(2 row(s) affected)

If you are running in the default READ COMMITTED mode, a new database option, MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, is available which transparently maps lower isolation levels (such as READ UNCOMMITTED and READ COMMITTED) to snapshot isolation when accessing memory-optimized tables from interpreted T-SQL transactions:

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
go
BEGIN TRAN
  insert into dbo.InMemTab(Name, City, State, LastModified)
     values ('Mary', 'Boston', 'MA', GETDATE());
  SELECT * FROM InMemTab
COMMIT TRAN
GO

(1 row(s) affected)
ID    Name  City        State LastModified
----- ----- ----------- ----- ---------------------------
8     Tom   Baltimore   MD    2015-03-29 13:51:38.0170000
10    Sue   Buffalo     NY    2015-03-29 13:53:42.0430000
11    Mary  Boston      MA    2015-03-29 13:56:19.4000000
(3 row(s) affected)

This database option reduces the need to modify existing SQL code to explicitly specify a transaction isolation level for queries against memory-optimized tables.

SELECT is_memory_optimized_elevate_to_snapshot_on
    FROM sys.databases
    WHERE name = 'AdventureWorks2012'

SELECT DATABASEPROPERTYEX('AdventureWorks2012',
                       'IsMemoryOptimizedElevateToSnapshotEnabled');

Cross-Container Transactions

As you’ve seen in the preceding examples, when you execute interpreted T-SQL against memory-optimized tables, you need to specify the transaction isolation level either via a table hint or using the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT hint. Any transactions you execute from interpreted T-SQL that access both disk-based and memory-optimized tables are referred to as cross-container transactions.

There are strict rules that govern which isolation level combinations are allowed when invoking cross-container transactions to assure that SQL Server can guarantee transactional consistency. The isolation levels that can be used with your memory-optimized tables in a cross-container transaction depend on what isolation level the transaction has defined for the SQL Server transaction. Most of the restrictions have to do with the fact that operations on disk-based tables and operations on memory-optimized tables each have their own transaction sequence number, even if they are accessed in the same T-SQL transaction. You can think of this behavior as having two sub-transactions within the larger transaction: one sub-transaction for the disk-based tables and one for the memory-optimized tables.

Table 33.2 lists the isolation levels that can be used together in cross-container transactions.

Table 33.2 Compatible Isolation Levels in Cross-Container Transactions

Monitoring Transactions on Memory-Optimized Tables

Since transactions on memory-optimized tables are handled separately from transactions on disk-based tables, the standard DMVs for monitoring transactions don’t provide information on memory-optimized table transactions. SQL Server 2014 provides the new sys.dm_db_xtp_transactions DMV for monitoring in-progress transactions on memory-optimized tables.

SELECT xtp_transaction_id ,
          transaction_id ,
          session_id ,
          begin_tsn ,
          end_tsn ,
          state_desc
       FROM sys.dm_db_xtp_transactions
       WHERE transaction_id > 0;
go

/* sample output
xtp_transaction_id transaction_id session_id begin_tsn end_tsn state_desc
------------------ -------------- ---------- --------- ------- ----------
85261              1730680        58         37        0       ACTIVE
85265              1731055        60         37        0       ACTIVE
*/

Let’s examine some of the values in this output.

The xtp_transaction_id values are generated by the Transaction-ID counter and are consecutive. This is the value that SQL Server inserts into End-Ts for rows that an active transaction is deleting, and into Begin-Ts for rows that an active transaction is inserting. The begin_tsn value is the same for both. This is the current timestamp for the last committed transaction at the time the transaction started. The end_tsn timestamp is 0 for both transactions since they are both still active.