SQL Server VM in Azure – Using In-Memory Tables

azure-vmmemory-optimized-tablessql serversql server 2014

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

Best Answer

  • 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.
Related Question