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:
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)
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.
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.
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