SQL Server 2014 new features-in-memory OLTP (In-memory OLTP)

SQL Server new Features-- in-memory OLTP (in-memory OLTP )

Overview

In-Memory OLTP (project "Hekaton") is a completely new database engine component that is fully integrated into SQL Server. Optimized for in-memory data in OLTP workload access. In-memory OLTP can help OLTP workloads achieve significant performance improvements and reduce processing time. Tables can be considered "memory-optimized" to enhance OLTP functionality in memory. Memory-optimized tables are fully transactional and can be accessed using Transact-SQL. Transact-SQL stored procedures can be compiled into machine code to further improve the performance of memory-optimized tables. The engine is designed for high concurrency, and the blocking is minimal.

Terms

Disk-based tables:

The traditional table storage method has the following main properties:

· Mapped to a filegroup, and the filegroup contains one or more files.

· Each file is divided into 8 page extents, with a size of 8K bytes per page.

· You can share a single zone across multiple tables, but there is a one-to mapping between the assigned page and the table or index. In other words, a page cannot have rows from two or more tables or indexes.

· The data is moved to memory (the buffer pool) as needed, and the modified or newly created page is asynchronously written to the disk that primarily generates the random IO.

Memory-Optimized tables:

Storage for memory-optimized tables has the following main properties:

· All memory-optimized tables are mapped to memory-optimized filegroups. Use a file stream file group to generate this filegroup.

· No pages exist, and the data is persisted as rows.

· All changes made to the memory-optimized table are stored by appending to the active file. The read and write operations on the file are sequential.

· Implement the update by deleting and inserting it first. Deleted rows are not immediately removed from the store. The deleted rows are removed by a background process called MERGE, based on the policy described in the persistence of memory-optimized tables.

· Unlike disk-based tables, storage for memory-optimized tables is not compressed. When you migrate a compressed (ROW or PAGE) disk-based table to a memory-optimized table, you will need to consider the size changes.

· Memory-optimized tables can be durable or non-durable. You only need to configure storage for persistent memory-optimized tables.

Cross-container transactions:

Refers to transactions that refer to memory-optimized tables and disk-based tables at the same time.

Interoperability:

Refers to the interpreted type of Transact-SQL that references memory-optimized tables.

Transact-SQL that can be compiled:

That is, traditional interpretative Transact-SQL. When interoperating is used, memory-optimized tables will access all Transact-SQL zones, but you should not expect to get the same performance as using local compiled stored procedures. When you run an ad hoc query, interop is the right choice, or it is used before the application migrates to in-memory OLTP. This use is a step in the best Performance critical program migration process. You can also use a compiled Transact-SQL when you need to access both memory-optimized tables and disk-based tables.

When Transact-SQL uses interop to access memory-optimized tables, there are only a few features that are not supported:

· TRUNCATE TABLE;

· MERGE (target of memory-optimized tables);

· Dynamic and key-value-set-based cursors (these are automatically demoted to static cursors);

· Cross-database queries;

· Cross-database transactions;

· Linked servers;

· Locking hints: TABLOCK, XLOCK, Paglock, etc. (Nolock support);

· Isolation level hints readuncommitted, readcommitted readcommittedlock;

· Memory-Optimized table types and tables are not just variables supported by CTP1.

Locally compiled stored procedures:

Refers to an object type that is supported by memory-resident OLTP, where memory-resident OLTP is compiled into machine code, and it is possible to further improve performance even more than using only memory-optimized tables. Another alternative is to use an interpreted-based Transact-SQL stored procedure, which is a commonly used, basic compiled stored procedure in SQL Server that only references memory-optimized tables.

includes in-memory OLTP component of SQL Server engine

While memory-resident OLTP is integrated with the SQL Server relational engine and can be accessed through the same interface, its behavior and performance are vastly different.

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "alt=" image "src=" http:// S3.51cto.com/wyfs02/m02/5c/03/wkiol1uzhimcro3jaahm7ran0nc969.jpg "border=" 0 "height=" 402 "/>

Whether a locally compiled stored procedure or Transact-SQL is called, the client application connects to the TDS processor in the same way as a memory-optimized table or disk-based table. You can see that interpreted Transact-SQL can access memory-optimized tables through interoperability, but locally compiled stored procedures can only access memory-optimized tables.

Using in-memory OLTP

