Book Review – SQL Server Internals: In-Memory OLTP

Book Review, Community, SQL

In-Memory OLTP – a potential game changing technology

Every once in a while a technology comes out that has the potential to change things dramatically. In-Memory OLTP (Hekaton) is one of them. The design team set out with a goal of reaching an order of magnitude improvement over existing technologies and techniques. To do so they had to rethink key facets of the relational database system, latching, locking, logging and statement compilation. When a technology as potentially disruptive as this comes along it gets everyone’s attention. When an opportunity to review a book based on this technology came along it certainly is worth the effort. I spent a ton of time with this book (maybe a little too much), reading and re-reading chapters but it was worth every minute.

SQL Server Internals: In-Memory OLTP by Kalen Delaney ( twitter | blog ) is one of the first books published dedicated to the topic and definitely will be (is) the go-to book on the topic. The author is a well known SQL Server internals expert that has an extensive history of top-tier publications and presentations and this book is no exception.

SQL Server Internals: In-Memory OLTP

In-Memory OLTP introduces a fundamental shift in how the database engine stores and accesses data. This book walks you through each of these topics with almost perfect precision. I would suggest reading this book from beginning to end. This way, when needed, you can refer back to a chapter and have an understanding of its context in the larger system.

  • The first three chapters are the preliminaries of In-Memory OLTP. Chapter 2 introduces the basics and some of the limitations of the technology and in chapter 3 the reasons behind those limitations become clear with the discussion of row structure. These chapters bring into light that we’re dealing with something new and unique. A traditional in-memory data structure that respects the ACID properties of a transactional relational database system
     
  • I felt than the second half of Chapter 4 on range indexes struggled to eloquently describe the technical nuances of the bw-tree data structure. I read this chapter several times and still struggled. Also, it is not entirely clear what the benefit of the mapping table is, I had to refer to the research paper for clarity here
      
  • Chapter 5 describes transactions and the opportunistic locking design that In-Memory OLTP uses. In contrast to pessimistic locking which requires latches and locks, opportunistic locking uses a validation technique at the time a transaction attempts to commit it’s data. Even thought it’s covered in detail in other texts, I would have liked deeper coverage on read phenomena here.
     
  • The best chapter in the book is Chapter 6 logging, checkpoint and recovery. Logging and recovery are completely re-engineered in In-Memory OLTP. This chapter walks you through the entire process culminating with an exceptional discussion on the recovery process. This chapter is so smooth it feels like a conversation.
     
  • Chapter 7 describes native compilation of tables and stored procedures, it’s interesting that after the bottlenecks of disk, latching and locking are removed we’re left with compilation as a potential bottleneck. This chapter presents the techniques used by In-Memory OLTP’s native compilation feature and it’s limitations.
     
  • The book concludes with a chapter on configuration best practices and where In-Memory OLTP fit in the database world.  This part of the book is key for those that have to evaluate a technology for it’s architectural fit into their data-tier.

There are few shortcomings with this book, the bw-tree could have been more clearly explained. Parts of the book may be hard to approach for some, but it is covering a bleeding-edge topic that is still being actively researched. The book toes the line between IT topic and graduate level computer science. I’m surprised I didn’t see some complexity analysis in some of the chapters :)

My detailed chapter notes are available here 

In summary:

This is the book that you will reach for when you have a question about how it works. I really look forward to when In-Memory OLTP matures enough that some it current limitations are relaxed.

Thank you to Kalen and the folks at RedGate for this release!

If you need help evaluating In-Memory OLTP and where it fits in your data-tier or system architecture**, please feel free to contact me at aen@centinosystems.com**

Follow me on Twitter @nocentino