In-Memory OLTP – Part 2

In last article “In-Memory OLTP Introduction” we discussed what is in-memory OLTP and how it works. We also got to know memory-optimized tables and natively compiled stored procedures. now we are going to be more familiar with creating in-memory objects.

Create Memory-Optimized Tables

To create memory-optimized tables, your database must contain a filegroup for in-memory OLTP data. To add a filegroup for memory-optimized data to a database you can use the ALTER DATABASE statement, as shown in the following example:[codesyntax lang=”tsql”]

ALTER DATABASE MyDB
ADDFILEGROUP mem_data CONTAINS MEMORY_OPTIMIZED_DATA;
GO
ALTER DATABASE MyDB
ADDFILE (NAME='MemData',FILENAME='D:DataMyDB_MemData.ndf')
TOFILEGROUP mem_data;

[/codesyntax]After file group and file creation is competed for memory-optimized data, you can create memory-optimized table which can include a hash index and the durability of the table data. By default, the durability option is set to SCHEMA_AND_DATA, so the data is persisted to filestream data which means in case of database server shut down or failure the data will be recovered from memory-optimized filegroup and transaction logs.[info] When the durability option is set to SCHEMA_AND_DATA, the data is written to disk as a stream, not in 8-KB pages as used by disk-based tables. [/info]You can specify the durability option to SCHEMA_ONLY, so only the table definition is persisted that means transactions on these tables do not require any disk I/O, however in the case of database server shut down or failure the data in these tables will be lost. [info]The ability to set the durability option to SCHEMA_ONLY is useful when the table is used for transient data, such as a session state table in a web server farm or data warehouse staging process. [/info]To create a memory-optimized table, execute a CREATE TABLE statement with the MEMORY_OPTIMIZED option set to ON, as shown in the following example:[codesyntax lang=”tsql”]

CREATE TABLE dbo.MemoryTable (
       OrderId INTEGER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000), 
       OrderDate DATETIME NOT NULL, 
       ProductCode INTEGER NULL, 
       Quantity INTEGER NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

[/codesyntax][info]All tables with durability option of SCHEMA_AND_DATA must include a primary key either single-column primary key or composite primary key. [/info]

Create Index on Memory-Optimized Tables

To create indexes in addition to the primary key, you must specify the indexes after the column definitions, as shown in the following example:[codesyntax lang=”tsql”]

CREATE TABLE dbo.IndexedMemoryTable (
       OrderId INTEGER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000), 
       OrderDate DATETIME NOT NULL, 
       ProductCode INTEGER NULL, 
       Quantity INTEGER NULL 
INDEX idx_MemTab_OrderDate NONCLUSTERED HASH(OrderDate) WITH (BUCKET_COUNT = 100000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

[/codesyntax]

Querying Memory-Optimized Tables

applications can query memory-optimized tables in two ways:

  • Query Interop. You can access memory-optimized tables using any Transact-SQL query or DML operation in the same way as traditional disk-based tables. Interop access refers to Transact-SQL batches or stored procedures other than a natively compiled stored procedure. In this way, the SQL Server query engine provides an interop layer to interpret the compiled in-memory table. this technique is useful to access both memory-optimized tables and disk-based tables.
  • Native Compilation. Native Compilation increase the performance of workloads that use memory-optimized tables. Memory-optimized tables are compiled when they are created. Natively compiled stored procedures also are compiled when they are loaded to native DLLs. Memory-optimized tables and DLLs are recompiled during the server restart.

[info] Note that the Actual Query Plan is only available for Interop queries, not for Natively Compiled stored procedures. [/info]

Create Natively Compiled Stored Procedure

Stored procedures that are marked with NATIVE_COMPILATION are natively compiled .To define these stored procedures, you must use CREATE PROCEDURE statements that the SQL Server query engine converts to native code, as shown in the following example:[codesyntax lang=”tsql”]

CREATEPROCEDURE [dbo].[usp_InsertData](@c1 int, @c3 int, @c4 int)
WITH NATIVE_COMPILATION, SCHEMABINDING 
AS BEGIN ATOMIC WITH 
( 
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' 
) 
  DECLARE @c2 datetime = GETDATE(); 
  INSERTINTO [dbo].[MemoryTable](OrderId, OrderDate, ProductCode, Quantity)
       values (@c1, @c2, @c3, @c4); 
END 
GO

[/codesyntax]In this example, NATIVE_COMPILATION determines that this stored procedure is a natively compiled store procedure. In addition, to create a natively compiled stored procedure these options are required:

  • SCHEMABINDING
  • BEGIN ATOMIC

The C version of the stored procedure is compiled into a DLL, which is loaded into memory. [info]You can only use natively compiled stored procedures to access memory-optimized tables; they cannot reference disk-based tables. [/info] 

Conclusion

In these two articles we discussed about memory-optimized tables and their features. These new data structures are part of SQL Server In-Memory OLTP Engine to achieve significant performance over traditional disk-based data structures. Furthermore, we saw simple examples on how we can define memory-optimized tables and indexes and natively-compiled stored procedures. Hope these two posts were informative, please share it with others if you think it worth to read and stay tuned to learn more about SQL Server. 

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan