In-Memory OLTP for Azure SQL DB
This episode of Data Exposed welcomes back Jos de Bruijn, Senior PM in the SQL Server Database Systems team, to talk about In-Memory OLTP functionality in Azure SQL Database, currently in Public Preview. Focusing on a single slide to begin, Jos discusses the key In-memory OLTP technology differences between SQL Server 2014/2016 and Azure SQL DB but still focusing on the same great performance feature and how it is implemented in Azure SQL Database.
At the 10:45 mark, Jos jumps into a demo that you might recognize (still a very cool demo though), but it really highlights the performance increase that the in-memory OLTP technology brings to Azure SQL DB. Jos highlights the fact that since it is the same feature and technology shared between SQL Server and Azure SQL DB, including the reporting capability to identify bottlenecks in your database via the Transaction Performance Analysis Overview report. Jos also shows the new Memory Optimization Advisor which helps migrate tables and stored procedures to in-memory.
The next demo Jos shows is at the 20:05 mark, which takes the first demo a bit further and shows the clustered columnstore showing the columnstore compression by offloading the data from the memory optimized table into the columstore table. Jos explains the reason you might want to do this is because you might be reaching the limits in your memory optimized table and need to ingest more data into the memory optimized table.
Lastly, at the 22:50 mark Jos concludes with one final demo showing a table that incorporates both technologies, in-memory OLTP and Clustered Columnstore and the performance gains you can get by utilizing both. He demos this on a table with over 30 million rows, and executes an analytic style query that returns rows in 0 seconds. Amazing. To help understand how the query was executed, query shows the query plan that was generated by the query.
All phenomenal demos that really highlight the performance gains in Azure SQL Database with In-memory and Clustered Columnstore.