Overcoming Performance Issues with Memory Optimized tables - SQL Server 2014 CPT2

Overcoming Performance Issues with

Memory Optimized tables - SQL Server 2014 CPT2

1.       Problem

Tuning   databases so that they provide the best possible performance is one of the most important responsibilities of database administrators (DBAs).  SQL Server was originally designed at a time when it could be assumed that main memory was very expensive, so data needed to reside on disk except when it was actually needed for processing.  As the amount data   within an organization increases, accumulation of data affects the productivity of DBAs, who must spend considerable time and effort on achieving the high levels of database performance.

2.       Solution

Nowadays memory is much cheaper and a working data set can be stored entirely in memory. The assumption that main memory was very expensive is no longer valid as memory prices have dropped enormously. Memory-optimized tables are stored completely differently than disk-based tables and these new data structures allow the data to be stored, accessed and processed in cache.

-  Disk-based tables Vs Memory-Optimized tables

The most important difference between memory-optimized tables and disk-based tables is that pages do not need to be read into cache from disk when the memory-optimized tables are accessed. All the data is stored in memory, all the time.

A set of checkpoint files which are only used for recovery purposes, is created on filestream filegroups that keep track of the changes to the data, and the checkpoint files are append-only.

Operations on memory-optimized tables use the same transaction log that is used for operations on disk-based tables, and as always, the transaction log is stored on disk. In case of a system crash or server shutdown, the rows of data in the memory-optimized tables can be recreated from the checkpoint files and the transaction log.

·                     Step 1:   Create database for memory optimized data

Any database that will contain memory-optimized tables need be created with at least one MEMORY_OPTIMIZED_DATA filegroup. These file groups are used for storing the checkpoint and delta files needed by SQL Server to recover the memory-optimized tables. This filegroup will be used to guarantee durability of memory-resident data in the event of a server crash or restart. During the crash recovery phase in server startup, the data is recovered from this filegroup and loaded back into memory.

CREATE DATABASE trunk201464

GO

ALTER DATABASE trunk201464

        ADD FILEGROUP trunk201464_mod contains MEMORY_OPTIMIZED_DATA

GO

ALTER DATABASE trunk201464

        ADD FILE (name='trunk201464_mod', filename='c:\data\trunk201464_mod')

        TO FILEGROUP trunk201464_mod

GO

USE trunk201464

GO

-  Contains MEMORY_OPTIMIZED_DATA : Although the syntax for creating them is almost the same as for creating a regular filestream file group, it must also specify the option CONTAINS MEMORY_OPTIMIZED_DATA.

-  Filename : In this example we picked the folder 'c:\data\trunk201464_mod'. Make sure the folder exists before running the script.

·                     Step 2: Create Memory-Optimized Tables

--Memory-Optimized Tables

CREATE TABLE dbo.Daily_INRDollarRate

(

        INRDollarRateID int      NOT NULL,

        INRDollarRateDt datetime NOT NULL,

        AverageRate     money    NOT NULL,

        EndOfDayRate    money    NOT NULL,

CONSTRAINT PK_Daily_INRDollarRate_ID PRIMARY KEY NONCLUSTERED HASH (INRDollarRateID) WITH (BUCKET_COUNT = 1024),

INDEX IX_INRDollarRate HASH (INRDollarRateDt,AverageRate,EndOfDayRate) WITH (BUCKET_COUNT = 2048)

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

GO

-  Primary key NONCLUSTERED HASH WITH BUCKET_COUNT :

At least one index must be declared to support a PRIMARY KEY constraint. The following example shows a PRIMARY KEY index created as a HASH index, for which a bucket count must also be specified.

-  MEMORY_OPTIMIZED: Specifying that the table is a memory-optimized table is done using the MEMORY_OPTIMIZED = ON clause.

-  DURABILITY : 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. However, the schema is persisted as part of the database metadata, so the empty table will be available after the database is recovered during a SQL Server restart.

-  Limitations:

Memory optimized table doesn’t allow following things to be created on it:

• No DML triggers
• No FOREIGN KEY or CHECK constraints
• No IDENTITY columns
• No UNIQUE indexes other than for the PRIMARY KEY
• A maximum of 8 indexes, including the index supporting the PRIMARY KEY

-  Transaction Isolation Levels

The following isolation levels are supported for transactions accessing memory-optimized tables.

• SNAPSHOT
• REPEATABLE READ
• SERIALIZABLE

-  T-SQL Support

T-SQL features not supported when accessing memory-optimized tables :

·         TRUNCATE TABLE

·         MERGE (when a memory-optimized table is the target)

·         Dynamic and keyset cursors (these are automatically degraded to static cursors)

·         Cross-database queries

·         Cross-database transactions

·         Linked servers

·         Locking hints: TABLOCK, XLOCK, PAGLOCK, NOLOCK, etc.

·         Isolation level hints READUNCOMMITTED, READCOMMITTED and READCOMMITTEDLOCK

·         Memory-optimized table types and table variables are not supported.

-  Migration to In-Memory OLTP

AMR (Analysis, Migrate and Report) Tool helps you plan your migration to In-Memory OLTP. AMR provides the Memory-Optimization Advisor, which helps you identify the incompatibilities in your table that must be removed before moving the table to In-Memory OLTP.

3.       Summary

SQL Server In-Memory OLTP provides the ability to create and work with tables that are memory-optimized and extremely efficient to manage, providing performance optimization for OLTP workloads. In-Memory OLTP memory-optimized tables can be referenced in the same transactions as disk-based tables, with only a few restrictions.

-  Further reads: 

http://msdn.microsoft.com/en-us/library/dn205133%28v=sql.120%29.aspx AMR (Analysis, Migrate and Report) Tool