Hekaton Part 4: Simple Performance comparison - In Memory vs Disk table

Continuing on the Hekaton Series, this post will attempt a very basic performance comparison of In Memory and disk based table.

Table Creation and Data Loading

CREATE TABLE dbo.Disk_tbl
(
[ID] Int identity(1,1) Not null PRIMARY KEY CLUSTERED,
[Data] char(32) COLLATE Latin1_General_100_BIN2 null,
[dt] datetime not null
);
GO
 
Insert into Disk_tbl(data,dt) Select 'xyz',getdate()
GO 10000
 
Insert into Disk_tbl(data,dt)
Select 'xyz',getdate() from Disk_tbl
GO 10

A Similar script for Hekaton table is provided below. Note that clustered primary key is replaced with Hash Index as cluster indexes are not supported in memory tables.

CREATE TABLE dbo.HK_tbl
(
[ID] Int identity(1,1) Not null PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
[Data] char(32) COLLATE Latin1_General_100_BIN2 null,
[dt] datetime not null,
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
 
Insert into HK_tbl(data,dt) Select 'xyz',getdate()
GO 10000
 
Insert into HK_tbl(data,dt)
Select 'xyz',getdate() from HK_tbl
GO 10
 

Performance comparison: 

   Please note that memory needs to be cleaned to have a

   genuine comparison between disk based table and In 

   memory table. "DBCC DROPCLEANBUFFERS" helps us on

   the same.  The query just picks one row out of 10 Million

   rows by filtering on the primary key column

 

   DBCC FREEPROCCACHE;

   DBCC DROPCLEANBUFFERS;

   SET STATISTICS IO ON

   Declare @dt Datetime

   SET @dt = getdate()

   Select * from HK_tbl where id = 100234

   Print datediff(ms,@dt,getdate())

   SET @dt = getdate()

   Select * from disk_tbl where id = 100234

   Print datediff(ms,@dt,getdate())


Performance comparison by time:

Result of the script provided below

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(1 row(s) affected)

0

(1 row(s) affected)

Table 'Disk_tbl'. Scan count 0, logical reads 3, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

16



In Memory tables" take 0 ms compared to 16 ms on disk based tables.

Performance comparison by IO:

Statistics I/O on doesn't work on In Memory tables as they seldom involve physical I/O. Disk based tables indicate 3 Physical I/Os.

Performance comparison by Cost:

Query plan indicates that query Disk based table had 99% of query cost while "In Memory" table took only 1% of total resource utilized.



On all counts, "In Memory" tables do outperform disk based tables. However, the scenario considered is a simple scenario and as this series progresses we will analyse with more in depth details and scenarios.