Tag: in-memory OLTP

T-SQL Tuesday #117 Invitation – When Have You Used MOT Tables?
When Memory-Optimized Tables (MOT) were announced for SQL Server, there was a lot of excitement about the technology. After this was released on SQL Server 2014, feelings waned with a lot of restrictions and limitations for using the technology. I remember a panel at a conference years ago where most of the MVPs and experts recommended against using the technology for most users.

Today there have been improvements (2017, 2016) in the MOT features, restrictions have been removed, and all editions can use MOT tables. That’s not to say that this is suitable for every table or situation where a DBA or developer suspects performance issues.

This month I want to ask you about when you’ve made that decision. This can be to use MOT tables or NOT to use MOT tables. This could be a simple thought, a POC, or actual testing of the feature.

Some ideas for you to write about:
- Performance analysis of MOT tables that affected a decision
- Reading the limitations and knowing this would prevent their use
- A scenario where MOT tables improved performance
- A successful implementation of MOT tables and what needed to change in your app
- A failed attempt at trying MOT tables
There might be other things that are related to MOT technology, but let us know this month what you think of the technology and how it has (or has not) impacted your application.

The Rules

Here are the rules as set out for the T-SQL Tuesday blog party.
1. Your post should be published on Tuesday, Aug 13, 2019 between midnight UTC and 11:59:59 UTC.
2. Include the T-SQL Tuesday logo in your post.
3. Link back to this invitation.
4. Include a comment on the invitation post or a trackback link.
5. Enjoy the chance to be creative and share some knowledge.
August 8, 2019
Practical Hekaton

I got into a debate recently with someone about the In-Memory OLTP tables (formerly Hekaton) feature that was released with SQL Server 2014. This person asked if anyone was actually using the technology, given all the limitations that were placed on these structures in this first release.

I’ve had quite a few people actually get upset with Microsoft releasing a “feature” that doesn’t just work as a replacement for the regular tables in SQL Server. They feel that Microsoft should have spent more time working to make the transition to these tables seemless, perhaps not releasing in-Memory tables until SQL Server 2016.

I feel differently. I think that Microsoft should get the technology working and stable, and then release it. Even if it has limitations, I’d like to see an implementation available early and give companies the chance to see how it performs in real world workloads. If you can’t workaround the limitations, then you can’t use it, but then does it matter if the feature is available in 2014 or 2016? I’m not sure it does.

We would really like to publish more guidance here, and help people understand the feature better. If anyone is out there actually using Hekaton in production, or has a development project underway that should get to production soon, we’d like to contact you and work out a way to publish information. You’re welcome to write an article, or we can write one for you with a little interview time, either way is fine; we’d just like to get some practical, real world information out for everyone to learn from.

Please contact us if you’re using In-Memory OLTP tables and natively compiled stored procedures.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

June 11, 2015

Project Hekaton

If you haven’t heard about Hekaton by now, you should think about reading up on it. There’s some information from TechNet, a keynote video, and more. Despite the ranting from Oracle’s Bob Evans in 2012 that this was “vaporware”, it’s coming in SQL Server 2014. The latest demos don’t show 100x increase in query speed, but they 30x or so numbers I’ve seen are very impressive.

Hekaton is an in-memory database technology, actually called In-Memory OLTP in Books Online. This isn’t a re-invention of the pintable concept from SQL Server 2000. Instead it’s a very well though out architecture and rather interesting in the way it’s implemented. The keynote video gives you a deep overview, though to use the tables, most of us don’t need to know the internals of how they work.

However we do need to look at the restrictions, err requirements. Since these tables will exist in memory, obviously you need more memory, but more than you thought. The recommendation is twice the expected table size, to account for versioning. In addition, this memory is in addition to the buffer pool and other memory your instance already needs. That means much more complex memory calculations and monitoring for DBAs.

The complexity of SQL Server continues to grow over time as more and more features are added to the product. Many of us don’t need much more than the core relational database functionality for many applications, but the in-memory OLTP tables might be one feature that can help dramatically increase the speed of your application.

Steve Jones

Video and Audio versions

Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.