SQL Server 2022 hastens transaction processing - powellagar1989

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

SQL Waiter 2022 wish come with a new in-memory OLTP locomotive, one shapely with the company's Heckaton set of technologies, first-year developed aside Microsoft Research.

"We're construction our transactional processing work in direct into the database. It is not a separate intersection, not a branch out affair to course of study to," aforementioned Quentin Kenneth Clark, corporate vice president of SQL Server, in a tonic presentation unveiling SQL Server 2022, proclaimed this week at the Microsoft TechEd North United States of America league 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 atomic number 3 an in-memory technology, though unlike other in-memory data stores, such as Oracle's TimesTen, information technology does not set up the uncastrated 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 dealing times by 50 times, and customers who sustain tried early versions of the technology have found a 16-bend percentage improvement in performance, according to Eron Kelly, Microsoft SQL Server ecumenical manager, in a followup audience with the IDG News Armed service.

In addition to speeding transactional system performance, SQL Waiter 2022 could too cut hardware costs, because the efficiencies wrought from the radical technologies would tighten the computational requirements, operating theater bi of servers, to carry out a given workload, Kelly said.

Traditionally, each clock time an OLTP arrangement conducts a transaction, such as books an enjoin for a go by agency, the results are written to, or show from, a row of information residing on a database table on a problematical tug. OLTP systems could execute much faster if the database tables were stored in a server's running retention. With most commercial OLTP systems, however, the cost of putt all the database tables in working memory would be prohibitively expensive. Hekatonspeeds dealings times through, among other techniques, a more judicious use of storage.

What Hekaton does, Kelly explained, is be active the hot data, or data that gets mountain of reads and writes, into a host's working memory.

To enable in-retention usage, the database administrator uses a diagnostic application that surveys data hold over usage and recommends which database tables should exist affected into a server's main memory.

The system maintains transactional wholeness—the guarantee that each completed transaction is saved even during a system outage—by writing transactions severally to a log file.

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

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

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

Tied personal rows aren't locked when they are being written to. Rather, the RDMS writes the updated wrangle to a new location and leaves a pointer to the red-hot location in the archaic quarrel, where it can comprise followed aside any query that takes place at the same time every bit the update. Kelly called this technique "optimistic concurrency."

This is non Microsoft's commencement foray into in-memory technologies. The Microsoft Parallel Information Warehouse uses a similar engineering science for big data analysis and PowerPivot, introduced in Excel 2010, uses an in-memory compressed columnar processing to parse large data sets in computer memory. SQL Server 2012 and then borrowed this columned processing technique for its possess use.

As an example of how the new SQL Server could speed operation, Grace Patricia Kelly explained how a retail string could benefit from this engineering science.

A retail store keeps an armoury of every the products on the shelves, which a gross sales shop clerk can consult on a computing device to see if an detail is in stock. This sorting of query can take time and sometimes is not accurate, given that the inventory database hindquarters only be updated once a day through a raft processing system.

"There's not the execution to update it in real time," Gene Kelly said. "With our OLTP railway locomotive, you can update things at once."

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

More inside information testament be given about the new features in SQL Waiter 2022 in the months ahead. A prevue of SQL Server 2022 will be available aside the end of the month and the overflowing yield adaptation will live available for purchase in early 2022.

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

"And so every clock a transaction is committed on Azure, it is committed on premise," Kelly said.

In that case, the information is derived asynchronously, so functioning of the primary database will not Be slowed waiting to complete the backup. A scheme could also payload-balance traffic 'tween the Azure database and the on-premises database, Eugene Curran Kelly said.

Source: https://www.pcworld.com/article/452261/sql-server-2014-hastens-transaction-processing.html

Posted by: powellagar1989.blogspot.com

Share this post