Feeds

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

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

The next step in data security

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. ®

Security for virtualized datacentres

More from The Register

next story
New 'Cosmos' browser surfs the net by TXT alone
No data plan? No WiFi? No worries ... except sluggish download speed
'Windows 9' LEAK: Microsoft's playing catchup with Linux
Multiple desktops and live tiles in restored Start button star in new vids
iOS 8 release: WebGL now runs everywhere. Hurrah for 3D graphics!
HTML 5's pretty neat ... when your browser supports it
Mathematica hits the Web
Wolfram embraces the cloud, promies private cloud cut of its number-cruncher
Google extends app refund window to two hours
You now have 120 minutes to finish that game instead of 15
Mozilla shutters Labs, tells nobody it's been dead for five months
Staffer's blog reveals all as projects languish on GitHub
SUSE Linux owner Attachmate gobbled by Micro Focus for $2.3bn
Merger will lead to mainframe and COBOL powerhouse
iOS 8 Healthkit gets a bug SO Apple KILLS it. That's real healthcare!
Not fit for purpose on day of launch, says Cupertino
Profitless Twitter: We're looking to raise $1.5... yes, billion
We'll spend the dosh on transactions, biz stuff 'n' sh*t
prev story

Whitepapers

Secure remote control for conventional and virtual desktops
Balancing user privacy and privileged access, in accordance with compliance frameworks and legislation. Evaluating any potential remote control choice.
WIN a very cool portable ZX Spectrum
Win a one-off portable Spectrum built by legendary hardware hacker Ben Heck
Intelligent flash storage arrays
Tegile Intelligent Storage Arrays with IntelliFlash helps IT boost storage utilization and effciency while delivering unmatched storage savings and performance.
High Performance for All
While HPC is not new, it has traditionally been seen as a specialist area – is it now geared up to meet more mainstream requirements?
Beginner's guide to SSL certificates
De-mystify the technology involved and give you the information you need to make the best decision when considering your online security options.