In-Memory was introduced in SQL Server 2014. SQL Server 2016 is continuing the investment in In-memory OLTP by removing many of the limitations found in SQL Server 2014, and enhancing internal processing algorithms so that In-memory OLTP can provide even greater improvements. The project code name of this feature is Hekaton (a Greek word) which means hundred or hundredfold. This powerful technology allows you to take advantage of large amounts of memory and many dozens of cores to increase performance for OLTP operations by up to 30 to 40 times
This document talks about implementation of SQL Server 2016's In-memory OLTP technology as of SQL Server 2016 CTP3. Using In-memory OLTP, tables can be declared as 'memory optimized' to enable In-Memory OLTP's capabilities. We're using SQL Server 2016 CTP3 installed on Database server.
In-Memory OLTP is integrated with the SQL Server relational engine, and can be accessed transparently using the same interfaces. In fact, users may be unaware that they are working with memory-optimized tables rather than disk-based tables. However, the internal behavior and capabilities of In-memory OLTP are very different.
Figure 1 gives an overview of the SQL Server engine with the In-Memory OLTP components.
Figure 1The SQL Server engine including the In-Memory OLTP component
Notice that the client application connects to the TDS Handler the same way for memory-optimized tables or disk-based tables, whether it will be calling natively compiled stored procedures or interpreted Transact-SQL. The In-memory native compiler takes an abstract tree representation of a T-SQL stored procedure defined as a native stored procedure. This representation includes the queries within the stored procedure, in addition to the table and index metadata then compiles the procedure into native\machine code. This code is designed to execute against tables and indexes managed by the storage engine for memory optimized tables.
A Columnstore Index is an index that organizes its data in terms of columns instead of rows. Each block of data represents one column with up to one million rows. So if you had five columns and ten million rows, you would have 50 blocks of data. This organizational strategy is particularly effective when you only want a subset of the columns, as the columns you don't care about aren't read from disk.
Columnstore indexes are significantly faster than table scans, but not quite as fast as traditional B-Tree style indexes. This makes especially suited for ad hoc reports where you can't predict which indexes will be needed.
A Memory Optimized Table is just what it sounds like, a table that is optimized to be held in memory at all times. This allows for numerous benefits such as lock-free writes, but it comes with significant limitations. For example, you are only allowed eight indexes, which can be quite limiting for a table being used for ad hoc queries.
SQL Server 2016 partially compensates for this limitation by allowing one of those eight indexes to be a Columnstore Index. But there are rules that you need to follow:
In addition to the Hardware and Software Requirements for Installing SQL Server 2016, the following are requirements to use In-Memory OLTP: -
To discover the size of a memory-optimized table, see
If you have disk-based tables in the database, you need to provide enough memory for the buffer pool and query processing on those tables.
It is important to know how much memory your In-Memory OLTP application will require. See Estimate Memory Requirements for Memory-Optimized Tables for more information.
Free disk space for that is two times the size of your durable memory-optimized tables.
A processor needs to support the instruction cmpxchg16b to use In-Memory OLTP. All modern 64-bit processors support cmpxchg16b.
If you are using a VM host application and SQL Server displays an error caused by an older processor, see if the application has a configuration option to allow cmpxchg16b. If not, you could use Hyper-V, which supports cmpxchg16b without needing to modify a configuration option.
NOTE: If you do not have any database you can use sample database Adventureworks2016CTP3. For downloading sample database, you can refer to steps in 4.1.1.
NOTE: For reference, we are using Adventureworks2016CTP3
database throughout the document.
Download AdventureWorks sample databases and restore it to SQL Server 2016 instance.
Note: The provided link is mapped to SQL 2016 CTP3 version downloads. Look for the latest version.
Select top 10 * from Sales.Customer
SQL Server 2016 CTP3 is up and running in the Database server.
If you have downloaded the sample database then from SQL management studio, you can see the AdventureWorks2016CTP3 database created on server.
Figure 3 Customer records displayed through the Management Services
Figure 3 executes a simple SELECT query to retrieve data from the restored database.
The In-Memory OLTP engine was introduced in SQL Server 2014. Installation of In-Memory OLTP is part of the SQL Server setup application, as it is just a part of the database engine service. The In-Memory OLTP components can only be installed with a 64-bit edition of SQL Server, and not available at all with a 32-bit edition.
Once a database has a filegroup with the property CONTAINS MEMORY_OPTIMIZED_DATA, and that filegroup has at least one file in it, you can create memory-optimized tables in that database. The following query will show you the names of all the databases in an instance of SQL Server that meets those requirements:
EXEC sp_MSforeachdb 'USE ? IF EXISTS (SELECT 1 FROM sys.filegroups FG
JOIN sys.database_files F
ON FG.data_space_id = F.data_space_id
WHERE FG.type = ''FX'' AND F.type = 2)
PRINT ''?'' + '' can contain memory-optimized tables.'' ';
The Command returned the List of Databases that meets the requirement. Adventureworks2016CTP3 DB meets the requirement for creating in memory tables.
The syntax for creating memory-optimized tables is almost identical to the syntax for creating disk-based tables, with a few restrictions, as well as a few required extensions. Specifying that the table is a memory-optimized table is done using the MEMORY_OPTIMIZED = ON clause.
A memory-optimized table can be defined with one of two DURABILITY values: SCHEMA_AND_DATA or SCHEMA_ONLY with the former being the default. A memory-optimized table defined with DURABILITY=SCHEMA_ONLY, which means that changes to the table's data are not logged and the data in the table is not persisted on disk.
The columnstore index on an in-memory table allows operational analytics by integrating in-memory OLTP and in-memory columnstore technologies to deliver high performance for both OLTP and analytics workloads. The columnstore index on an in-memory table must include all the columns.
Note: - A columnstore index on in-memory table can only be added as part of table creation. You cannot add a columnstore index on an existing in-memory table. This limitation will be removed before SQL Server 2016 Release Candidate (RC0) is released for RTM.
To Create an In-Memory table with a columnstore index run the following command: -
CREATE TABLE t_account (
accountkey int NOT NULL PRIMARY KEY NONCLUSTERED,
Accountdescription nvarchar (50),
INDEX t_account_cci CLUSTERED COLUMNSTORE
WITH (MEMORY_OPTIMIZED = ON);
After executing the above script, In-Memory OLTP and In-memory Columnstore Indexes are created on the table t_account.