Microsoft SQL Server 14 man: 'Nothing stops a Hekaton transaction'

In-memory tables and native code stored procedures on the way


TechEd Microsoft’s SQL Server 14 will include a new memory optimisation feature code-named Hekaton, with the potential for huge performance gains – provided your data fits in RAM.

Hekaton is not a separate product, but will be part of enterprise editions of SQL Server 14, for which no availability date has been announced. The latest update of Microsoft's database manager was presented as part of the 2012 R2 wave of server products, which includes Windows Server 2012 R2 and System Center 2012 R2.

The way Hekaton works is that you declare a table as memory-optimised, following which SQL Server will load the entire table into memory. In addition, stored procedures can be compiled to native DLLs for fast access.

Resultant speed increases can be stunning. At a press preview last month, SQL Server program manager Kevin Farlee gave a demo using a 2-socket, 16-core server with 256GB RAM and 2 SSDs alongside hard drives. Carrying out intensive transactions simulating 80 users, the demo yielded 2,400 transactions per second. Enabling memory-optimised tables increased this to 17,000. Compiling stored procedures to native code upped performance further: to 65,000 transactions per second.

SQL Server Hekaton’s remarkable performance boost

Since both SQL Server and Windows cache data in memory, why does Hekaton provide such a performance boost?

“The reason that Hekaton is that we use memory structures that are much more efficient,” said Farlee.

Hekaton: How it works

Most of today’s database managers are built on the assumption that data lives on a disk, with little bits of data at a time loaded into memory for manipulation. “There’s a whole lot of infrastructure involved in paging those subsets of data in and out," Farlee explained. "There’s software to manage what’s in memory, and where on disk this piece of memory relates to, and in Hekaton we don’t need any of it. We don’t use pages. When you look up an index it’s just a memory pointer to your data, as opposed to traversing a B-tree."

Transactions in Hekaton are designed so that they always complete, says the SQL Server man. “When you amend a row in Hekaton, you create a new version in memory. When no transactions need to reference the old version, we reclaim the memory. Nothing can stop a Hekaton transaction,” Farlee said.

Backing up SQL Server data to Azure using PowerShell (click to enlarge)

If the transaction fails to commit, the changes are never written to disk. When a transaction does commit, it gets logged in the transaction log and a background process copies it back to the set of sequential files that persist Hekaton data. These files are only read at database start-up, when they get copied into memory.

An analysis tool called AMAR (Analyse Migrate and Report) will assist database administrators in working out which tables will benefit from memory optimisation.

The obvious limitation of Hekaton is that the data has to fit in RAM. “If the data becomes too large, we stop inserting rows,” Farlee said. There is no failover back to disk-based tables.

There are also some code compatibility issues with compiling stored procedures, which means existing code is likely to need modification, though Microsoft is aiming to reduce or eliminate this eventually. Microsoft also showed improved high availability features in SQL Server 14. There is support for high availability groups using Server 2012 Cluster Shared Volumes, and the maximum number of secondary databases – for failover and read-only performance boost – is doubled: from four to eight. The team have also made tweaks to how online indexes are rebuilt which will reduce the need to take a database offline for maintenance.

Azure integration means that you will be able to backup SQL Server databases to Windows Azure blob storage using PowerShell or smart agents. You can also maintain a standby replica in Azure of your on-premise database. ®

Similar topics


Other stories you might like

  • Prisons transcribe private phone calls with inmates using speech-to-text AI

    Plus: A drug designed by machine learning algorithms to treat liver disease reaches human clinical trials and more

    In brief Prisons around the US are installing AI speech-to-text models to automatically transcribe conversations with inmates during their phone calls.

    A series of contracts and emails from eight different states revealed how Verus, an AI application developed by LEO Technologies and based on a speech-to-text system offered by Amazon, was used to eavesdrop on prisoners’ phone calls.

    In a sales pitch, LEO’s CEO James Sexton told officials working for a jail in Cook County, Illinois, that one of its customers in Calhoun County, Alabama, uses the software to protect prisons from getting sued, according to an investigation by the Thomson Reuters Foundation.

    Continue reading
  • Battlefield 2042: Please don't be the death knell of the franchise, please don't be the death knell of the franchise

    Another terrible launch, but DICE is already working on improvements

    The RPG Greetings, traveller, and welcome back to The Register Plays Games, our monthly gaming column. Since the last edition on New World, we hit level cap and the "endgame". Around this time, item duping exploits became rife and every attempt Amazon Games made to fix it just broke something else. The post-level 60 "watermark" system for gear drops is also infuriating and tedious, but not something we were able to address in the column. So bear these things in mind if you were ever tempted. On that note, it's time to look at another newly released shit show – Battlefield 2042.

    I wanted to love Battlefield 2042, I really did. After the bum note of the first-person shooter (FPS) franchise's return to Second World War theatres with Battlefield V (2018), I stupidly assumed the next entry from EA-owned Swedish developer DICE would be a return to form. I was wrong.

    The multiplayer military FPS market is dominated by two forces: Activision's Call of Duty (COD) series and EA's Battlefield. Fans of each franchise are loyal to the point of zealotry with little crossover between player bases. Here's where I stand: COD jumped the shark with Modern Warfare 2 in 2009. It's flip-flopped from WW2 to present-day combat and back again, tried sci-fi, and even the Battle Royale trend with the free-to-play Call of Duty: Warzone (2020), which has been thoroughly ruined by hackers and developer inaction.

    Continue reading
  • American diplomats' iPhones reportedly compromised by NSO Group intrusion software

    Reuters claims nine State Department employees outside the US had their devices hacked

    The Apple iPhones of at least nine US State Department officials were compromised by an unidentified entity using NSO Group's Pegasus spyware, according to a report published Friday by Reuters.

    NSO Group in an email to The Register said it has blocked an unnamed customers' access to its system upon receiving an inquiry about the incident but has yet to confirm whether its software was involved.

    "Once the inquiry was received, and before any investigation under our compliance policy, we have decided to immediately terminate relevant customers’ access to the system, due to the severity of the allegations," an NSO spokesperson told The Register in an email. "To this point, we haven’t received any information nor the phone numbers, nor any indication that NSO’s tools were used in this case."

    Continue reading

Biting the hand that feeds IT © 1998–2021