We all know, retrieving data from physical memory (AKA RAM) is faster than the data saved in physical disk (HDD). Memory-optimized tables save & retrieve data into/from physical memory rather than Hard-disk. This could be excellent feature to solve lot of use cases such as online game where only end results matters.
This feature was introduced with SQL version 2014 and continuous improvement are being done in consecutive releases. In lot of places, you would see this feature named as “Hekaton” OR “In-Memory optimization”.
Following a short demo may help to develop your understanding regarding Memory table & performance comparison with disk based table.
-- Create InMemoryTableDemo database. USE [master] CREATE DATABASE [InMemoryTableDemo] GO -- Create Memory optimized File Group. USE [master] ALTER DATABASE [InMemoryTableDemo] ADD FILEGROUP MemoryGroupFG CONTAINS MEMORY_OPTIMIZED_DATA -- Add the container for file group. ALTER DATABASE [InMemoryTableDemo] ADD FILE (NAME = AWMemFile, FILENAME = N'F:\FGData\FG1') TO FILEGROUP MemoryGroupFG GO
In my following example, I have set ‘Durability’ to ‘schema_only’ which means only the schema will be recoverable in case of crash as the rows will remain in memory. The other option is ‘Schema_and_Data’ that protect table schema as well rows, the copy of data will also save in HDD.
-- Create Memory Based Table. USE [InMemoryTableDemo] CREATE TABLE TableInMemory ( ID INT CONSTRAINT [pk_TableInMemory_ID] PRIMARY KEY NONCLUSTERED IDENTITY (1,1), EMPID INT, CharData VARCHAR(100) ) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY ) GO
Let’s create a disk based table with same columns.
-- Create Disk Based Table. USE [InMemoryTableDemo] CREATE TABLE TableInDisk ( ID INT CONSTRAINT [pk_TableInDisk_ID] PRIMARY KEY NONCLUSTERED IDENTITY (1,1), EMPID INT, CharData VARCHAR(100) ) GO
Now we are inserting 100k rows and it takes only (approximately) 4 seconds to insert in memory based table whereas disk based table takes (approximately) 200 seconds.
-- Test 100,000 inserts into a Memory Based Table DECLARE @start DATETIME = GETDATE(); DECLARE @count INT = 1; WHILE @count < 100000 BEGIN INSERT INTO TableInMemory VALUES (@count, 'stuff'); SET @count = @count + 1; END SELECT DATEDIFF(s, @start, GETDATE()) AS [Memory Insert] GO -- Test 100,000 inserts into a Disk Based Table DECLARE @start DATETIME = GETDATE(); DECLARE @count INT = 1; WHILE @count < 100000 BEGIN INSERT INTO TableInDisk VALUES (@count, 'stuff'); SET @count = @count + 1; END SELECT DATEDIFF(s, @start, GETDATE()) AS [Memory Insert] GO
Let’s test the retrieval of rows. Run following 3 queries together while enabling ‘Include actual execution plan’ OR ‘Live query statistics‘.
-- Run following 3 queries together while enabling 'include actual execution plan' OR 'Live querystatistics' USE [InMemoryTableDemo] SELECT * FROM TableInDisk SELECT * FROM TableInMemory GO
You would notice Memory table takes (approximately) 16 % resources as compare to disk based table that takes 84 % of resources. Additionally storage & I/O Cost columns will give the details of they type of query.
More details on Memory optimized table.