When Have You Used MOT Tables? T-SQL Tuesday #117


August 13, 2019 by Kenneth Fisher

T-SQL TuesdaySteve 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.

6 thoughts on “When Have You Used MOT Tables? T-SQL Tuesday #117

  1. Anna says:

    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.

  2. I have most of the same constrains, memory is to “expensive”

  3. wfvdijk says:

    When I saw the title, I thought you mentioned “Mother of All Tables” , a kind of supertable which every database have…

  4. […] 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. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: