Deployment Guide for SQL Server 2016 In-Memory OLTP and In-Memory Columnstore

Overview

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.

How It Works

In-Memory OLTP

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.

In-Memory Columnstore

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:

  • Like other indexes on a Memory Optimized Table, the Columnstore Index must be defined when the table is created.
  • The Columnstore Index must include all columns in the base table. (This limitation doesn't exist for Columnstore Indexes on normal tables.)
  • The Columnstore Index must include all rows in the base table. Or in other words, it can't be a filtered index.

Prerequisite and Deployment Considerations

In addition to the Hardware and Software Requirements for Installing SQL Server 2016, the following are requirements to use In-Memory OLTP: -

  • 64-bit Enterprise, Developer, or Evaluation edition of SQL Server 2016.
  • SQL Server needs enough memory to hold the data in memory-optimized tables and indexes. To account for row versions, you should provide an amount of memory that is two times the expected size of memory-optimized tables and indexes. But the actual amount of memory needed will depend on your workload. You should monitor your memory usage and make adjustments as needed. The size of data in memory-optimized tables must not exceed the allowed percentage of the pool.

    To discover the size of a memory-optimized table, see
    sys.dm_db_xtp_table_memory_stats (Transact-SQL).

    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.

  • To install In-Memory OLTP, select Database Engine Services when you install SQL Server 2016.To install report generation (Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP) and SQL Server Management Studio (to manage In-Memory OLTP via SQL Server Management Studio Object Explorer), select Management Tools—Basic or Management Tools—Advanced when you install SQL Server 2016.

Environment

Prerequisites

  1. SQL Server 2016 CTP 3 should be configured on your Database server.
  2. Your desired database should be created on the server instance.

    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.

  3. On the Database server, sign in to SQL 2016 Management Studio using the windows credentials (default).

    NOTE: For reference, we are using Adventureworks2016CTP3
    database throughout the document.

Download AdventureWorks sample databases and restore it to SQL Server 2016 instance.

  1. Remote logging to the Database server using local administrator credential.
  2. Open Server Manager (located on the task bar near to start button), click on local server and turn off IE Enhanced Security for Administrators (you can turn it on after the downloads)
  3. Go to http://www.microsoft.com/en-us/download/details.aspx?id=49502 , select and download the AdventureWorks2016CTP3 database back up (.bak) file to a folder.

    Note: The provided link is mapped to SQL 2016 CTP3 version downloads. Look for the latest version.

  4. Open SQL Server 2016 Management Studio and connect to the default instance using Windows credentials.
  5. Right click on the Server and click Restore Database, on the wizard select Device and
    browse for the
    AventureWorks2016CTP3.bak
    file downloaded. Click OK to start the restore.
  6. Wait (it will take 1 – 2 minutes) for the "Database Adventureworks2016CTP3 restored successfully" message and click OK
  7. Right click on the database restored and click on New Query, One the SQL Query window issue the below command and verify result set is returned successfully:

Select top 10 * from Sales.Customer

Post Condition

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.

Implementing In-Memory OLTP and In-Memory Columnstore Index

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.'' ';
GO

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: -

USE AdventureWorks2016CTP3
CREATE TABLE t_account (
   accountkey int NOT NULL PRIMARY KEY NONCLUSTERED,
   Accountdescription nvarchar (50),
   accounttype nvarchar(50),
   unitsold int,
   INDEX t_account_cci CLUSTERED COLUMNSTORE
)
WITH (MEMORY_OPTIMIZED = ON);
GO


Post Condition

After executing the above script, In-Memory OLTP and In-memory Columnstore Indexes are created on the table t_account.

References