The in-memory OLTP engine, starting with the June 2013 CTP, has become part of SQL Server 2014. The installation of in-memory OLTP SQL Server is part of the SQL Server Setup program. In-memory OLTP components can only be installed in a 64-bit version of SQL Server 2014, not compatible with 32-bit versions.

Preparing: Creating a sample Database

CREATE DATABASE Imoltpgo

First step: Set up the database to support in-memory OLTP

Any database that contains a memory-optimized table requires at least one memory_optimized_data filegroup. These filegroups are used to store data and delta files that are used by SQL Server to recover memory-optimized tables. Although creating a memory_optimized_data filegroup is almost identical to the syntax for creating a regular FILESTREAM filegroup, you must specify the contains Memory_optimized_data option.

There are two ways to add the MEMORY_OPTIMIZED_DATA filegroup and the container that holds the FileStream file.

using T-SQL implementation:

ALTER database IMOLTP Add FILEGROUP imoltp_mod CONTAINS memory_optimized_dataalter DATABASE imoltp ADD FILE (name= ' imoltp_ Mod1 ', filename= ' E:\SQL-DATA\imoltp_mod1 ') to FILEGROUP Imoltp_modgo

using the SSMs implementation:

1. In the Object Explorer, expand the Database node, right-click the database, and click Properties.
650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/5c/03/wkiol1uzhirdzusyaaflsoqsv90953.jpg "border=" 0 "height=" 472 "/>

2. Add a new file group of memory-optimized data, click on the Filegroups page. Under the Memory OPTIMIZED data option, click Add Filegroup and set the values for the filegroup.
650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "alt=" image "src=" http:// S3.51cto.com/wyfs02/m01/5c/03/wkiol1uzhivr2jfgaagwxi0tb_0433.jpg "border=" 0 "height=" 506 "/>

3. Add files to the file group and click on the General page. Under Database files, click Add and enter the values for the file. The File type is set to FileStream Data.
650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "alt=" image "src=" http:// S3.51cto.com/wyfs02/m02/5c/03/wkiol1uzhiyxzvsnaagmgble21s375.jpg "border=" 0 "height=" 504 "/>

Step two: Create a memory-optimized table

The syntax for creating an optimized memory table is almost identical to the syntax for creating disk-based tables, with only a few limitations and the required extensions. Specifies that the table is a memory-optimized table that is implemented with the memory_optimized= on statement. A memory-optimized table can have only the following columns that can support data types:

· bytes

· All integer types: tinyint, smallint, int, bigint;

· All money types: money, smallmoney;

· All floating-point types: float, real;

· All datetime types: datetime, smalldatetime, datetime2, date, time;

· Number and floating point type;

· All non-LOB character types: char (n), varchar (n), nchar (n), nvarchar (n), sysname;

· Non-LOB binary type: binary (n), varbinary (n);

· Unique identifier.

Note that the LOB data type is not available, that you cannot have columns of the XML type, CLR, or Max data type, and that all rows are limited to 8,060 bytes in length, and that there is no out-of-row data. In fact, the 8060-byte limit is executed when the table is created, so unlike a disk-based table, a memory-optimized table cannot create two varchar (5000) columns.

Memory-optimized tables can be defined by two durability values: Schema_and_data or schema_only, which are the default values. Memory-optimized tables are defined by durability=schema_only, which means that changes to the data in the table are not recorded, and the data in the table is not saved on disk. However, the schema is saved as part of the database metadata, so an empty table is available after the database is restored during a SQL Server restart.

As mentioned earlier, memory-optimized tables must have at least one index, but this requirement can be met by automatic index creation to support primary key constraints. Except for tables that are created with the schema_only option, you must have a declared primary key. At least one index must declare that a PRIMARY KEY constraint is supported. The following example shows that a primary key index is created as a partition index, where the statistic indicator must also be specified.

When you create a memory-optimized table, there are only a few other restrictions except for the columns of the data types that are listed:

· no DML triggers;

· No foreign key or check constraint;

· No identity column;

· There is no unique index except the primary key;

· Up to 8 indexes, including indexes that support primary keys.

Also, once a table has been created, the schema is immutable. Instead of using ALTER TABLE, you will need to delete and recreate the table. In addition, there are no specific index DDL commands (such as creating an index, modifying an index, deleting an index). Indexes are always created as part of table creation.

