Feeds:
Posts
Comments

Archive for the ‘SQL SERVER HEKATON’ Category

In my earlier article, I wrote a solution to create memory optimized table in a new database. But the question is how to create memory optimized table in an existing database. Let me explain it step by step.

Step 1 :
In this step we need to create a simple database to demonstrate. Skip this step if you already have a database but make sure its compatibility level is 110.

USE master;
GO
CREATE DATABASE test
ON
( NAME = Sales_dat,
    FILENAME = 'C:\DATA\test.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = 'C:\DATA\test_log.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;
GO
--OUTPUT

Step 2 :
Create a memory optimized table.

USE Test
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
--OUTPUT

Msg 41337, Level 16, State 0, Line 3
The MEMORY_OPTIMIZED_DATA filegroup does not exist or is empty. Memory optimized tables cannot be created for database until it has one MEMORY_OPTIMIZED_DATA filegroup that is not empty.

Ooopps…… I am unable to create memory optimized table in TEST database (existing database).

So, what’s the remedy for it ?

Step 3 :
Create a file group, add file in it containing memory optimized data option. Given below is the script.
Do not forget to change the path in the script.

USE [master]
GO
ALTER DATABASE [test] ADD  FILEGROUP [test_fg]
CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE [test] ADD FILE
( NAME = N'test_fg', FILENAME = N'C:\DATA\test_fg.ndf')
TO FILEGROUP [test_fg]
GO
USE [test]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups
WHERE is_default=1 AND name = N'test_fg')
ALTER DATABASE [test] MODIFY FILEGROUP [test_fg] DEFAULT
GO
--OUTPUT

Step 4 :
Once the file group is created, just create the memory optimized table and this time it will be created successfully.

USE Test
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
--OUTPUT

Command(s) completed successfully.

Step 5 :
Now, browse the table and check whether it is created or not.

USE Test
GO
SELECT * FROM tbl_sample_Memory_table
--OUTPUT

memoryoptimizedtables_exisiting1.1

Conclusion :
Whenever you create a memory optimized table, you must consider two things before creating it.
1- The database must have the compatibility level 110
2- Database must have a file group having CONTAINS MEMORY_OPTIMIZED_DATA

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 »

The %S_MSG ‘%ls’ is not supported with memory optimized tables. is one of the new error messages in SQL Server Hekaton. This error message is related to Memory optimized tables feature, a new type of table shipped with SQL Server Hekaton.

Let’s discuss this in detail:
Message Number: 10770

Severity : 16

Error Message: The %S_MSG ‘%ls’ is not supported with memory optimized tables.

Error Generation:
Let me create a sample memory optimized table and insert few records in it to demonstrate this error.

