SQL Server 2014 hastens transaction processing

Microsoft's next relational database management system will come with built-in in-memory technologies

The next version of Microsoft's relational database management system (RDMS) promises to bring immense performance gains to online transactional processing systems.

SQL Server 2014 will come with a new in-memory OLTP engine, one built with the company's Heckaton set of technologies, first developed by Microsoft Research.

"We're building our transactional processing work in directly into the database. It is not a separate product, not a separate thing to program to," said Quentin Clark, corporate vice president of SQL Server, in a keynote presentation launching SQL Server 2014, announced this week at the Microsoft TechEd North America conference in New Orleans. "We are doing it because it is a way to achieve unprecedented low latency and high-scale throughput for transactional data."

Microsoft has billed Heckaton as an in-memory technology, though unlike other in-memory data stores, such as Oracle's TimesTen, it does not place the entire database into working memory, or RAM, to speed performance. Rather, it selectively moves only the most consulted database tables to memory.

In Microsoft labs, Hekaton has been shown to cut transaction times by 50 times, and customers who have tried early versions of the technology have found a 16-fold percent improvement in performance, according to Eron Kelly, Microsoft SQL Server general manager, in a follow-up interview with the IDG News Service.

In addition to speeding transactional system performance, SQL Server 2014 could also cut hardware costs, because the efficiencies wrought from the new technologies would reduce the computational requirements, or number of servers, to execute a given workload, Kelly said.

Traditionally, each time an OLTP system conducts a transaction, such as books an order for a travel agency, the results are written to, or read from, a row of data residing on a database table on a hard drive. OLTP systems could perform much faster if the database tables were stored in a server's working memory. With most commercial OLTP systems, however, the cost of putting all the database tables in working memory would be prohibitively expensive. Hekaton speeds transaction times through, among other techniques, a more judicious use of memory.

What Hekaton does, Kelly explained, is move the hot data, or data that gets lots of reads and writes, into a server's working memory.

To enable in-memory usage, the database administrator uses a diagnostic application that surveys data table usage and recommends which database tables should be moved into a server's main memory.

The system maintains transactional integrity -- the guarantee that each completed transaction is saved even during a system outage -- by writing transactions separately to a log file.

Hekaton also looks for stored procedures, or queries that are stored for regular use, that would be ripe for optimization as well. The software can identify heavily used stored procedures and can compile them to machine code. Machines code typically executes much more quickly -- and requires less memory -- than code written in high-level languages such as SQL itself.

A third way Hekaton improves performance is by refining the process of locking database tables to write data. Traditionally, in order to avoid overwriting of data midtransaction by another operation, a RDMS will lock a table page each time a row of data needs to be updated. Multiple concurrent reads or writes to a database table can, however, create a backlog, slowing performance.

The OLTP Engine doesn't lock data by pages. "So each process can hit any row it wants, and the only time there is a conflict is when two processes hit the same row at the exact same instant," Kelly said.

Even individual rows aren't locked when they are being written to. Rather, the RDMS writes the updated row to a new location and leaves a pointer to the new location in the old row, where it can be followed by any query that takes place at the same time as the update. Kelly called this technique "optimistic concurrency."

This is not Microsoft's first foray into in-memory technologies. The Microsoft Parallel Data Warehouse uses a similar technology for large-scale data analysis and PowerPivot, introduced in Excel 2010, uses an in-memory compressed columnar processing to parse large data sets in memory. SQL Server 2012 then borrowed this columnar processing technique for its own use.

As an example of how the new SQL Server could speed performance, Kelly explained how a retail chain could benefit from this technology.

A retail store keeps an inventory of all the products on the shelves, which a sales clerk can consult on a computer to see if an item is in stock. This sort of query can take time and sometimes is not accurate, given that the inventory database can only be updated once a day through a batch processing system.

"There's not the performance to update it in real time," Kelly said. "With our OLTP engine, you can update things in real time."

Kelly pointed to one early user of the technology, online gaming company Bwin, which by updating to SQL Server 2014 managed to increase the number of transactions on its SQL Server-based Web application, from 15,000 transactions per second to 250,000 transactions per second. "For them, each transaction is money," Kelly said.

More details will be given about the new features in SQL Server 2014 in the months ahead. A preview of SQL Server 2014 will be available by the end of the month and the full production version will be available for purchase in early 2014.

Microsoft did talk about how it has made it easy for users of this RDMS to use Microsoft's new Azure-based backup and replication services. The SQL Server Management Studio will include the option to periodically back up a database to the Azure blob storage. An administrator can also configure a secondary live copy of SQL Server on Azure.

"So every time a transaction is committed on Azure, it is committed on premise," Kelly said.

In this case, the data is copied asynchronously, so performance of the primary database will not be slowed waiting to complete the backup. A system could also load-balance traffic between the Azure database and the on-premises database, Kelly said.

Joab Jackson covers enterprise software and general technology breaking news for The IDG News Service. Follow Joab on Twitter at @Joab_Jackson. Joab's e-mail address is Joab_Jackson@idg.com

Join the Good Gear Guide newsletter!

Error: Please check your email address.

Tags applicationsdatabasesMicrosoftsoftware

Our Back to Business guide highlights the best products for you to boost your productivity at home, on the road, at the office, or in the classroom.

Keep up with the latest tech news, reviews and previews by subscribing to the Good Gear Guide newsletter.

Joab Jackson

IDG News Service
Show Comments

Most Popular Reviews

Latest News Articles


PCW Evaluation Team

Azadeh Williams

HP OfficeJet Pro 8730

A smarter way to print for busy small business owners, combining speedy printing with scanning and copying, making it easier to produce high quality documents and images at a touch of a button.

Andrew Grant

HP OfficeJet Pro 8730

I've had a multifunction printer in the office going on 10 years now. It was a neat bit of kit back in the day -- print, copy, scan, fax -- when printing over WiFi felt a bit like magic. It’s seen better days though and an upgrade’s well overdue. This HP OfficeJet Pro 8730 looks like it ticks all the same boxes: print, copy, scan, and fax. (Really? Does anyone fax anything any more? I guess it's good to know the facility’s there, just in case.) Printing over WiFi is more-or- less standard these days.

Ed Dawson

HP OfficeJet Pro 8730

As a freelance writer who is always on the go, I like my technology to be both efficient and effective so I can do my job well. The HP OfficeJet Pro 8730 Inkjet Printer ticks all the boxes in terms of form factor, performance and user interface.

Michael Hargreaves

Windows 10 for Business / Dell XPS 13

I’d happily recommend this touchscreen laptop and Windows 10 as a great way to get serious work done at a desk or on the road.

Aysha Strobbe

Windows 10 / HP Spectre x360

Ultimately, I think the Windows 10 environment is excellent for me as it caters for so many different uses. The inclusion of the Xbox app is also great for when you need some downtime too!

Mark Escubio

Windows 10 / Lenovo Yoga 910

For me, the Xbox Play Anywhere is a great new feature as it allows you to play your current Xbox games with higher resolutions and better graphics without forking out extra cash for another copy. Although available titles are still scarce, but I’m sure it will grow in time.

Featured Content

Latest Jobs

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?