Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


What is In-Memory OLTP?
Published: Dec 05, 2020
This post has been a long time coming as this was a feature added in SQL Server 2014, just over 4 years ago now. Despite having learnt the ins and outs of the feature at the time and thinking it was a potential great leap forward, facts are that I rarely ever used it in any live situation, therefore other posts always came to the fore instead (as it’s a lot easier to knock up demos of features you’re currently using than trying to invent something completely from scratch with no use case). There’s also a LOT to explain with in-memory as it’s a complex beast and it seemed a waste to write so much about a feature that wasn’t really being used.

However, as time has passed I’ve started to utilize it a little more and therefore I’ve finally decided to put out some notes on the topic for anyone else also considering its use.

Firstly, what is it? Well, there are several answers to that:

Tables

For tables we have 2 options in which we can define whether we wish to persist the schema or schema and data. For schema only, SQL Server simply creates an empty table on startup which exists only in memory. Any data you place into that table is held solely in memory on the instance and never gets written to disk. Therefore, if you restart SQL Server you lose any data with only the schema remaining.

The other option is to persist schema and data. However, this data is not held on disk in the traditional way… SQL Server creates a dll to hold your schema and data on disk and, on server startup, it creates the table and sucks in the data. From that point on disk is out of the equation… there’s no logging for in-memory tables… you make your changes, do as you please, and when the SQL Server is shut down it will write your data back out to disk again.

Also, in-memory tables are lock free. This also allows for much greater access speed whilst maintaining data consistency and integrity. I’ll not go into detail on the locking mechanism here, maybe in a future post, but in essence it’s running snapshot isolation behind the scenes.

Procs and Functions

These can also be in-memory objects and are natively compiled in a variant of machine code and held as dll files on the file system in order to make it much faster to access and execute the code.

So why would you use it?

Well the answer to that is simple… speed. The idea of an in-memory object with no locking and no logging means, combined with dll compiled stored procedures can amount to immense processing speed in your system.



Now, that all sounds simple enough and there’s no reason not to use it, right? Well there are a large number of caveats with all of the above which can quickly remove some of the associated enthusiasm BUT, as I’ve found over time, in the right usage scenarios it is indeed powerful and handy… but you really do need the right use cases otherwise you’ll be left scratching your head as to whether the added complexity is really worth it.

I’ll discuss all of the above in the upcoming posts…

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.