Available in SQL Server 2014 Enterprise Edition
In-Memory OLTP is a new lock-free, latch-free, optimistic concurrency approach to storing data in memory. While it may seem reminiscent of DBCC PINTABLE is it definitely not the same since data in memory-optimized tables is not organized in pages, and does not use the Buffer Pool. It is designed to eliminate locking delays by incurring less latching and locking for high volume insertion workloads and can drastically improve performance. It gives you a way to get around locking and latching issues without implementing partitioning which requires code changes.
Create a MEMORY OPTIMIZED DATA filegroup:
Note: You can have only one filestream filegroup for memory optimized tables but you can create multiple files and place them into the filegroup.
In SSMS, go to the properties of a database that you want to enable to use In-Memory OLTP and click on the Filegroups page. Under MEMORY OPTIMIZED DATA, click Add filegroup and then enter a name for the filegroup.
Then add a file to the filegroup, click the Files page, click Add and then set up the new file with a Logical Name, File Type = FILESTREAM Data and Filegroup = the Memory Optimized filegroup that was just created.
Create Memory Optimized Tables:
Create memory optimized tables by specifying the MEMORY_OPTIMIZED = ON clause to differentiate it from regular disk based tables. Specifying the MEMORY_OPTIMIZED = ON clause tells the In-Memory OLTP engine to handle this table instead of the SQL database engine. You might wonder how data is put back into memory especially after a server restart of crash… This is done by SQL recovering objects by reading data into memory from the transaction log and checkpoint files on recovery. BUCKET COUNT and DURABILITY must be set when you create the In-Memory table.
CREATE TABLE [RoadRaces]([RaceID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 500000), [RaceName] VARCHAR(500) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX [RName] HASH WITH (BUCKET_COUNT = 500000), [LengthMiles] INT NULL, [State] CHAR(2) NULL
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
BUCKET COUNT and DURABILITY
When done a dll and other files will be created. You can find them in this folder: <Drive letter>:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\DATA\xtp\<database id>\ You should notice that there will be a new XTP folder and a subfolder with a number which is the database id. The number on the end of the dll name is the object id of the table that was just created.
Memory optimized tables can have up to 8 non-clustered indexes but no clustered index and must be created when the table is created. Indexes can be created on string columns but only if they use a BIN2 collation. You can query the BIN2 collations to choose from by checking sys.fn_helpcollations. When indexes are created you cannot drop or modify the index so you must plan well before you create it. There are 2 types of indexes (Hash Index and Range Index) and all are inherently covering indexes meaning it will virtually contain all of the columns in the table.
Create Natively Compiled Stored Procedures:
You can also create natively compiled stored procedures that use the In-Memory OLTP engine. These stored procedures can only access memory optimized tables. Creating a natively compiled stored procedure is very much like creating an In-Memory OLTP table. The WITH NATIVE_COMPILATION option tells SQL that the procedure is to be handled by the In-Memory OLTP engine.
CREATE PROCEDURE [dbo].[NewRoadRace] @RaceID INT, @RaceName VARCHAR(500), @LengthMiles INT, @State CHAR(2)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = ‘us_english’)
INSERT INTO dbo.RoadRaces (RaceID, RaceName, LengthMiles, [State]) VALUES (@RaceID, @RaceName, @LengthMiles, @State)
Now let’s put all the parts into motion. Execute the stored proc just like you would any other stored proc.
EXECUTE [dbo].[NewRoadRace] @RaceID = 100, @RaceName = ‘Run The Country 5K’, @LengthMiles = 3.1, @State = ‘TX’
EXECUTE [dbo].[NewRoadRace] @RaceID = 101, @RaceName = ‘Run The Country 10K’, @LengthMiles = 6.2, @State = ‘TX’
And query the In-Memory OLTP table as usual.