Welcome to this month’s T-SQL Tuesday post ,which is about when I have used Memory Optimized Tables (MOTs). Now, this month’s T-SQL Tuesday is hosted by the organizer Steve Jones, and I suspect he stepped in at the last minute.
Steve invites us all to write about when we have decided whether to use MOTs. You can read more about the invite in detail by clicking on the T-SQL Tuesday logo above.
To clarify, MOTs are used within the In-Memory OLTP technology within SQL Server.
I’ll be honest here I used MOT tables the most when I learned about them for my MCSE.
In fact, I ended up going through a white paper and the entire first book about them by Kalen Delaney.
I have frequently considered using them more in the workplace. Especially for databases that have either a high number of transactions on or use staging tables for ETL processes.
However, the main issue has been redesigning the architecture to cater for them. Especially on older versions of SQL Server where there less supported features.
You can read more about unsupported features for SQL Server 2017 MOTs in detail here.
In addition, for some servers it would have meant getting more memory for them. You can read about the memory requirements in detail here.
Popularity of Memory Optimized Tables
Generally speaking, MOTs appear to have a popularity issue.
For instance, I currently work with a large estate that has nearly 2000 instances and as far as I am aware only a limited number of those instances use them.
However, after looking into them to gain my MCSE I must admit they do have their uses.
In fact, I have spoken to others in the community who have used them, and they have stated they have been of great use.
Personally, I think they are underrated just like filtered indexes. I truly hope over time they both become more popular.
Maybe, just maybe, we’ll see something appear in SQL Server 2019 that will give them the significant boost in popularity they obviously need.
Final word
Well I hope you enjoyed my T-SQL Tuesday contribution for this month. If you’re one of the people who uses these tables, feel free to comment about your use of them.
If anybody else is looking to find out more about MOTs for your SQL Server exams you can read my old post about exam tips here.
For those of you who are thinking about submitting your own T-SQL Tuesday contribution I actively encourage you to do so for so many reasons.
Be First to Comment