Hekaton (In-Memory OLTP) Tables In Use
Hekaton was a new way of storing data in SQL Server 2014. In-Memory OLTP can actually be really cool. Here are some of its advantages:
- It stores data differently in a way that eliminates latch waits
- If your stored procedures use a subset of T-SQL commands, they can get compiled to native code and run much faster
- If you don’t need to persist the data to disk, you can skip disk writes altogether and just have some RAM-only tables
But you need to understand the limitations, and they’ve changed a lot over the years. Click on the links to learn more about each restriction:
- Hekaton is designed for less than 250GB of data
- Your memory needs to be 2x the size of your data
- Hekaton is designed for no more than 4 CPU sockets or 60 cores
- Hekaton does not allow table alters
- Hekaton tables can’t have additional indexes added later
- Hekaton tables can’t have more than 7 non-clustered indexes
- Hekaton does not support cross-database queries
- Hekaton max row size is 8060 bytes
- Hekaton tables can’t have full text indexes
- Databases with Hekaton objects can’t have database snapshots
- Hekaton tables can’t be replicated
- Hekaton tables can’t have partitioning, computed columns, IDENTITY, foreign keys, check constraints, or unique indexes
Whew! That’s a lot of limitations – make sure you understand those fully, because it impacts how you manage a database with Hekaton tables. Things got better in 2016, though.
To Learn More About Hekaton
List out the Hekaton tables to identify:
- Their table sizes (keeping in mind their projected growth, your version’s memory limit, and the double-memory requirement)
- Their data storage (they may not be persisted to disk, so you’ll have an ugly surprise when rebooting the server)