Memory-Optimized Tables – Helps in Performance and Scalability

Today, while performing the code review on one of my project, which is getting developed using many Azure services/technologies.

Being ‘Internet of Things’/IOT scenario, the requirements demands the use of “Polyglot Persistence” pattern. Because solution need to store the structured/SQL as well unstructured/NoSQL data. And as we know, to store the structured/relational data ‘SQL Azure’ is the default technology choice being on Azure and Microsoft person 🙂

So, while analyzing the stored procedure’s T-SQL code, observed that many of the SPs are utilizing the temporary tables for data computation/processing operations to improve the overall performance. Using temporary tables, table variables, or table-valued parameters was a reasonable/acceptable practice when I was a programmer 🙂 But started wondering if anything new added to this approach/pattern to improve for better. By using Bing, I quickly discovered that we really have something new and better of course namely, “Memory-Optimized Tables“. This is part of In-Memory OLTP, which is the premier technology available in SQL Server and Azure SQL Database for optimizing performance of transaction processing, data ingestion, data load, and transient data scenarios.

As MS docs says, Memory-optimized tables are tables, created using CREATE TABLE with “MEMORY_OPTIMIZED = ON” option. Memory-optimized tables are fully durable by default, and, like transactions on (traditional) disk-based tables, fully durable transactions on memory-optimized tables are fully atomic, consistent, isolated, and durable (ACID). Memory-optimized tables and natively compiled stored procedures support a subset of Transact-SQL. More details.

Hence, if you are exploring the options to enhance your SPs/T-SQL code on SQL Azure then please refer here for performance and scalability considerations.

The details scenario are documented with instructions @ Replace global tempdb ##table and Replace session tempdb #table

So, next time whenever you see CREATE TABLE #temptable and/ CREATE TABLE #temptable and choose to replace by memory optimization option then make sure you visit this blog to say thanks you 🙂

One response

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: