Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Overcoming storage speed limitations with Memory-Optimized Tables for SQL Server


By:   |   Last Updated: 2013-10-02   |   Comments (8)   |   Related Tips: More > In-Memory OLTP

Problem

Achieving mission critical performance is key for most businesses, but sometimes due to storage costs you just can't afford it. Traditional databases were designed assuming that memory resources were expensive and were optimized for disk I/O. Nowadays memory is much cheaper and a working set can be stored entirely in memory. In this tip we look at a new solution in SQL Server 2014 to create memory optimized tables.

Solution

The upcoming version of SQL Server 2014 will provide us a new feature called In-Memory OLTP (codenamed Hekaton). In this tip I will tell you about Memory-Optimized Tables.

Memory-Optimized Tables allow us to store an entire table in the system memory. At this point you may say "Wait! This is not new, DBCC PINTABLE was amongst us for a long time", but this didn't work the same way.
Initially, DBCC PINTABLE does not store the entire table in memory it only stores the read pages. Also, this feature was discontinued since SQL Server 2005 as stated in this Transact-SQL reference.

What is Hekaton?

Basically Hekaton is a new database engine, integrated into SQL Server, optimized for memory resident data and OLTP workloads. With Hekaton, the entire table is stored in memory and stored procedures that only references memory optimized tables can be compiled into machine code with no user interaction just by adding the "WITH NATIVE_COMPILATION" clause in the stored procedure creation.  It adjusts completely to the ACID (Atomicity, Consistency, Isolation and Durability) properties.

Hekaton has the following features:

  1. Algorithms that are optimized for accessing memory-resident data.
  2. Optimistic concurrency control that eliminates logical locks via automatic SNAPSHOT ISOLATION with multi-versioning.
  3. Lock free and latch free structures to access all data.

Now you may be asking yourself the following question: Do I get the same performance as with a table in a filegroup on a SSD RAID or even a RAM Drive?  The answer is NO. Even if the entire database could be stored in system memory, performance won't be the same. Keep in mind that SQL Server code is optimized for disk storage, and this requires a complex buffer pool where a page must be protected by latching before it can be accessed. In a disk based table, rows are addressed using PAGE ID and ROW OFFSET. By contrast, on Hekaton rows are addressed by a type of B-Tree called Bw-Tree (http://research.microsoft.com/pubs/170384/bw-tree.pdf).

Creating a Memory Optimized File Group for SQL Server

First, we must create a MEMORY_OPTIMIZED_DATA filegroup. which is much like a FILESTREAM filegroup. I am using a copy of AdventureWorks for this tip. Keep in mind that only one MEMORY_OPTIMIZED_DATA filegroup is allowed per database. You can create this filegroup with the GUI as follows.

Step 1

Database Properties - Filegroups

Step 2

Database Properties - Files

Also you can use the following SQL code.

USE [master]
GO
ALTER DATABASE [AdventureWorks] 
  ADD FILEGROUP [MemoryOptimized] CONTAINS MEMORY_OPTIMIZED_DATA 
