SQL Server Portal

I have written multiple articles on memory optimized table and its handling. I was also working on its core area that is its performance. I continued with my research to see whether it really improves the performance or not. And as per my research, I found a massive improvement in the performance (except few limitations).

Before proceeding with the performance, I would like to create a sample to compare the performance between disk based & memory optimized table.

• Sample For Memory Optimized table :

Let me create sample tables for memory optimized table and insert bulk data in it to measure the performance.
Given blow is the script.

--Given below scripts are compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
--Create a memory optimized table
CREATE TABLE [dbo].[tbl_product_Master_MO]
(
[Product ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100000),
[Product Name] [nvarchar](100) NULL,
[Creation Datetime] [datetime] NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
--Insert 65536 records into the memory optimized table
--This script may take few minutes to insert records.

USE hkNorthwind
GO
;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y),
N5 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N4 AS X, N4 AS Y)
INSERT INTO tbl_product_Master_MO
SELECT n,'Number' + Convert(varchar(10),n),GETDATE()
from N5

---Create native compiled procedure to give boost to memory optimized table.
CREATE PROCEDURE dbo.usp_product_master
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN
ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
SELECT [Product ID],[Product Name],[Creation Datetime]
FROM dbo.[tbl_product_Master_MO]

END
GO

• Sample For Disk Based table :

Let me create sample tables for disk based table and insert bulk data in it.
Given blow is the script.

--Create a disk based table (Normal table)
USE [hkNorthwind]
GO
CREATE TABLE [dbo].[tbl_product_Master_DB](
[Product ID] [int] NOT NULL,
[Product Name] [nvarchar](100) NULL,
[Creation Datetime] [datetime] NULL
) ON [PRIMARY]
GO

--Insert 65536 records into the disk based table.
--This script may take few minutes to insert records.
USE hkNorthwind
GO
;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y),
N5 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N4 AS X, N4 AS Y)
INSERT INTO tbl_product_Master_DB
SELECT n,'Number' + Convert(varchar(10),n),GETDATE()
from N5
GO

Let’s proceed with the different categories of performance comparison between normal table and memory optimized table.

• Query Cost related to the Batch :

Let us start with the query cost related to the batch.
Given below is the script and its output.
Note : Given below results are tested on 300K~ records.

USE hkNorthwind
GO
--Memory optimzed table
Select * from [tbl_product_Master_MO]

--Disk based table
Select * from [tbl_product_Master_DB]
GO
--OUTPUT

As you can see that memory optimized table only took 7% but on the other hand disk based table took 93% for the same task.

• Time Statistics

Let’s turn on the time statistics and view the performance comparison.
Given below is the script

SET STATISTICS TIME ON
USE hkNorthwind
GO
---Given below is the Memory optimized native compiled stored procedure.
--This stored procedure we used to browse memory optimized table.
--It will give boost to memory optimized table performance.
EXEC usp_product_master
GO

--Disk based table
Select * from [tbl_product_Master_DB]
GO
SET STATISTICS TIME OFF
--OUTPUT

–For memory optimized
SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 1706 ms.

— For disk based table.
SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 3251 ms.

• IO Statistics 

Let’s turn on the IO statistics and you will be amazed to view that there is NO IO involvement in memory optimized table
and due to this, it produces a high level performance.
Given below is the script.

SET STATISTICS IO ON
USE hkNorthwind
GO
--Memory optimzed table
Select * from [tbl_product_Master_MO]
GO
--Disk based table
Select * from [tbl_product_Master_DB]
GO
SET STATISTICS IO OFF
--OUTPUT

–For memory optimized query
No Result.

— For disk based table query.
(327680 row(s) affected)
Table ‘tbl_product_Master_DB’. Scan count 1, logical reads 1962, physical reads 0, read-ahead reads 1584, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Conclusion :
I am very much impressed with the memory optimized table performance. But still it requires some improvement regarding BLOB data types & validations.
Given below is the consolidated summary that will give you a glance performance review of disk based vs memory optimized table.

Let me know about your test results.

Read Full Post »