SQL Server 2014 In-Memory OLTP vs Redis

Question

Is SQL Server 2014's In-Memory OLTP (Hekaton) the same or similar concept with Redis?

I use Redis for in-memory storage (storage in RAM) and caching, while having a separate SQL Server database (like StackExchange does). Can Hekaton do the same thing?

Magdalenmagdalena

Answer 1

They're similar by both being primarily in-memory, but that's about it.

Redis is an in-memory key-value database. It can persist data to disk if configure it, but it keeps the entire dataset in memory so you need enough RAM for that. The key-value architecture allows various different data types so you can store a value as a simple string or lists, sets, hashes, etc. Basically all the data structures you can use inside of a programming language are available in Redis natively.

SQL Server Hekaton (In-Memory OLTP) is a new engine designed to run relational tables in memory. All the data for these tables is kept in RAM but also stored to disk so they are fully durable.

Hekaton can take individual tables in a SQL Server database and run them in a different process using MVCC (instead of pages and locks) and other optimizations so operations are thousands of times faster than the traditional disk-based engine. There is a lot of research that went into this and the primary use-case would be to take a table that is under heavy load and switch it to run in-memory to increase performance and scalability.

Hekaton was not meant to run an entire database in memory (although you can do that if you really want to) but rather as a new engine designed to handle specific cases while keeping the interface the same. Everything to the end-user is identical to the rest of SQL Server: you can use SQL, stored procedures, triggers, indexes, atomic operations with ACID properties and you can work seamlessly with data in both regular and in-memory tables.

Because of the performance potential of Hekaton, you can use it to replace Redis if you need the speed and want to model your data within traditional relational tables. If you need the other key-value and data structure features of Redis, you're better off staying with that.

---

With SQL 2016 SP1 and newer, all tiers of SQL Server now have access to the same features and the only difference is pricing for support and capacity.

Phenomenalism

However, Redis supports various expiration schemes for each key-value pair, while SQL Server does not, correct? Or there is something built-in for that too? – Trapezohedron

@Trapezohedron Redis supports a single expiration scheme by using TTL on keys. No relational database has the same feature but you can easily add a column for ExpirationTime to select only active rows and delete old rows in a background job. – Phenomenalism

True. I wish there where some performance comparisons between Redis and SQL Server 2016 with in-memory tables. I have decided to go with SQL Server now, but it would be nice to have some extra data. But I guess no-one cares to compare things that originally were not meant to be used for the same purpose. – Trapezohedron

@Trapezohedron Redis is more of a key/value cache than a fully persistent datastore so yes comparisons are tough and it might win on latency because of it. Redis is single-threaded though so SQL Server will scale much better by using multiple cores. SQL version 2019 is in preview now, I'd advise looking at the latest version instead of 2016. – Phenomenalism

@Trapezohedron someone tried this though as noted in the comments, I can't tell if he overloaded the RAM and started paging (writing to disk anyway) – Lomasi

Answer 2

Firstly you need the enterprise edition (very expensive) of SQL Server to use Hekaton (In-Memory OLTP). Note you have to pay for sql server per CPU, adding more workload to SQL server may require you to have more CPU and therefore a lot more licence costs.

But unlike Redis, you can have a trigger or stored proc update your “in memory cache” as part of the database transaction. You may also find that Hekaton is fast enough that you don’t need a separate set of caches from your main tables.

So yes, Hekaton can do the same as Redis, but it is unlikely to be sensible to use it in that way unless its usage does not cost you much.

Hekaton comes into its own when it allows you to process a lot more data without having to invest in the programming cost of re-designing your system to make use of caching with Redis or otherwise.

Dree

Just a note, OLTP is available in SQL Azure. So it is a more cost effective option if applicable. – Unpin

Quoting from Mani's update above: "With SQL 2016 SP1, all tiers of SQL Server now have access to the same features and the only difference is pricing for support and capacity." – Plough