Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER HEKATON’

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

diskbased_vs_memory_optimized.1.1

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.

diskbased_vs_memory_optimized.1.2

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.

S.No

Type

Memory Optimized Table

Disk Based Table

1

Query Cost related to the Batch

7%

93%

2

Statistics Time

93 ms

234 ms

3

Statistics IO

NO IO involvement

1962 read

Let me know about your test results.

Advertisements

Read Full Post »

Recently, I was doing some research on memory optimized table and I came across a situation where I needed to keep some temporary data in the tables. So I thought of creating temporary table. Now I have two choices, either to go for disk based temporary table or to go for memory optimized temporary table.
I decided to create a memory optimized temporary table.

Given below is the script.

USE hkNorthwind
GO
--DROP TABLE #tbl_Product_Master
--GO
CREATE TABLE #tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 10770, Level 16, State 92, Line 11
The feature ‘temporary tables’ is not supported with memory optimized tables.

Opssssssss error….

If I am not wrong, you cannot create a memory optimized temporary table because this feature is not supported by memory optimized table. So I started my research and tried to find out the work around that can help me create a memory optimized temporary table. Finally I came to know how to create a temporary table in memory optimized table.

Given below is the script.

USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--GO
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY)

GO
--OUTPUT

As you can see, I just added one argument (DURABILITY = SCHEMA_ONLY) at the time of memory optimized table and because of this argument, the table is converted to a temporary table.

Let me know if you came across this issue and its solution.

Reference : MSDN

Read Full Post »

Today, I came across a situation where I had to get the total number of records in each Memory optimized table in a particular database.
I remember developing a solution earlier (How to get the number of records in a disk based tables. ). I tried this solution on memory optimized table but it did not work. Ooopssssssss.
Given below is the script that we use to get the number of records in a disk based table. I included the table type in the query to demonstrate the problem.

USE hkNorthwind
GO
SELECT
  OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema Name]
, OBJECT_NAME(OBJECT_ID) As [Table Name]
, SUM([rows]) as [Total Records]
,(CASE WHEN OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
  And OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=0
  THEN 'User Table'
  WHEN OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
  And OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=1
  THEN 'Memory Optimized Table'
  ELSE NULL
  END) As [Table Type]
FROM sys.partitions
WHERE
(OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
OR
OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=1)
GROUP BY OBJECT_ID
ORDER BY OBJECT_NAME(OBJECT_ID)
--OUTPUT

memory optimized table record count1.1

As you can see in the above example, it only returned the total number of records in disk based tables and returned zero for memory optimized table. So I started doing my research and found the solution. Given below is the script.

SOLUTION :

USE hkNorthwind
GO
SELECT
  OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema Name]
, OBJECT_NAME(OBJECT_ID) As [Table Name]
, SUM([rows]) as [Total Records]
FROM sys.partitions
WHERE
(OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
And OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=0)
GROUP BY OBJECT_ID

UNION

SELECT OBJECT_SCHEMA_NAME(Ind.OBJECT_ID) AS [Schema Name]
,OBJECT_NAME(Ind.OBJECT_ID) As [Table Name]
,ISNULL([rows],0)  as [Total Records]
FROM sys.hash_indexes AS Ind
CROSS APPLY sys.dm_db_stats_properties(Ind.object_id,Ind.index_id)
WHERE Ind.index_id =2
ORDER BY OBJECT_NAME(OBJECT_ID)
--OUTPUT

memory_optimized table_record_count.1.2

Note : Before executing the above query make sure that all your tables’ stats are up to date.

Let me know if you came across this issue and its solution.

Read Full Post »

Memory optimized table is a new concept to maintain records in table with high performance. I already discussed this new type of table earlier in my article. So, I kept on researching for this new feature and came across an issue. The issue is, I once created a memory optimized table and inserted few records in it and did other research work on the test database and once I was done, I just shut down my machine. Next morning when I tuned on my machine, I found no data in the memory optimized table. First I thought I deleted the data by mistake and forgot it. So I repeated the process but the following morning same thing happened to me again. I could not comprehend what on earth was going on !!  I was puzzled and started doing my research and finally resolved it.

Let me explain it step by step. (Never apply these steps on production database)

Step 1 :
Create a memory optimized table in a memory optimized table enabled database.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--GO
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO

Step 2 :
Insert records in the memory optimized table.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
INSERT INTO tbl_Product_Master VALUES (1, 'SQL Server 2012',GETDATE())
INSERT INTO tbl_Product_Master VALUES (2, 'SQL Server 2014',GETDATE())
GO

Step 3 :
Browse the memory optimized table.

--This script is compatible with SQL Server 2005 and above.
USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO

Memory Optimized table myth.1

Step 4 :
Now either restart the database server or shutdown your test machine and turn it on.
Never do this exercise on production server without taking proper precautions.
Browse the table again.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO

Memory Optimized table myth.2

Step 5 :
Opsssss, now you can see the data gone and the reason behind this is, just a parameter that we passed at the time of memory optimized table creation and this is DURABILITY because if you make DURABILITY = SCHEMA_ONLY the schema will be durable but not the data. Once you restart the database server, you will lose your data. Remember, whenever you create a memory optimize table, to  keep the durable data (permanent data). Always use WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA).

Note : If you create the memory optimized table without passing DURABILITY parameter, by default it will be DURABILITY = SCHEMA_AND_DATA.