USE Sample_DB
GO
CREATE TABLE tbl_sample
(
[ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100000),
[Name] VARCHAR(50) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

INSERT INTO tbl_sample ([ID],[Name]) VALUES (1,'raresql-1')
GO
INSERT INTO tbl_sample ([ID],[Name]) VALUES (2,'raresql-2')
--OUTPUT

Given below is the script that I tried to execute but it resulted with the following error :

TRUNCATE TABLE tbl_sample
--OUTPUT

Msg 10770, Level 16, State 102, Line 17
The statement ‘TRUNCATE TABLE’ is not supported with memory optimized tables.

Ooopps…… I am unable to execute it.

Resolution:
Memory optimized tables does not support truncate statement, So, instead of truncate you can use delete statement for memory optimized tables.

Lets rewrite the above statement using delete. Given below is the script.

USE Sample_DB
GO
DELETE FROM tbl_sample
--OUTPUT

(2 row(s) affected)

Lets browse the table and view either the records are deleted or not.

USE Sample_DB
GO
SELECT * FROM tbl_sample
--OUTPUT

truncate memory optimized table 1.1

Read Full Post »

Memory optimized table is a new feature introduced in SQL Server Hekaton.  As it is new for all of us, I am doing my research with different aspects of memory optimized table. In this article, we will create an identity field in the memory optimized table.

Given below is the script that we usually use to implement identity 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 identity(1,1) not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 10771, Level 16, State 8, Line 3
The feature ‘identity column’ is not yet implemented with memory optimized tables.

Oooops.. I am unable to create identity column in memory optimized table.

So, how can we create an identity column in memory optimized table ? Here comes the SEQUENCE object solution to the rescue.

Let me create an identity column in memory optimized table in sequential steps.

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

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

Step 2 :
Once you have created the memory optimized table, create a Sequence object.

USE hkNorthwind
GO
CREATE SEQUENCE [Seq_Sample]
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 1000
GO

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

USE hkNorthwind
GO
INSERT INTO tbl_sample ([ID],[Name])
VALUES (NEXT VALUE FOR [Seq_Sample],'raresql-1')
GO
INSERT INTO tbl_sample ([ID],[Name])
VALUES (NEXT VALUE FOR [Seq_Sample],'raresql-2')
GO

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

USE hkNorthwind
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

identity_field_for_memory_optimized_table.1.1

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »

In my earlier article, I explained how to create memory optimized table. However, once I created memory optimized tables, I came across a problem segregating the disk based tables, file tables & memory optimized tables.

SQL Server Hekaton comes to the rescue with an addition of a field(column) namely is_memory_optimized in sys.tables (system table) which facilitates segregation. Given below is the script.

USE hkNorthwind
GO
SELECT
  object_id
, name  As [Table Name]
,(CASE WHEN is_filetable=0 AND is_memory_optimized=0
 THEN 'DISK BASED TABLE'
 WHEN is_filetable=1
 THEN 'FILE TABLE'
 WHEN is_memory_optimized=1
 THEN 'MEMORY OPTIMIZED TABLE' END)
 AS [Table Type]
FROM sys.tables
--OUTPUT

memoryoptimizedtables1.1

Note : You can download the hkNorthwind database from here.

Let me know if you know a better solution.

Read Full Post »

Memory Optimized Table is a new concept introduced in SQL Server Hekaton. Memory optimized table is also a table but the major difference between memory-optimized tables and disk based tables is that the memory optimized table information is available in the memory and it does not require the pages to be read into cache from disk. The performance of memory optimized table is much higher than disk based table. Shall discuss its performance in the upcoming article.

Let me create the memory optimized table, but before proceeding with the creating memory table, we should know that whenever you create memory optimized table the database must have memory-optimized data filegroup.
Lets first create a database having memory-optimized data filegroup. Given below is the script.

CREATE DATABASE Sample_DB
ON
PRIMARY(NAME = [Sample_DB],
FILENAME = 'C:\DATA\Sample_data.mdf', size=500MB)
,FILEGROUP [hekaton_demo_fg] CONTAINS MEMORY_OPTIMIZED_DATA(
NAME = [hekaton_demo_dir],
FILENAME = 'C:\DATA\Sample_dir')
LOG ON (name = [hekaton_demo_log]
, Filename='C:\DATA\Sample_log.ldf', size=500MB)
;
GO

Once you create the database having memory-optimized data filegroup then you can create memory optimized table. Given below is the script.

USE Sample_DB
GO
CREATE TABLE tbl_sample
(
[ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100000),
[Name] VARCHAR(50) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Read Full Post »

In the earlier versions of SQL Server, whenever you need to rebuild an index (pass DDL statement for an online index), you sometimes end up with deadlock situation. Given below is the script that we use to rebuild an online index in the earlier version of SQL Server.

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

In SQL Server Hekaton, a solution to this problem has been introduced namely WAIT_AT_LOW_PRIORITY . It allows DBA to control the locking mechanism that is required to rebuild an online index and causing deadlock situations.
Given below is the script :

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY
   ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
    DATA_COMPRESSION = ROW
)
;

In the above script, you can see two arguments MAX_DURATION and ABORT_AFTER_WAIT. Given below are the details.

MAX_DURATION :
It describes the waiting time in minutes, but you can just pass the integer number and MINUTES can be removed from the syntax.

ABORT_AFTER_WAIT:
ABORT_AFTER_WAIT came up with three nice options. It basically provides you the hands on the different locking mechanism. Given below are the details :

NONE : It implements no locking on the online index rebuild operation,it performs operation like a normal scenario.
Given below is the script if you would like to implement NONE in ABORT_AFTER_WAIT.

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY
   ( MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE ) ),
    DATA_COMPRESSION = ROW
)
;

SELF: It aborts the online index rebuild operation by using normal priority. It gives priority to the user operation instead of rebuilding index.
Given below is the script if you intend to implement SELF.

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY
    ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = SELF) ),
    DATA_COMPRESSION = ROW
)
;

BLOCKERS: It kills all the user transactions that usually block the online index rebuilding, so that you can rebuild index easily. This is not recommended if you are using at the peak hours.
Given below is the script if you would like to implement BLOCKERS.

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY
   ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS) ),
    DATA_COMPRESSION = ROW
)
;

Let me know if you implement it in CTP 1 and find any issues.

Read Full Post »

Whenever new technology steps in, we usually run for its sample database to test it features, functionality & enhancement, etc. When I started testing SQL Server Hekaton, I was looking for some sample databases. Finally, I grabbed it from msdn but there, it is script only. So you need to copy and execute it but if you need a downloadable version, you can download it from here (Northwind database).

Note : Make sure you have data folder in c: drive (C:\data)

Read Full Post »

« Newer Posts