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.