THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: The Hekaton Saga Continues, and some answers to questions

My Hekaton (aka In-memory OLTP) books is with the publisher. And I have no doubt they will do a much better job than my previous publisher did in bringing my SQL Server 2012 Internals Book into the light of day. 

My only regret is that my awesome Tech Editor, Benjamin Nevarez, who has been with me since my SQL Server 2008 Internals book, was not available for this book since he is now writing books of his own! But he recommended the person who tech edited his book and I’m pretty sure I can trust Ben’s judgment on this. I’m intentionally not mentioning his name in this post, but I’m sure it will come up in a future post.

I gave my first full day Hekaton presentation as a precon in Atlanta on May 2. I was worried that I wouldn’t have enough content to fill an entire day, but it pretty much worked out perfectly. As expected, there were some questions I couldn’t answer on the spot, either because I was too jet lagged, or because I just hadn’t thought about it enough yet, but I have some of those answers now, thanks to Sunil Argawal and Jos de Bruijn at Microsoft.


1. Are default constraints allowed on memory-optimized tables?

YES! However, there is a bug in the memory-optimization advisor that shows them as unsupported. In addition, not every expression that would be supported for defaults on disk-based tables is supported for defaults on memory-optimized tables. Only expressions that are allowed in natively-compiled procedures are allowed in default constraints on memory-optimized tables, but this does include a lot of the most frequently used ones: sysdatetime(), newid() and constants.

2.  Are the data values in the rows compressed in any way.

NO! No compression is used to store the data values in the rows.

3. If a transaction fails after it is has obtained its timestamp, but then fails during validation, will its timestamp be reused?

NO! Timestamps are never reused once they have been assigned.

4. How is the timestamp used for reading rows determined?

Every transaction that reads rows from memory-optimized tables reads as of a certain ‘point in time’, i.e. a timestamp. It then can only read row versions that were inserted after the read before the read timestamp, and not deleted until after the read timestamp (or not deleted yet at all!).  SQL Server maintains a current timestamp used for all read operations, which is incremented when a read/write transaction enters validation and is assigned its own timestamp. It can also be incremented for internal system transactions that are never visible to users. So basically, the read timestamp is the timestamp of the most recent committed transaction in the system.  Multiple read operations can share the same timestamp used for their reads.

5. The documentation says that cross-database transactions are not allowed with memory-optimized tables. What about transactions involving temp tables (so they are in tempdb)?

Since there can be no memory optimized tables in tempdb, this restriction does not apply. Cross-database transactions are allowed with tempdb and model, and read-only cross-database transactions are allowed with master.

6. Can an identity value in a memory-optimized table be reseeded?

(Note: earlier versions of Hekaton did not support identity at all, and I still get questions from people thinking that is still true. Hekaton tables can have identity columns as long as the seed is 1 and the increment is 1.) No reseed is possible but you can use IDENTITY_INSERT to manually increase the seed.

7.  Will DBCC CHECKDB check memory-optimized tables?

NO! DBCC CHECKDB does not include any checks on any part of Hekaton. However, checksums are maintained for the checkpoint files, and explicit checksums are supported for database backups, even those containing memory-optimized tables.

8.  IF a checkpoint file grows larger than 128K due to large transactions, are there special considerations on merging it with neighboring files? E.g. is SQL Server more aggressive in merging large files?

A checkpoint file pair (CFP) can be self-merged if the data file is larger than 256 MB and over half of the rows are marked deleted. Otherwise no…

9.  Can transaction logs containing operations on memory-optimized tables be used for recovery to a point in time or to a log mark?


10. Can the checkpoint files be excluded from virus scanners?

Right now, there is no easy way to do this since the checkpoint files have no suffix. However, the team at Microsoft will be looking into this.


Thanks for all the great questions!


And if you want more Hekaton details, check out the class I recorded for SSWUG a couple of weeks ago:  



Published Monday, May 12, 2014 2:05 PM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement