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.
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
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.
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
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.