In-Memory OLTP for Azure SQL Database Hits General Availability

Built-in In-Memory technologies now available for Azure SQL Database Premium database tier.

Share online:

Azure SQL Database built-in In-Memory technologies are now generally available for the Premium database tier including Premium pools.

In-memory technology helps optimize the performance of transactional (OLTP), analytics (OLAP), as well as mixed workloads (HTAP).

"These technologies allow you to achieve phenomenal performance with Azure SQL Database – 75,000 transactions per second for order processing (11X perf gain) and reduced query execution time from 15 seconds to 0.26 (57X perf)," writes Azure team.

Additionally, these also help reduce cost – "on a P2 database obtain 9X perf gain for transactions or 10X perf gain for analytics queries by implementing In-Memory technologies," without any additional cost.

Benefits of these performance and cost savings results include:

  • In-Memory OLTP increases throughput and reduces latency for transaction processing.
  • Clustered Columnstore Indexes reduce storage footprint (up to 10X) and improve performance for reporting and analytics queries.
  • Non-clustered Columnstore Indexes for Hybrid Transactional and Analytical Processing (HTAP) gain real-time insights into business by querying the operational database directly, without the need to run an expensive ETL process and wait for the data warehouse to be populated. Non-clustered Columnstore indexes allow very fast execution of analytics queries on the OLTP database, while reducing the impact on the operational workload.
  • In-Memory OLTP and Columnstore index can also be combined, allowing you to both perform very fast transaction processing and run analytics queries very fast on the same data.

The team also shared performance results for In-Memory OLTP in Azure SQL Database as well as for Columnstore indexes (from Azure team):

  • Order processing benchmark (scale factor 100, with 400 clients) on P15: 75,000 transactions per second (TPS) with In-Memory OLTP, compared with 6,800 TPS with traditional tables and stored procedures, which translates to 11X performance gain with In-Memory OLTP.
  • Also lower performance tiers show impressive results. For a P2 with same workload (scale factor 5, with 200 clients): 8,900 TPS with In-Memory OLTP, compared with 1,000.
  • 30-40% performance gain just by replacing traditional table-valued parameters (TVPs) with memory-optimized TVPs: High Speed IoT Data Ingestion Using In-Memory OLTP in Azure

Columnstore indexes performance results:

  • Using a P15 database, the query runs in 0.26 seconds with Columnstore, while it runs in 15 seconds when using traditional indexes with page compression. This translates to a performance gain of 58X!
  • Even with lower pricing tiers: using a P1 database, query runs in 4.8 seconds with Columnstore, while it runs in 27 seconds using traditional indexes with page compression. A performance gain of 5.6X without increasing the pricing tier, explained Azure team.

In other Azure announcements today, Content Moderator in Microsoft Cognitive Services, is now in public preview in the Azure portal, starting Nov 3rd.

Microsoft Content Moderator, powered by intelligent machine learning, automatically filters out offensive content in text, image, and video across platforms, and includes human review tools for more nuanced cases such as:

"Image moderation lets you easily evaluate images for offensive and unwanted content as well as it also includes optical character recognition (OCR) and face detection for detecting unwanted or exploitive content."

Text moderation help to detect profanity in more than 100 languages and match text against custom lists. Additionally, it checks for malware and phishing URLS, and personally identifiable information (PII) as well.

Content Moderator can also be used for Video moderation to proactively detect offensive and unwanted content in videos through Azure Media Services.

Those who are not aware of Microsoft Cognitive Services, "are a collection of APIs which enable developers to tap into high-quality vision, speech, language, knowledge and search technologies, developed with decades of Microsoft research to build intelligent apps."

Microsoft Cognitive Services' Face API is updated now to allow developers to store up to 100,000 Persons by purchasing additional Person Faces." The quota for stored Person Groups is now 1,000, with up to 1,000 Persons per Person Group," Microsoft said.

Hybrid Connections feature of Azure Service Bus is now in public preview, and allows securely accessing existing assets, wherever they are, without a complex firewall, network or VPN configuration.

These services provide enhanced authentication and connectivity through the cloud with the added benefits of visibility and monitoring.

With the new hybrid connections agent, "you can keep your data where it is, while enabling access from the cloud or on-premises."

Finally, Site Recovery service lets customers leverage the power of the Cloud while still being very thoughtful about their first foray into the Cloud.

Unfortunately, this isn't a service within Azure Government, and many public sector customers have their own Data Centers or are using Co-Lo facilities for management of their infrastructure.

Site Recovery provide replication, migration, and disaster recovery for Servers or VMs that exist in one data center, but may need to be made available replicated or Failed-Over to another Data Center.

"The Site Recovery service provides Disaster Recovery as a Service (DRaaS) by allowing you to setup a Recovery Plan, such that each VM or Server can be monitored and imaged in such a way that it will be quick and easy to spin up a complete replica of the entire Data Center in either another facility or in the Cloud while keeping all data and configurations of the systems," Azure team explained.

See the list of features available within the service under:

  • Can protect Hyper-V, VMware and physical servers
  • Coordinates and manages the ongoing replication of data by integrating with existing technologies including System Center and SQL Server AlwaysOn
  • Recovery Plans can be as simple or as advanced as necessary based on application requirements, including the execution of custom Windows PowerShell scripts and Azure Automation Runbooks, and pauses for manual interventions
  • Applications can be Migrated to Azure with just a few clicks, or burst to Azure temporarily when you encounter a surge in demand
  • Site Recovery monitors the state of your protected instances continuously and remotely from Azure with all monitoring traffic being encrypted, writes Azure team.