We currently have two tables, ' ShoppingCart ' and ' usersession '. ' ShoppingCart ' is a persisted table (the default), which means that the contents of the table are stored on disk and are not lost because of a server crash. ' Usersession ' is a non-persisted table (durability=schema_only), which means that the contents of the table are stored only in memory and the server restart is lost.

Note: SQL 2014 memory-optimized tables support nonclustered hash indexes (hash index) and nonclustered indexes (rang index). The Bucket_cout recommended value for a hash index is 4 to 8 times times the number of unique index key values that can be found in the table.

The following two ways to create a memory-optimized table.

using T-SQL implementation:

USE imoltp
The GO
-- durable table
The CREATE TABLE dbo. ShoppingCart (
ShoppingCartId int not null primary key nonclustered hash with
(bucket_count = 2000000),
UserId int not null index ix_UserId nonclustered hash with (bucket_count=1000000),
CreatedDate datetime2 not null,
TotalPrice money
)
WITH (MEMORY_OPTIMIZED = ON)
The GO
-- non-durable table
The CREATE TABLE dbo. UserSession (
SessionId int not null primary key nonclustered hash with (bucket_count=400000),
UserId int the not null,
CreatedDate datetime2 not null,
ShoppingCartId int,
Index ix_UserId nonclustered hash (UserId) with (bucket_count=400000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
The GO

using the SSMs implementation:

1. In the object Explorer, right-click the tables node of your database, click New, and then click Memory Optimized Table. You can then see the template that created the memory-optimized table.
650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/5c/03/wkiol1uzhi2r0lhkaaeugpqwejs973.jpg "border=" 0 "height=" 287 "/>

2. Replace the parameters in the template, and in the Query menu, click Specify Values for Template Parameters. Shortcut keys are ctrl-shift-m.
650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "alt=" image "src=" http:// S3.51cto.com/wyfs02/m01/5c/03/wkiol1uzhi7sc1h3aaizsipriae928.jpg "border=" 0 "height=" 374 "/>

3. After execution, confirm that the table is created.
650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "alt=" image "src=" http:// S3.51cto.com/wyfs02/m01/5c/09/wkiom1uzhpog--jaaabhiietxz8627.jpg "border=" 0 "height=" 123 "/>

Step Three: Load data

You can load data into a table in several ways, including INSERT. SELECT from an already existing table and bcp stored on disk.

-- insert a few rows
INSERT dbo.UserSession VALUES (1,342,GETUTCDATE(),4)
INSERT dbo.UserSession VALUES (2,65,GETUTCDATE(),NULL)
INSERT dbo.UserSession VALUES (3,8798,GETUTCDATE(),1)
INSERT dbo.UserSession VALUES (4,80,GETUTCDATE(),NULL)
INSERT dbo.UserSession VALUES (5,4321,GETUTCDATE(),NULL)
INSERT dbo.UserSession VALUES (6,8578,GETUTCDATE(),NULL)
INSERT dbo.ShoppingCart VALUES (1,8798,GETUTCDATE(),NULL)
INSERT dbo.ShoppingCart VALUES (2,23,GETUTCDATE(),45.4)
INSERT dbo.ShoppingCart VALUES (3,80,GETUTCDATE(),NULL)
INSERT dbo.ShoppingCart VALUES (4,342,GETUTCDATE(),65.4)
GO
-- verify table contents
SELECT * FROM dbo.UserSession
SELECT * FROM dbo.ShoppingCart
GO

Fourth step: UPDATE STATISTICS

Memory-Optimized tables do not support auto_update_statistics, so statistics need to be updated manually. You can use UPDATE STATISTICS to update statistics for individual tables or sp_updatestats to update statistics for all tables in the database.

-- update statistics on memory optimized tables
UPDATE STATISTICS dbo.UserSession WITH FULLSCAN, NORECOMPUTE
UPDATE STATISTICS dbo.ShoppingCart WITH FULLSCAN, NORECOMPUTE
GO

Fifth step: Execute the Query

Optimized memory tables can be accessed in two different ways: either through a compiled Transact-SQL interop, or through a locally compiled stored procedure.

Now you are ready to execute the query. Because queries require access to memory-optimized tables, they benefit from an unlocked data structure, which increases the efficiency of data access. Here are some examples:

-- in an explicit transaction, assign a cart to a session and update the total price.
-- note that the isolation level hint is required for memory-optimized tables with
-- SELECT/UPDATE/DELETEstatements in explicit transactions
BEGIN TRAN
UPDATE dbo.UserSession WITH (SNAPSHOT) SET ShoppingCartId=3 WHERE SessionId=4
UPDATE dbo.ShoppingCart WITH (SNAPSHOT) SET TotalPrice=65.84 WHERE ShoppingCartId=3
COMMIT
GO
-- verify table contents
SELECT * FROM dbo.UserSession u JOIN dbo.ShoppingCart s on u.ShoppingCartId=s.ShoppingCartId
WHERE u.SessionId=4
GO

Sixth step: Create a locally compiled stored procedure

To further optimize the access to memory-optimized tables and to optimize the execution of your business logic, you have the option of creating locally compiled stored procedures. These stored procedures are created using Transact-SQL, but do not support the full transaction-sql environment. Refer to the Books Online for specific details.

The following is an example of a locally compiled stored procedure that accesses a table that we created earlier.

-- natively compiled stored procedure for assigning a shopping cart to a session
CREATE PROCEDURE dbo.usp_AssignCart @SessionId int
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N‘us_english‘)
DECLARE @UserId int,
@ShoppingCartId int
SELECT @UserId=UserId, @ShoppingCartId=ShoppingCartId
FROM dbo.UserSession WHERE [email protected]
IF @UserId IS NULL
THROW 51000, ‘The session or shopping cart does not exist.‘, 1
UPDATE dbo.UserSession SET [email protected] WHERE [email protected]
END
GO
EXEC usp_AssignCart 1
GO

The following stored procedure tests the performance of a locally compiled stored procedure by inserting a large number of rows of data into the memory-optimized table. The script inserts 1,000,000 rows of data.

It is important to note that if the write things log file becomes an application performance bottleneck, SQL Server allows you to completely remove the write transaction log by using a non-persisted table (durability=schema_only).

-- natively compiled stored procedure for inserting a large number of rows
-- this demonstrates the performance of native procs
CREATE PROCEDURE dbo.usp_InsertSampleCarts @StartId int, @InsertCount int
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N‘us_english‘)
DECLARE @ShoppingCartId int = @StartId
WHILE @ShoppingCartId < @StartId + @InsertCount
BEGIN
INSERT INTO dbo.ShoppingCart VALUES
(@ShoppingCartId, 1, ‘2013-01-01T00:00:00‘, NULL)
SET @ShoppingCartId += 1
END
END
GO
-- insert 1,000,000 rows
DECLARE @StartId int = (SELECT MAX(ShoppingCartId)+1 FROM dbo.ShoppingCart)
EXEC usp_InsertSampleCarts @StartId, 1000000
GO
-- verify the rows have been inserted
SELECT COUNT(*) FROM dbo.ShoppingCart
GO

