Creating a table to be in memory compared to standard tables that use the files on disk can have several performance benefits for high transaction databases.

In memory tables store data in server memory compared to disks and can help eliminate locks and latches on objects.  In memory tables were introduced in SQL Server 2014 and the capabilities have been upgraded in the various new versions of SQL Server.

Packaged with the latest versions of SSMS is a conversion wizard to help you identify and convert good target tables to in memory.  The in memory tables can be classified as durable and non-durable, this all depends on how important your data is and whether or not you want to recover that data if a server crashes or reboots.  Furthermore you can specify the durability at the schema or schema and data level with the commands below:

WITH (MEMORY_OPTIMIZED = ON,

          DURABILITY = SCHEMA_ONLY);

WITH (MEMORY_OPTIMIZED = ON,

          DURABILITY = SCHEMA_AND_DATA);

Before creating or converting a table to in memory you must first create a file group and create a file in the file group to be used by the database for the in memory table.  These files host metadata/delta files related to the table.  Below you can find some samples in creating the file group and a file.

ALTER DATABASE DATABASE_NAME

ADD FILEGROUP [File_group_name] CONTAINS MEMORY_OPTIMIZED_DATA;

Next add a file to the file group with the following sample:

ALTER DATABASE Database_Name

ADD FILE (NAME=’File_Name’, FILENAME=’File_Location’)

TO FILEGROUP [File_Group_Name];

If you want to check and make sure you database is ready to add the in memory table run the following:

USE Database_Name

SELECT g.name, g.type_desc, f.physical_name

 FROM sys.filegroups g JOIN sys.database_files f ON g.data_space_id = f.data_space_id

 WHERE g.type = ‘FX’ AND f.type = 2

The hard part is now finished, you have laid down the ground work and foundation for the in memory table, next up is actually creating the table in memory.  In order to do this only a few commands are required in your table creation script:

WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY);

Of course make sure you change the durability setting to what works best for you.

The post Steps to Create an In-Memory table for SQL Server appeared first on VitaminDBA.