Monday, October 13, 2014

0 SQL2014: In Memory OLTP (1)

One of the biggest enhancement in SQL 2014 is “In Memory OLTP”, used to be called “Hekaton”.

Microsoft introduce complete new storage engine “Memory Optimized” to move all the data from specific table  into the memory . 

Here are couple key points that memory optimized table has better performance than the traditional row base table.

  • Access as row level . No page, no lock and latch
  • All data has pointer to the row –> Index, only in memory.
  • All index are covering index
  • Since there is no lock and latch. Storage engine use Multi-version optimistic concurrency to support concurrent access.  Similar to Snapshot isolation.
  • Native compiled store procedure –> assembly

prerequisites

Below test is base on the TPC-H database.

I user HammerDB to create the TPCH database and test dataset with scalar factor = 1

image

I also change the database to simple recovery mode to reduce the log file growth.

Create Memory optimize file group

Memory optimize file group is for durable table only. Only one memory optimize file group allow per database.  In memory file group needs the fast sequence read not random access. so not necessary needs the SSD.

We can specify the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT option on the database, which will set the isolation level for memory-optimized tables to SNAPSHOT (as if we included WITH(SNAPSHOT) hints to every memory-optimized table).

--- Use HammerDB to generate the scale factor 1 tpch database
 
--- Change the recovery model to SIMPLE, this is not rerequired , I just want to reduce the log
USE [master]
GO
ALTER DATABASE [tpch] SET RECOVERY SIMPLE WITH NO_WAIT
GO
--- Add memory optimize filegroup
 
IF NOT EXISTS (SELECT * FROM sys.data_spaces WHERE type='FX')
    ALTER DATABASE CURRENT ADD FILEGROUP [tpch_mem] CONTAINS MEMORY_OPTIMIZED_DATA
GO
IF NOT EXISTS (SELECT * FROM sys.data_spaces ds JOIN sys.database_files df ON ds.data_space_id=df.data_space_id WHERE ds.type='FX')
    ALTER DATABASE CURRENT ADD FILE (name='tpch_mem_dir', filename='C:\Database\MSSQL12.SGC4SQL2014\MSSQL\DATA\MEM_DIR') TO FILEGROUP [tpch_mem]
GO
-- select * from sys.data_spaces
--- MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT optional. this is same as use WITH(SNAPSHOT) in every query 
--ALTER DATABASE tpch 
--    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
--GO
 
--- Create memory optimize table
-- Orders table already have clusted index on orderdate and non-clustered index on orderkey
 
 
CREATE TABLE [dbo].[orders_mem](
    [o_orderdate] [date] NOT NULL,
    [o_orderkey] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=3000000),
    [o_custkey] [int] NOT NULL,
    [o_orderpriority] [char](15) NULL,
    [o_shippriority] [int] NULL,
    [o_clerk] [char](15) NULL,
    [o_orderstatus] [char](1) NULL,
    [o_totalprice] [money] NULL,
    [o_comment] [varchar](79) NULL
    INDEX ix_orderdate (o_orderdate ASC)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)  -- SCHEMA_ONLY
 
GO
CREATE TABLE [dbo].[lineitem_mem](
    [l_shipdate] [date] NOT NULL,
    [l_orderkey] [int] NOT NULL,
    [l_discount] [money] NOT NULL,
    [l_extendedprice] [money] NOT NULL,
    [l_suppkey] [int] NOT NULL,
    [l_quantity] [int] NOT NULL,
    [l_returnflag] [char](1) NULL,
    [l_partkey] [int] NOT NULL,
    [l_linestatus] [char](1) NULL,
    [l_tax] [money] NOT NULL,
    [l_commitdate] [date] NULL,
    [l_receiptdate] [date] NULL,
    [l_shipmode] [char](10) NULL,
    [l_linenumber] [int] NOT NULL,
    [l_shipinstruct] [char](25) NOT NULL,
    [l_comment] [varchar](44) NULL
    ,PRIMARY KEY NONCLUSTERED ([l_orderkey], [l_partkey], [l_suppkey], [l_linenumber]) -- Hash Index
    ,INDEX ix_lineitem_mem_shipdate (l_shipdate DESC) -- Range Index
) WITH (MEMORY_OPTIMIZED=ON) -- Default is durable (SCHEMA_AND_DATA)
 
