Creating an awesome In-Memory Database

 Feb 01, 2016

Creating your very first In-Memory Database.

With the SQL Server 2014, Microsoft unveiled the inclusion of a new type of engine that would supposedly increase the performance of your queries. This engine is known as the In-Memory OLTP engine and it gains its performance through memory optimisation. This engine was part of the performance evolution and was drawn from the Vertipaq technology that Microsoft already released in an earlier edition of SQL.

When looking at this engine a little closer there are some areas it addresses as part of its functionality, these areas are the following:

  • Optimised tables and indexes that were designed to be stored in memory.
  • Procedures are compiled to native code for higher efficiency, these procedures are also optimised by the SQL query optimiser at creation.
  • Locks and latches are eliminated by the new MVCC (Multi Version concurrency control) mechanism.

In order to use these new In-memory capabilities your will have to alter your existing database by adding a new Filegroup that supports In-Memory optimisation, otherwise you can create a new database and add the Filegroup immediately. In my example, I am going to stick to the bare minimal.

To create this database run a normal T-SQL command that creates a database, such as the following:

in-memory database

Once you have that database in existence you have to create a Filegroup that supports the In-Memory optimisation. (You can only have one of these per Database)

in-memory database

Next, we need to have at least one container within the Filegroup that will store our DLLs that are generated by the compiler. To do this, you need to add a file to the FileGroup that you created in the previous step, although this won’t be a file but a folder.

in-memory database

Once you have created this container, your file system will now have a corresponding folder where the compiler can store the DLL files that it needs to create.

in-memory database

These DLL files will store the metadata and schema of the objects you create in your In-Memory container. Also be aware that although this database support In-memory features, it can still contain any native SQL objects that you wish to have in the database.

The database you just created is visible within the object explorer, this gives you the ability to look at the properties of the DB in the SQL Server Management Studio

in-memory database

How do your Excel skills stack up?   

Test Now  

About the Author:

Auret Swanepoel  

As a recent addition to the New Horizons team, Auret is a highly skilled and qualified IT Technical trainer. He has been a Microsoft Certified Trainer (MCT) since 2008 and has since then, also become a Microsoft Certified Professional (MCP), a Microsoft Certified Technology Specialist (MCTS) and a Microsoft Certified Information Technology Professional (MCITP). With his international experience as a trainer in South Africa, Auret is able to adapt his teaching style to different audiences in the classroom and ensure that students are learning in a positive and collaborative environment.

Read full bio
top
Back to top