With this post we are giving you a brief introduction to the new syntax for in-memory OLTP, and will show you how to get started with T-SQL and SQL Server Management Studio (SSMS).
Before we start, let’s create a sample database. If you have an existing database you can skip this step.
-- optional: create database
CREATE DATABASE imoltp
GO
SSMS: To create a database,
1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
2. Right-click Databases, click New Database and then enter the value for the database name.
Step 1: enable your database for in-memory OLTP
We are going to add a filegroup for memory_optimized_data to our database, and add a container to this filegroup. 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.
When creating the container in the memory_optimized_data filegroup you must specify the storage location. In this example we picked the folder ‘c:\data’. Make sure the folder exists before running the script.
-- enable for in-memory OLTP - change file path as needed
ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\data\imoltp_mod1') TO FILEGROUP imoltp_mod
GO
SSMS: To add a memory_optimized_data filegroup and its container,
1. In Object Explorer, expand the Databases node, right-click your database and then click Properties.
2. To add a new memory optimized data filegroup, click the Filegroups page. Under MEMORY OPTIMIZED DATA, click Add filegroup and then enter the values for the filegroup.
3. To add a file to the filegroup, click the General page. Under Database files, click Add and then enter the values for the file. Use file type FILESTREAM Data.
Step 2: create your first memory-optimized table
We are now ready to create our first memory-optimized tables. We have here two tables, ‘ShoppingCart’, and ‘UserSession’. ‘ShoppingCart’ is a durable table (the default), which means that its contents are persisted on disk and will not be lost on a server crash. ‘UserSession’ is a non-durable table (DURABILITY=SCHEMA_ONLY), which means that the contents of the table exist only in memory, and are lost on server restart.
Note that in CTP1 memory-optimized tables support only ‘nonclustered hash’ indexes. The bucket_count of the index should be roughly 1 to 2 times the number of unique index keys you expect to find in the table.
-- create memory optimized tables
USE imoltp
GO
-- durable table – contents of this table will not be lost on a server crash
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)
GO
-- non-durable table – contents of this table are lost on a server restart
CREATE TABLE dbo.UserSession (
SessionId int not null primary key nonclustered hash with (bucket_count=400000),
UserId int 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)
GO
SSMS: To create a memory-optimized table,
1. In Object Explorer, right-click the Tables node of your database, click New, and then click Memory Optimized Table. A template for creating a memory-optimized table is displayed.
2. To replace the template parameters, click Specify Values for Template Parameters on the Query menu. The shortcut key is Ctrl-Shift-M.
Step 3: load your data
You can load data into the tables in various ways, including INSERT .. SELECT from an existing disk-based table and BCP. In this example we are using simple INSERT statements for loading the data.
-- Basic DML
-- 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
SSMS: To view the contents of a memory-optimized table,
⦁ In Object Explorer, right-click on your memory-optimized table, click on Script Table as, click on SELECT To, click on New Query Editor Window and then execute the query that is displayed.
Step 4: update statistics
Memory-optimized tables do not support auto_update_statistics, thus statistics will need to be updated manually. You can use UPDATE STATISTICS to update statistics for individual tables, or sp_updatestats 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
Step 5: run queries
You are now ready to run your queries. Because they access memory-optimized tables, these queries will benefit from the latch-free data structures and more efficient data access. Here are a few 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/DELETE statements 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
Step 6: create natively compiled stored procedures
To further optimize the access to memory-optimized tables, and to optimize execution of your business logic, you can create natively compiled stored procedures. While these procedures are written using Transact-SQL, they do not support the full Transact-SQL surface area. For details, see Books Online.
Here is an example of a natively compiled stored procedure that accesses the tables we created previously.
-- 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 SessionId=@SessionId
IF @UserId IS NULL
THROW 51000, 'The session or shopping cart does not exist.', 1
UPDATE dbo.UserSession SET ShoppingCartId=@ShoppingCartId WHERE SessionId=@SessionId
END
GO
EXEC usp_AssignCart 1
GO
The following stored procedure showcases the performance of natively compiled stored procedures by inserting a large number of rows into a memory-optimized table. This scripts inserts 1,000,000 rows.
Note that if log IO becomes a bottleneck in the application, SQL Server allows you to use a non-durable table (DURABILITY=SCHEMA_ONLY), which removes the log IO completely.
-- 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
SSMS: To create a natively compiled stored procedure,
1. In Object Explorer, right-click the Stored Procedures node of your database, click New, and then click Natively Compiled Stored Procedure. A template for creating natively compiled stored procedures is displayed.
2. To replace the template parameters, click Specify Values for Template Parameters on the Query menu. The shortcut key is Ctrl-Shift-M.
For more details about the concepts for in-memory OLTP, as well as a reference of the syntax, see Books Online or get started and download SQL Server 2014 CTP1 here.
That’s all for today, but stay tuned for further posts on this blog!
http://blogs.technet.com