Lets recreate the memory optimize table. Given below is the script. Do the same process again without step 1 and your data will remain with you.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--GO
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Please let me know if you come across this issue and its resolution.

Reference : MSDN

Read Full Post »

‘Update statement’ is one of the frequently used Data Manipulation Language (DML) in SQL Server scripting.  We usually use this statement to rectify the data. Recently, I was working on memory optimized table and I tried to update a primary key column and I came across an error.
Given below is the script that we usually use to update any disk based table (normal table). Lets try the same script for memory optimized table.

USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--GO
--Create table
CREATE TABLE tbl_Product_Master
(
 [Product ID] INT not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Product Name] NVARCHAR(100),
 [Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON)
GO
--Insert record into the table
INSERT INTO tbl_Product_Master
([Product ID],[Product Name],[Creation Datetime])
VALUES (1, 'SQL Server 2012',getdate())
GO
--Update table
Update tbl_Product_Master SET [Product ID]=11 WHERE [Product ID]=1
GO

Msg 10770, Level 16, State 10, Line 40
The operation ‘primary key update’ is not supported with memory optimized tables.

Oooops.. I am unable to update a primary key in memory optimized table. So how to update the primary key of a memory optimized table?

Let me explain it step by step.

Step 1 :
In this step, you need to delete the record which you need to update .

USE hkNorthwind
GO
DELETE FROM tbl_Product_Master WHERE [Product ID]=1
GO

Step 2 :
In this step, you need to insert the record with the updated data.

USE hkNorthwind
GO
INSERT INTO tbl_Product_Master
([Product ID],[Product Name],[Creation Datetime])
VALUES (11, 'SQL Server 2012',getdate())
GO

Step 3 :
Browse the table and check whether the data has been updated or not.

USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO
--OUTPUT

update_PK_in_memory_optimized_Table1.1

Read Full Post »

‘How to implement default constraints in the memory optimized table’ seems very simple to me and I tried to implement in the same way, as we usually do it in the disk based table. But the output was not as per the expectation. Let me show you the output.

USE hkNorthwind
GO
CREATE TABLE tbl_Product_Master
(
 [Product ID] INT not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Product Name] NVARCHAR(100),
 [Creation Datetime] datetime default Getdate()
) WITH (MEMORY_OPTIMIZED = ON)
GO

Msg 10770, Level 16, State 13, Line 3
The feature ‘default constraints’ is not supported with memory optimized tables.

Oooops.. I am unable to create a default constraint in memory optimized table.

So, can we create a default constraint column in memory optimized table ? The answer is No & Yes.

Why No: Because you cannot create a default constraint column in memory optimized table.
Why Yes:Because you can implement the default constraint functionality in memory optimized table using stored procedure.

Let me create the table & stored procedure to implement default constraints in the memory optimized table.

Step 1 :
First of all, you need to create a memory optimized table without any default constraint.
Given below is the script.

USE hkNorthwind
GO
CREATE TABLE tbl_Product_Master
(
 [Product ID] INT not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Product Name] NVARCHAR(100),
 [Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON)
GO

Step 2 :
Once you have created the memory optimized table, create a stored procedure to insert records in it.

USE hkNorthwind
GO
CREATE PROCEDURE usp_Insert_Product_Master
@ProductID int,
@ProductName nvarchar(100)
AS
INSERT INTO tbl_Product_Master
([Product ID],[Product Name],[Creation Datetime])
VALUES (@ProductID,@ProductName,getdate())

Step 3 :
Insert the records using stored procedure in the memory optimized table.
Given below is the script.

USE hkNorthwind
GO
EXEC usp_Insert_Product_Master 1, 'SQL Server 2012'
GO
EXEC usp_Insert_Product_Master 2, 'SQL Server 2014'

Step 4 :
Just browse the table and check whether the default constraint is implemented or not.

USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO
--OUTPUT

implement_deafult_constraints.1.1

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »

In my earlier article, I had discussed about how to implement identity column in the Memory optimized table. Now, lets try to create a BLOB data type column in memory optimized table.

Given below is the script that we usually use to implement BLOB data type columns in the disk based table (normal table). Lets try the same script for memory optimized table.

USE hkNorthwind
GO
CREATE TABLE tbl_sample
(
 [ID] integer not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null,
 [Address] varchar(Max)
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 10770, Level 16, State 94, Line 3
The type ‘varchar(max)’ is not supported with memory optimized tables.

Oh no.. I am unable to create a BLOB data type column in memory optimized table and as we know, BLOB data types are one of the most commonly used data types in the database.

So what is the solution?

The solution is very simple. What you need to do is, actually split the above table into two tables and one as memory optimized table and 2nd one should be disk based table. And the logic behind this is, we will keep all the data types columns except BLOB in memory optimized table and will put all the BLOB data types column in disk based tables. But do not forget to keep the same ID in both tables.
Given below is the script.

USE hkNorthwind
GO
CREATE TABLE tbl_sample_Memory_table
(
 [ID] integer not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null,
 ) WITH (MEMORY_OPTIMIZED = ON)
GO

CREATE TABLE tbl_sample_Normal_Table
(
 [ID] integer not null,
 [Address] varchar(Max)
)
GO
--OUTPUT

Command(s) completed successfully.

As you can see that, both tables are created successfully.

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »

Older Posts »