When Have You Used MOT Tables? T-SQL Tuesday #1176
August 13, 2019 by Kenneth Fisher
Steve Jones (b/t) is our host for T-SQL Tuesday and in case you didn’t know has taken over running T-SQL Tuesday from Adam Machanic (b/t). Thanks for all of the hard work both of you!!
The subject of the month is MOT (Memory Optimized tables). And I’ll be honest, I’ve never used them. (That was a short post wasn’t it?)
Fortunately, Steve left the opening for a blog on why I’ve never used them. Just so you know, I think this is an awesome technology. Did you know that MOT tables actually don’t take locks? They are the real NOLOCK. They are also insanely fast!
Even though I’ve never used them I have done some reading about them and even listened to Kalen Delaney (b/t) talk about them a few times. So why I haven’t I used them yet? Three reasons primarily.
- Memory requirements – Memory Optimized Tables require memory. My company is kind of stingy on memory so most of our servers don’t have enough memory (and aren’t likely to get enough memory) to handle even small MOTs.
- My position – I advise our developers on ways to optimize their code I don’t write a lot of code myself.
- SQL Version – We just finished our upgrade project taking our servers from SQL 2008 R2 to 2016.
That last one is the most important of course. Now that we are using 2016 I fully expect to start seeing some MOT in our environment, so I guess it’s time to start playing with them at home. In particular, I’m looking forward to using SCHEMA_ONLY durable tables for staging tables and temp tables.
Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tuesday | Tags: T-SQL Tuesday
6 thoughts on “When Have You Used MOT Tables? T-SQL Tuesday #117”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Thank you for summarizing, we really have to use this amazing technology more. Another thing that stops us at this moment – need to rewrite existing code to accommodate In-Memory OLTP, and this is not applicable for vendors applications.
Yea, that’s a good point. We have that issue some too.
I have most of the same constrains, memory is to “expensive”
Got to love it right? 🙂 Not that I’m the one paying for it.
When I saw the title, I thought you mentioned “Mother of All Tables” , a kind of supertable which every database have…
[…] Ken Fisher has a few notes about why he hasn’t used them. Not enough memory in his systems, older versions, and he doesn’t write the code. Apparently I’ve given him some things to think about and play with, and I’m sure I’ll see more blogs from him soon. […]