1

I am running azure and considering deploying a SQL Server 2014 instance due to the in memory feature, however, I was looking through the documentation and couldn't seem to find how to configure In-Memory, is it enabled by default, what considerations for data do I need to take?

Does any one have any experience with the In memory feature in SQL Server

1
  • In-Memory is for making your heavily accessed table as memory optimized.
  • For a memory optimized table, all data is stored in memory and hence unlike disk based tables, pages don't need to be brought into the buffer pool or cache.
  • In order to create a memory optimized table, you need to create a database with a filestream filegroup (along with the use of CONTAINS MEMORY_OPTIMIZED_DATA)

    -- enable for in-memory OLTP - change file path as needed 
    ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA 
    ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\data\imoltp_mod1') TO FILEGROUP imoltp_mod 
    

    GO

Now for creating memory optimized tables you have to create table with WITH (MEMORY_OPTIMIZED=ON)

Getting Started with SQL Server 2014 In-Memory OLTP will give you all the detailed steps to get you started.

For more details, check this BOL article : In-Memory OLTP (In-Memory Optimization)


Update:

@cat you have to follow 3 step process

  1. Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP ,
  2. Determining if a disk based Table or Stored Procedure Should Be Ported to In-Memory OLTP -- informs you about which tables in your database will benefit if ported to use In-Memory OLTP and finally
  3. After you identify a table that you would like to port to use In-Memory OLTP, you can use the memory optimization advisor to help you migrate the disk-based database table to In-Memory OLTP.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.