GO
ALTER DATABASE [AdventureWorks] 
  ADD FILE ( NAME = N'Hekaton', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton' ) 
  TO FILEGROUP [MemoryOptimized]
GO

Memory Optimized Filegroup Properties

A memory optimized filegroup contains one or more containers and each container has one or more files. There are three types of files.

Root File

A root file contains metadata including the data and delta file pairs for a complete checkpoint taken for memory-optimized tables.

Data File

A data file contains data rows that were inserted into memory-optimized tables. The rows are appended to the data file in the transaction log order making data access sequential. This increases IO throughput compared to random IO. Data files have a maximum size of 128MB. Beyond this point a new data file is created. Also, a new data file is created when a manual checkpoint is performed.

The rows are stored with a header that contains the row timestamp to implement versioning and N index pointers for row chaining, where N is the number of indexes defined on the table. See the image below.

Hekaton Row Format

The indexes are not stored in the data files; instead they are recreated at startup.

Delta File

A delta file contains data rows that were deleted. For each deleted row, it inserts minimal information, the ID of deleted rows and the insert / delete transaction ID. The Delta files are stored in transactional log order, so the file can be accessed sequentially.

A note of interest is the way Hekaton handles updates. As I mentioned before, in order to avoid latches and locks, Hekaton handles multiple versions of the data with a timestamp. So, to achieve this, updates are managed as a DELETE followed by an INSERT. This implies that the engine does not need to seek the data file to update the information. Instead, it appends the updated data at the end of the data file.

Storage considerations for Memory Optimized Tables

As data and checkpoint files use streaming IO for data persistence, the limiting factor for this files is the sequential IO speed instead of latency or random access speed. To optimize data storage for database startup times, you can add multiple containers to the memory-optimized data filegroup, each on a different disk. This way SQL Server will read all of the containers in parallel.

When rows are deleted, data files still contain the inactive rows. In order to consolidate data and delta file pairs, Hekaton runs a merge process in the background.

All this processing will degrade file system performance due to fragmentation.

Memory-Optimized tables write to the SQL Server transaction log just like disk based tables. The only difference is that there are no log records for physical structure modifications, indexing or UNDO information. So, if your system has high transaction rates, you may consider using a SSD disk or a PCI-E card for the transaction log.

Table creation for a Memory Optimized Table

The CREATE TABLE statement for Memory-Optimized tables is the same as the statement for standard tables with the addition of the MEMORY_OPTIMIZED = ON table option. Also, you can specify if the data will persist after a server restart by setting the DURABILITY table option to SCHEMA_AND_DATA, the default option, or to SCHEMA_ONLY.

The SCHEMA_AND_DATA options guarantees durability of schema and data, like a disk based table, but does not store index data, as I previously explained, indexes are built on server startup. By contrast, the SCHEMA_ONLY option only stores schema definition of the table and indexes, much like a tempDB table.

You may be asking what is the difference between a tempDB table and a Memory-Optimized Table with SCHEMA_ONLY duration. First, a tempDB table does not keep its schema definition upon server restart, and second and most important, there is the fact that a tempDB table is subject to checkpoints and also generates log entries of table and index operations into the log files. On the other hand, a Memory-Optimized Table with SCHEMA_ONLY duration is unaffected by the checkpoint process and does not store any log information.

DLL Generation

Every time you create a Memory-Optimized table or a natively compile a stored procedure, which I will explain in an upcoming tip, SQL Server launches a child process that compiles the table structure or the stored procedure into a Windows DLL as shown on the image below.

Table DLL Compilation

The compiled DLL's are stored in "..\MSSQL\DATA\xtp\<DB_ID>\", where <DB_ID> is the ID of the database in which the object belongs and the name of the DLL has the following format: "xtp_<t|p>_<DB_ID>_<OBJECT_ID>.dll".

Tag Description

<t|p>

t = Table; p = Stored Procedure

<DB_ID>

The ID of the database in which the object belongs to

<OBJECT_ID>

The ID of the object from sys.objects

Limitations for Memory Optimized Tables

To date, with SQL Server 2014 CTP1, there are some limitations for Memory-Optimized tables which I will list below.

  • ALTER TABLE, sp_rename, alter bucket_count, and adding and removing an index outside of the CREATE TABLE statement is not supported for memory-optimized tables.
  • The IDENTITY (for table types only) and PRIMARY KEY constraints are supported.
  • UNIQUE, CHECK, and FOREIGN KEY constraints are not supported.
  • CREATE INDEX, ALTER INDEX and DROP INDEX statements are not supported for HASH indexes.
  • Memory-optimized tables do not support range indexes (in CTP-1), DDL triggers, DML triggers, or constraints (except for primary key).

For more information check MSDN - SQL Server Support for In-Memory OLTP.

Example of a Memory Optimized Table for SQL Server

For this example, I have used the AdventureWorks database and specifically the Sales.CurrencyRate table. This table, as the name suggests, stores currency rates for different currencies. It contains the Average exchange rate for the day and the day's final exchange rate. So, in a production environment, this table would be subject to several queries. The exchange rate for a currency can vary several times a day, so this table would cause a lot of updates. Furthermore, for every sale, this table will be accessed to check the currency rates to determine the sale amount. Consequently, this table is a good candidate for memory optimization.

What I am going to do in this tip is to split the Sales.CurrencyRate table into two. One called Sales.HistoricCurrencyRate to store historical data, and other called Sales.DailyCurrencyRate to keep in memory currency rates of the actual day and then create a view to merge the two tables.

1. Add Memory-Optimized FILEGROUP to AdventureWorks

USE [master]
GO
ALTER DATABASE [AdventureWorks] 
  ADD FILEGROUP [MemoryOptimized] CONTAINS MEMORY_OPTIMIZED_DATA 
GO
ALTER DATABASE [AdventureWorks] 
  ADD FILE ( NAME = N'Hekaton', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton' ) 
  TO FILEGROUP [MemoryOptimized]
GO

2. CREATE TABLE Sales.DailyCurrencyRate

Here is the Sales.DailyCurrencyRate creation script.

USE AdventureWorks
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE Sales.DailyCurrencyRate(
 CurrencyRateID int NOT NULL,
 CurrencyRateDate datetime NOT NULL,
 FromCurrencyCode nchar(3) COLLATE Latin1_General_100_BIN2 NOT NULL,
 ToCurrencyCode nchar(3) COLLATE Latin1_General_100_BIN2 NOT NULL,
 AverageRate money NOT NULL,
 EndOfDayRate money NOT NULL,
 ModifiedDate datetime NOT NULL,
 CONSTRAINT PK_DailyCurrencyRate_CurrencyRateID PRIMARY KEY NONCLUSTERED HASH (CurrencyRateID) WITH (BUCKET_COUNT = 1024),
 INDEX AK_DailyCurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode HASH (CurrencyRateDate, FromCurrencyCode, ToCurrencyCode) WITH (BUCKET_COUNT = 2048)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

In this script you may notice the introduction of HASH INDEXES which are supported only on memory optimized tables. In an upcoming tip I will explain this topic and give you some samples, but for now you can read the available documentation on the TechNet library about the Guidelines for Using Indexes on Memory-Optimized Tables.

Also you will see that this table has no IDENTITY column. That is a limitation of Memory-Optimized tables, but we can solve this with the creation of a SEQUENCE as you will see further down.

Note the COLLATE Latin1_General_100_BIN2 clause. Hekaton tables only allow BIN2 collation on all character columns that participate in indexes. Also, natively compiled stored procedures only support BIN2 collations for comparisons, sorting and aggregate functions.

3. CREATE TABLE HistoricCurrencyRate

Now we create the Sales.HistoricCurrencyRate table with the same definition of Sales.CurrencyRate table but without the identity column. That is because new records will be inserted into Sales.DailyCurrencyRate table.

USE AdventureWorks;
GO
SET  QUOTED_IDENTIFIER ON;
GO
CREATE TABLE Sales.HistoricCurrencyRate(
  CurrencyRateID     INT NOT NULL,
  CurrencyRateDate   DATETIME NOT NULL,
  FromCurrencyCode   NCHAR(3) NOT NULL,
  ToCurrencyCode     NCHAR(3) NOT NULL,
  AverageRate        MONEY NOT NULL,
  EndOfDayRate       MONEY NOT NULL,
  ModifiedDate       DATETIME NOT NULL DEFAULT (getdate()),
  CONSTRAINT FK_HistoricCurrencyRate_Currency_FromCurrencyCode FOREIGN KEY
    (FromCurrencyCode)
     REFERENCES Sales.Currency(CurrencyCode),
  CONSTRAINT FK_HistoricCurrencyRate_Currency_ToCurrencyCode FOREIGN KEY
    (ToCurrencyCode)
     REFERENCES Sales.Currency(CurrencyCode),
  CONSTRAINT PK_HistoricCurrencyRate_CurrencyRateID PRIMARY KEY
    CLUSTERED
    (CurrencyRateID ASC)
    WITH ( PAD_INDEX = OFF , FILLFACTOR = 100 , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , DATA_COMPRESSION = NONE )
    ON [PRIMARY])
CREATE UNIQUE NONCLUSTERED INDEX AK_HistoricCurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode
  ON Sales.HistoricCurrencyRate(
    CurrencyRateDate,
    FromCurrencyCode,
    ToCurrencyCode)
  WITH ( PAD_INDEX = OFF , FILLFACTOR = 100 , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , DATA_COMPRESSION = NONE ) ON [PRIMARY];
GO

4. INSERT INTO Sales.HistoricCurrencyRate

At this time, we are ready to move the data of Sales.CurrencyRate into Sales.HistoricCurrencyRate.

USE AdventureWorks
GO
INSERT INTO Sales.HistoricCurrencyRate
 SELECT CurrencyRateID,
   CurrencyRateDate,
   FromCurrencyCode,
   ToCurrencyCode,
   AverageRate,
   EndOfDayRate,
   ModifiedDate
 from Sales.CurrencyRate

5. DROP TABLE CurrencyRate

In order to drop the Sales.CurrencyRate table we must first drop the constraint of the Sales.SalesOrderHeader table.

USE AdventureWorks
GO
ALTER TABLE Sales.SalesOrderHeader 
 DROP CONSTRAINT FK_SalesOrderHeader_CurrencyRate_CurrencyRateID
DROP TABLE Sales.CurrencyRate

6. CREATE VIEW Sales.CurrencyRate

After that, we can create the Sales.CurrencyRate view.

USE AdventureWorks
GO
IF object_id(N'Sales.CurrencyRate', 'V') IS NOT NULL
 DROP VIEW Sales.CurrencyRate
GO
CREATE VIEW Sales.CurrencyRate AS
SELECT D.CurrencyRateID,
   D.CurrencyRateDate,
   D.FromCurrencyCode COLLATE SQL_Latin1_General_CP1_CI_AS AS FromCurrencyCode,
   D.ToCurrencyCode COLLATE SQL_Latin1_General_CP1_CI_AS AS ToCurrencyCode,
   D.AverageRate,
   D.EndOfDayRate,
   D.ModifiedDate 
FROM Sales.DailyCurrencyRate D WITH (SNAPSHOT)
UNION ALL
SELECT H.CurrencyRateID,
   H.CurrencyRateDate,
   H.FromCurrencyCode,
   H.ToCurrencyCode,
   H.AverageRate,
   H.EndOfDayRate,
   H.ModifiedDate 
FROM Sales.HistoricCurrencyRate H
GO

Notice the WITH (SNAPSHOT) hint. This is because accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. So we must provide a supported isolation level for the memory optimized table using a table hint.

7. CREATE SEQUENCE Sales.DailyCurrencyRate_CurrencyRateID_Seq

To solve Hekaton's lack of support for IDENTITY columns, we must create a SEQUENCE as follows.

USE [AdventureWorks]
GO
DECLARE @StartValue  INT
DECLARE @SqlStmt  VARCHAR(255)
SELECT @StartValue = MAX (CurrencyRateID) +1 FROM 
 sales.HistoricCurrencyRate
SET @SqlStmt = N'CREATE SEQUENCE Sales.DailyCurrencyRate_CurrencyRateID_Seq
       AS [int]
      START WITH ' +CONVERT (NVARCHAR(50), @StartValue )
      + 'INCREMENT BY 1'
EXEC (@SqlStmt)
GO

8. CREATE TRIGGER Sales.TR_CurrencyRate_Insert

After that, we create an INSTEAD OF TRIGGER to handle the inserts.

USE [AdventureWorks]
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER Sales.TR_CurrencyRate_Insert 
   ON  Sales.CurrencyRate 
   INSTEAD OF INSERT
AS 
BEGIN
 SET NOCOUNT ON;
 INSERT INTO sales.DailyCurrencyRate
         ( CurrencyRateID ,
           CurrencyRateDate ,
           FromCurrencyCode ,
           ToCurrencyCode ,
           AverageRate ,
           EndOfDayRate ,
           ModifiedDate
         )
 SELECT   NEXT VALUE FOR Sales.DailyCurrencyRate_CurrencyRateID_Seq,
           CurrencyRateDate ,
           FromCurrencyCode ,
           ToCurrencyCode ,
           AverageRate ,
           EndOfDayRate ,
           ModifiedDate
  FROM INSERTED
END
GO

9. Insert test data

I have made a simple script to insert data into Sales.CurrencyRate with a CTE. You can create your own if you like.

USE AdventureWorks
GO
DECLARE  @start DATETIME
DECLARE  @end DATETIME
SET @start = '20130903'
SET @end = '20130904'
; WITH CTE AS (
 SELECT @start AS  I
 UNION ALL
 SELECT   I +1 FROM CTE
 WHERE I +1 <= @end
)
INSERT INTO Sales.CurrencyRate (CurrencyRateDate, FromCurrencyCode, ToCurrencyCode,
    AverageRate, EndOfDayRate, ModifiedDate)
SELECT 
      I     AS  CurrencyRateDate,
     'USD'    AS  FromCurrencyCode,
      C.CurrencyCode AS ToCurrencyCode,   
   RAND()*SQUARE (CONVERT(TINYINT,HASHBYTES('SHA1',  C.CurrencyCode)))    AS AverageRate,
   RAND()*SQUARE (CONVERT(TINYINT,HASHBYTES('MD5',  C.CurrencyCode)))   AS EndOfDayRate,
      I     AS  ModifiedDate
   FROM CTE 
CROSS APPLY Sales.Currency  C 
ORDER BY I, CurrencyCode
OPTION (MAXRECURSION 4000);

At this point the Memory Optimized Table is setup and ready to be used for your database transactions.

Next Steps

Stay tuned for additional tips about SQL Server 2014.  In the meantime, take some time to review these tips:



Last Updated: 2013-10-02


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, October 19, 2015 - 1:05:34 PM - Lokesh Back To Top

Great Explanation! Thank you for sharing in such deep detail.


Friday, December 27, 2013 - 5:25:31 PM - Daniel Farina Back To Top

Hi Csaba Toth,

This is the PDF link. http://research.microsoft.com/pubs/178758/bw-tree-icde2013-final.pdf but that PPT is more easy to read.

 

Thank you for everything!

 

Best Regards!


Friday, December 27, 2013 - 3:50:08 PM - Csaba Toth Back To Top

I couldn't navigate to the Bw-Tree link. Probably it is https://research.microsoft.com/apps/pubs/default.aspx?id=178758 or PPT: research.microsoft.com/en-us/um/people/justinle/papers/icde2013_bwtree.pptx


Wednesday, November 06, 2013 - 11:03:57 AM - Junior Galv„o - MVP Back To Top

Hi Daniel,

 

Congratulations for post. I didn't know Hekaton e Memory Optimized - Table.

 

Great News.

 

Regards.

 

Tuesday, October 08, 2013 - 12:45:54 PM - Daniel Farina Back To Top

Hi Bill

You are right! That was an error on the editing process. Please accept my apologies.
The fact is that primary key constraints are supported for both Memory-optimized tables and Memory-optimized table types, but by now in CTP1 Identity columns are only supported on Memory-optimized table types.

 

 

Thank you for your time on reading and letting me know about this mistake.
Thank you very much!


Best regards

 


Tuesday, October 08, 2013 - 12:40:07 PM - Daniel Farina Back To Top

Hi Timothy!

I don't know certainly in which editions this feature will be available. I believe that MS would include this feature in all editions but maybe with some limitations.

Hekaton has his own checkpoint process not tied to recovery time interval and is triggered when the log exeeds a threshold and when a determined time has crossed since last checkpoint or manual checkpoint.

Thank you for reading!

Best regards


Tuesday, October 08, 2013 - 9:19:39 AM - Bill Back To Top

In the section

Limitations for Memory Optimized Tables

You state: The IDENTITY (for table types only) and PRIMARY KEY constraints are not supported.

You follow up by saying that the only supported constraint is a PRIMARY KEY.  Your example then creates a memory table with a primary key.

 


Monday, October 07, 2013 - 6:48:27 PM - TimothyAWiseman Back To Top

This looks like it could be a useful feature at times.  Do you know which editions of SQL Server 2014 this will be in?  Will it be limited to enterprise or will be available in the other editions?

Also, how persistent will the tables with a Durability of SCHEMA_AND_DATA be in the face of something like an unexpected power outage?  In short, how often is it flushed to disk?


Learn more about SQL Server tools