Today was the 2nd day of the SQL PASS conference, and as always there was a keynote. Todays keynote was by Douglas McDowall who is the VP of Finance, Thomas LaRock who is the VP of Marketing, followed by Dr. David Dewitt from Microsoft.
Douglas talked to the members at large about the financial health of the PASS organization. PASS is proud to now have a $1M rainy day fund so that we can survive though rough years like the years that we had a few years ago.
Bill Grazino took the stage for a few minutes in order to thank out going board members Rushabh Mehta (Immediate Past President), Douglas McDowall (VP of Finance) and Rob Farley (Board Member).
When Tom LaRock took the stage he started by introducing the new members to the board of directors which included Jen Stirrup, Tim Ford and Amy Lewis. For the PASS BA Conference we are moving to San Jose during May 7-9th and for the PASS Summit we will be back in Seattle from November 4-7. Early Bird registration will only be open until mid-December, so if you plan to sign up for the early bird rate, be sure to sign up soon at sqlpass.org.
Dr. David Dewitt took the stage and started his keynote, which this year is all about Hekaton (aka. In Memory OLTP Tables). Hekaton is a memory-optimized yet fully durable high performance OLTP engine which has been integrated into SQL Server 2014. For Hekaton Microsoft did a major rewrite of the core engine of SQL Server to fit this new engine into the existing database engine as they didn’t want to introduce a new Windows service to run this new In Memory database engine.
On of the primary reason that Hekaton is so fast compared to the traditional database engine is because of the lack of latching and locking as they don’t exist when using Hekaton tables. This is the core reason that Hekaton is able to get very close to being 100 times faster than the normal database engine.
Dr. Dewitt explained how latches work for those who aren’t used to looking at latches. Latches are used as a special kind of lock which are used to ensure that a process that is attempting to query for a page which is in the process of being read from the disk has to wait until the page read from the disk has been completed by the storage engine of the database engine.
One question that Dr. Dewitt gets asked a lot, and that I’ve been asked several times as well, is “is Hekaton the new pinned tables”? The answer here is no. Hekaton is a totally different way of processing data. By simply putting tables into memory and pinning the table there you wouldn’t even be able to get a 10X performance improvement which wasn’t enough to justify the time and money which Microsoft wanted to spend while making this product. Hekaton is actually the third query engine in the SQL Server database product. There’s the normal relational engine which we’ve been using for years, the ColumnStore engine (project Apollo) and not the Hekaton engine for these ultrafast database engine.
When building a Hekaton engine you MUST have a PRIMARY KEY defined on the table which is nonclustered and must be HASH or RANGE defined. For v1 there are no blobs and no XML data types available for Hekaton tables, which will hopefully be fixed in the release after SQL Server 2014.
Some of the big changes in the way that Hekaton handles data changes without locking is to change the way that updates are done. The big change here is that when an UPDATE is run the row isn’t actually change. A new row is added to the table and the old row is “expired”. This expiration is done by marking a timestamp of sorts (it’s closer to a transaction ID than a timestamp) as the last transaction which the old version of the row is valid for and when the new version of the row is written it is marked as having a beginning timestamp of the transaction time when the transaction was started. When select statements are accessing rows they use this timestamp to figure out which version of the row they should be looking at. This is a VERY different way of managing changes in the database tables from what we have had until now.
When are older row versions deleted? There’s a garbage collector which runs in the background watching for when there are rows which are expired with an earlier timestamp than any of the current transactions. At that point the old version of the rows can be deleted. This means that there’s no blocking that can possibly happen (if there was blocking in Hekaton tables) while this is happening as there are no transactions which could possibly read these older rows. Based on this is someone was to begin a new transaction and never commit the transaction none of the older rows would be able to be deleted.
I hope that you enjoyed the keynote from Dr. Dewitt as much as I did, and hopefully I’ll see you at the summit next year.
Denny