CREATE TABLE [dbo].[customer_mem](
    [c_custkey] [int] NOT NULL,
    [c_mktsegment] [char](10) NULL,
    [c_nationkey] [int] NULL,
    [c_name] [varchar](25) NULL,
    [c_address] [varchar](40) NULL,
    [c_phone] [char](15) NULL,
    [c_acctbal] [money] NULL,
    [c_comment] [varchar](118) NULL
    ,PRIMARY KEY NONCLUSTERED ([c_custkey]) -- Hash Index
) WITH (MEMORY_OPTIMIZED=ON) 
 
 
INSERT INTO [lineitem_mem] SELECT * FROM lineitem
GO
INSERT INTO [customer_mem] SELECT * FROM customer
GO
INSERT INTO [orders_mem] SELECT * FROM orders
GO
 
--- get the db_id and object_id and compare the filesystm structure 
select db_id('tpch')
select object_id('orders_mem')

File group type is FX.

image

image

Create Memory optimize TABLE

Table can be durable ( SCHEMA_AND_DATA) or non durable ( SCHEMA_ONLY).

  • Require Primary key and require index on the primary key
  • All Index are Non-Clustered . Must be NOT NULL. NONCLUSTERED keyword is optional
  • Index needs to created at the table creation time.
  • Maxim 8 Index per table
  • BUCKET_COUNT , recommend 1.5 ~ 2X of unique value on the column. 

Limitation :

  • No Foreign key constrain on the table.
  • xml, user defined and lob data ( such as varchar(max) ) type is not supported in the index.
  • Row size is limit 8060 bytes. ( disk base table have off page storage)

After we have create the table, we can get the db_id for the database  and the object_id for the table. In this case, the database id is 8. 

image

On the database folder, we can find the folder name 8 which has the table definition. The ID 677577452 match our object_id for the orders_mem table. As you can see, the table structure has been compile as C code. That is why we can not change the table structure after table is created. ( No Alter table action allow) and all the index needs to be created at table creation time.

image

Under the memory optimized file group directory, it has all the data that related to the table.

image

Finally, below query show us what dll  related memory optimized table has been loaded into SQL Server memory. the dll name is DatabaseID_objectID

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

image

ERROR related to MEMORY OPTIMIZED TABLE

Below are the some of the errors you may see if we violate the limitation for creating table.

Msg 12301, Level 16, State 7, Line 40

Nullable columns in the index key are not supported with indexes on memory optimized tables.

Msg 1750, Level 16, State 0, Line 40

Could not create constraint or index. See previous errors.

image

Msg 41321, Level 16, State 7, Line 38
The memory optimized table 'orders_mem2' with DURABILITY=SCHEMA_AND_DATA must have a primary key.
Msg 1750, Level 16, State 0, Line 38
Could not create constraint or index. See previous error

image

Msg 12317, Level 16, State 72, Line 38
Clustered indexes, which are the default for primary keys, are not supported with memory optimized tables.

image

Msg 10794, Level 16, State 12, Line 37
The operation 'CREATE INDEX' is not supported with memory optimized tables.

image

Msg 10794, Level 16, State 84, Line 44
The type 'varchar(max)' is not supported with memory optimized tables.

image

Msg 41307, Level 16, State 1, Line 44
The row size limit of 8060 bytes for memory optimized tables has been exceeded. Please simplify the table definition.

image

Memory usage report

Click Database –> Reports –> Memory Usage By Memory Optimized Objects

image

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates