SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

In Memory Optimized Table in SQL Server 2014

To increase performance of highly accessible tables, a new feature incorporated in Sql Server 2014 “In-Memory OLTP”.You can define a table that is accessed by large no of users at a time as “memory optimized”. Memory-optimized-tables are fully transactional, durable, and are accessed using T-SQL in the same way as disk-based tables.
In memory optimization is designed for extremely high session concurrency for OLTP type of transactions using latch-free data structures and optimistic, multi-version concurrency control.
-- Creating disk-based table.
CREATE TABLE [dbo].[PersonSimple] (

-- Creating memory-optimized table durable.
CREATE TABLE [dbo].[PersonMemOptimized] (
BUCKET_COUNT parameter is mandatory when you create the memory-optimized table. If you cannot determine the correct bucket count, use a nonclustered index instead. An incorrect BUCKET_COUNT value, especially one that is too low, can significantly impact workload performance, as well as recovery time of the database. It is better to overestimate the bucket count.
DURABILITY parameter defines table schema or table data with schema to be persisted when database is restarted.
-- Creating memory-optimized table non durable.
CREATE TABLE [NonDurablePersonMemOptimized]

If our database does not contain MEMORY_OPTIMIZED_DATA filegroup ,below error occurred on IMO table creation
Msg 41337, Level 16, State 100, Line 7
Cannot create memory optimized tables in a database that does not have an online and non-empty MEMORY_OPTIMIZED_DATA filegroup.

You can only create one memory-optimized file group per database. You need to explicitly mark the filegroup as containing memory_optimized_data.

You need to add one or more containers to the MEMORY_OPTIMIZED_DATA filegroup. As
ALTER DATABASE [ss2014Test] ADD FILE (name='ss2014TestIMO', filename='c:\IMOfg\ss2014TestIMO') TO FILEGROUP ss2014TestIMO

CREATE TABLE [dbo].[PersonMemOptimized] (
Now query works.

Important Limitations of IMO Filegroup:
·         Once you create a memory-optimized filegroup, you can only remove it by dropping the database. In a production environment, it is unlikely that you will need to remove the memory-optimized filegroup.
·         You cannot drop a non-empty container or move data and delta file pairs to another container in the memory-optimized filegroup.
·         You cannot specify MAXSIZE for the container.

Querying Microsoft SQL Server

I am a technology enthusiast and software developer by profession. I am developing .Net/database based enterprise applications from past 3 years.

My skills includes C# ,ASP.NET,SQL Server 2008 and MVC . My areas of interests are database development and application software development using Microsoft Technologies.


Leave a comment on the original post [queryingsql.blogspot.com, opens in a new window]

Loading comments...