To create a locally compiled stored procedure using SSMS:

1. In the Object Explorer, right-click the Stored procedures node in your database, click New, and then click Natively Compiled Stored Procedure. Templates that create locally compiled stored procedures will be displayed on the interface.
650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/5c/09/wkiom1uzhpth2iqgaaeh2b8j2e0242.jpg "border=" 0 "height=" 329 "/>

2. Replace the parameters in the template, click Specify Values for Templates Parameters under the Query menu. Shortcut keys are ctrl-shift-m.
650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "alt=" image "src=" http:// S3.51cto.com/wyfs02/m02/5c/03/wkiol1uzhjcbfji8aai-lr5pegq857.jpg "border=" 0 "height=" 372 "/>

SQL Server Feature Support

Many SQL Server features support memory-resident OLTP and databases that contain memory-optimized tables. For example, AlwaysOn components, log shipping, and database backup and recovery are fully supported. However, database mirroring and replication are not supported. You can use SQL Server Management Studio to support memory-optimized tables and SSIS work.

For a complete list of supported and unsupported features, see the SQL Server memory resident OLTP documentation.

Summarize

SQL Server memory-resident OLTP provides extremely efficient management for creating and running memory optimizations, enabling performance optimizations for OLTP workloads. Truly multiple versions of active concurrency control, without the need to lock in during access. All memory-resident OLTP memory-optimized tables must have at least one index, and all access to such tables is required through these indexes. In addition to a limited number of cases, memory-resident OLTP memory-optimized tables can act as transactions based on the same reference in a disk table. Locally compiled stored procedures are the quickest way to make calculations with your memory-optimized tables and performance business logic.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1626514

New SQL Server 2014 features-in-memory OLTP (In-memory OLTP)