27 Aug SQL Server 2014 In-Memory OLTP Hekaton Useful Links
SQL Server 2014 is nearly here. When it ships it will come with an “In-Memory OLTP” feature. This feature is generally known by its codename: Hekaton. Why that code name? Well, hekaton is Greek for a hundred, and that was the targeted performance improvement that Microsoft set out to achieve when building this new technology.
I’m a huge fan of this feature. I’m already thinking about putting together some kind of script that would target a database and check to see which objects would be best suited to be converted to in-memory objects. Of course Microsoft already has something for you to use. However, that tool will require your use of the Management Data Warehouse feature at some point. When the time comes for both of those folks currently using MDW to migrate to Hekaton they are going to be very happy.
What objects make for good candidates for Hekaton objects? Great question. I’d suggest that the objects you want to target for Hekaton are objects that are “hot”. That is, they are used frequently, and they are able to easily fit inside of memory.
Why those tables? Well, I’d wager that if you have an OLTP system and a table you declare to be “hot”, you likely already have a handful of indexes on this tables for a variety of reasons. Those indexes are causing additional overhead that you would typically see as latching and locking. Hekaton removes the need for latching and locking, resulting in a dramatic increase in performance and throughput.
I expect that very soon the day will come for you to consider using Hekaton. To help prepare you for that day I wanted to provide you with some of the more useful links I have been collecting these past few months.
First up, here is a link to the MSDN entry that gets you started on the topic of In-Memory OLTP: http://technet.microsoft.com/en-us/library/dn133186(v=sql.120).aspx
Are you instead looking for code samples? They have that, too: http://technet.microsoft.com/en-us/library/dn296373(v=sql.120).aspx
Hekaton Blog Posts
Here is a list of Microsoft blog posts that will help you prepare for Hekaton:
- In-Memory OLTP Sample for SQL Server 2014 RTM: http://blogs.technet.com/b/dataplatforminsider/archive/2014/05/06/in-memory-oltp-sample-for-sql-server-2014-rtm.aspx
- Getting Started with SQL Server 2014 In-Memory OLTP: http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/26/getting-started-with-sql-server-2014-in-memory-oltp.aspx
- Sample AdventureWorks sample database specific for Hekaton functionality: http://blogs.technet.com/b/dataplatforminsider/archive/2013/11/11/new-in-memory-oltp-sample-for-sql-server-2014-ctp2.aspx
- SQL Server 2014 In-Memory OLTP – bwin Migration and Production Experience: http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/30/sql-server-2014-in-memory-oltp-bwin-migration-and-production-experience.aspx
- SQL Server 2014 In-Memory OLTP: App Migration Scenario Leveraging the Integrated Approach: http://blogs.technet.com/b/dataplatforminsider/archive/2013/08/13/sql-server-2014-in-memory-oltp-app-migration-scenario-leveraging-the-integrated-approach.aspx
- SQL Server 2014 In-Memory OLTP: Nonclustered Indexes for Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2013/11/12/sql-server-2014-in-memory-oltp-nonclustered-indexes-for-memory-optimized-tables.aspx
- SQL Server 2014 In-Memory OLTP: High Availability for Databases with Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2013/11/05/in-memory-oltp-high-availability-for-databases-with-memory-optimized-tables.aspx
- SQL Server 2014 In-Memory OLTP: Memory Management for Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2013/11/14/sql-server-2014-in-memory-oltp-memory-management-for-memory-optimized-tables.aspx
- SQL Server 2014 In Memory OLTP: Memory-Optimized Table Types and Table Variables: http://blogs.technet.com/b/dataplatforminsider/archive/2014/01/07/sql-server-2014-in-memory-oltp-memory-optimized-table-types-and-table-variables.aspx
- In-Memory OLTP: How Durability is Achieved for Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/11/in-memory-oltp-how-durability-is-achieved-for-memory-optimized-tables.aspx
- In-Memory OLTP Programmability: Concurrency and Transaction Isolation for Memory-optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/01/in-memory-oltp-programmability-concurrency-and-transaction-isolation-for-memory-optimized-tables.aspx
- In-Memory OLTP Index Troubleshooting, Part II: http://blogs.technet.com/b/dataplatforminsider/archive/2014/01/30/in-memory-oltp-index-troubleshooting-part-ii.aspx
- Hardware Considerations for In-Memory OLTP in SQL Server 2014: http://blogs.technet.com/b/dataplatforminsider/archive/2013/08/01/hardware-considerations-for-in-memory-oltp-in-sql-server-2014.aspx
- How Memory-Optimized Database Technology is Integrated into SQL Server 2014: http://blogs.technet.com/b/dataplatforminsider/archive/2013/08/08/how-memory-optimized-database-technology-is-integrated-into-sql-server-2014.aspx
- Boosting Transaction Performance in Windows Azure Virtual Machines with In-Memory OLTP: http://blogs.technet.com/b/dataplatforminsider/archive/2013/11/25/boosting-transaction-performance-in-windows-azure-virtual-machines-with-in-memory-oltp.aspx
- Storage Allocation and Management for Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2014/01/16/storage-allocation-and-management-for-memory-optimized-tables.aspx
- Database Backup with Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2014/02/05/database-backup-with-memory-optimized-tables.aspx
- Differential Database Backup with Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2014/02/07/differential-database-backup-with-memory-optimized-tables.aspx
- SQL Server 2014: Inside Hekaton Natively Compiled Stored Procedures: http://blogs.msdn.com/b/igorpag/archive/2014/01/15/sql-server-2014-inside-hekaton-natively-compiled-stored-procedures.aspx
- Using Natively Compiled Stored Procedures in SQL Server 2014: http://blogs.technet.com/b/dataplatforminsider/archive/2014/02/21/using-natively-compiled-stored-procedures-in-sql-server-2014.aspx
- Merge Operation in Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2014/01/22/merge-operation-in-memory-optimized-tables.aspx
- State Transition of Checkpoint Files in Databases with Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2014/01/23/state-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables.aspx
Posts from Tony Rogerson:
- Hekaton In-Memory Tables: Hash Indexes: http://dataidol.com/tonyrogerson/2014/01/06/hekaton-in-memory-tables-hash-indexes/
- Hekaton In-Memory tables: Understanding the Row Chains of Hash Indexes: http://dataidol.com/tonyrogerson/2014/01/16/sql-server-hekaton-in-memory-tables-understanding-the-row-chains-of-hash-indexes/
- SQL Server Hekaton (XTP) In-Memory Tables: Range Indexes and Row Chains: http://dataidol.com/tonyrogerson/2014/01/21/sql-server-hekaton-xtp-in-memory-tables-range-indexes-and-row-chains/
- SQL Server Hekaton (XTP) In-Memory tables: Choosing the correct BUCKET_COUNT for a Hash Index: http://dataidol.com/tonyrogerson/2014/01/25/sql-server-hekaton-xtp-in-memory-tables-choosing-the-correct-bucket_count-for-a-hash-index/
- Changing In-Memory Table definition without down time: http://dataidol.com/tonyrogerson/2014/06/12/changing-in-memory-table-definition-without-down-time/
- How Hekaton (XTP) achieves Durability for “Memory Optimised” Tables: http://dataidol.com/tonyrogerson/2014/06/16/how-hekaton-xtp-achieves-durability-for-memory-optimised-tables/
Posts from Bob Beauchemin:
- Thinking about Hekaton? Then think about collations: http://www.sqlskills.com/blogs/bobb/thinking-about-hekaton-then-think-about-collations/
- Tracking execution stats of SQL Server 2014 natively-compiled sprocs: http://www.sqlskills.com/blogs/bobb/tracking-execution-stats-of-sql-server-2014-natively-compiled-sprocs/
- Hekaton data and code – where does that stuff actually live?: http://www.sqlskills.com/blogs/bobb/hekaton-data-and-code-where-does-that-stuff-actually-live/
- SQL Server 2014 memory-optimized table variables – metadata and garbage collection: http://www.sqlskills.com/blogs/bobb/sql-server-2014-memory-optimized-table-variables-metadata-and-garbage-collection/
- In-memory OLTP – read_set, write_set, and scan_set: http://www.sqlskills.com/blogs/bobb/in-memory-oltp-read_set-write_set-and-scan_set/
- SQL Server 2014 In-Memory OLTP: What exactly is a “dusty corner”?: http://www.sqlskills.com/blogs/bobb/sql-server-2014-memory-oltp-exactly-dusty-corner/
Here is a video from Dr. David DeWitt at the PASS 2013 Summit in Charlotte:
- Hekaton: Why, What, and How (fast forward to 27:00 mark so you don’t have to listen to me introduce David)
Here are some videos on Channel 9 from TechEd 2013, the first three are from Sunil Agarwal, a Principal Program Manager for the SQL Server product team at Microsoft:
- Microsoft SQL Server In-Memory OLTP: Overview of Project “Hekaton”
- Microsoft SQL Server In-Memory OLTP Project “Hekaton”: App Dev Deep Dive
- Microsoft SQL Server In-Memory OLTP Project “Hekaton”: Management Deep Dive
These next three are from Jos de Bruijn, a Program Manager for the SQL Server product team and key member of the Hekaton team:
- Microsoft SQL Server 2014 In-Memory OLTP: Overview
- SQL Server In-Memory OLTP: DB Developer Deep Dive
- SQL Server In-Memory OLTP: DBA Deep Dive
Lastly, I’ll leave you with a link to the some whitepapers:
- First up is the one published by Microsoft earlier this year: http://research.microsoft.com/pubs/193594/Hekaton%20-%20Sigmod2013%20final.pdf
- Here is one from Kalen Delany: http://download.microsoft.com/download/F/5/0/F5096A71-3C31-4E9F-864E-A6D097A64805/SQL_Server_Hekaton_CTP1_White_Paper.pdf
While in Madrid for TechEd this year I had the honor to staff one of the Microsoft booths with Jos. While showing one of the standard Hekaton demos an attendee challenged him by saying “show me the source code for the demo app”. Jos complied, breaking down the application code, explaining how the tables were loaded into memory, how the stored procedures were written as in-memory objects as well. We would get improvement upwards of 70x with the sample data and then Jos started to speculate what scenarios would be needed for data and code to achieve greater than 100x.
That conversation alone was worth the price of admission for any SQL geek at TechEd this year. I count myself lucky to have been able to just listen to Jos explain about the nuances of Hekaton.
Most of the information listed above is specific to SQL Server 2014 CTP1. As newer versions of SQL Server are released I intend to update this post over time with additional links that I find to be useful regarding Hekaton. So you may find it useful to bookmark this page for future reference.