Microsoft introduced in-memory OLTP in SQL Server 2014, advertising that queries can be up to 100 times faster. In this blog series I will explain the design principles of the in-memory OLTP engine to explain why it has the potential to be significantly faster than the traditional on-disk engine.
When a request is sent to the query engine, the traditional storage engine must first read the data pages from disk if they are not yet available in the buffer pool. Reading from the hard disk is still one of the most time-consuming operations and therefore largely determines the execution of the queries. Reading data from memory is much faster than reading from disk but memory was expensive in the past, therefore SQL Server loads data in the buffer pool on demand only. Memory is much cheaper nowadays, so that the core idea of the on-disk engine to regulate the memory allocation is no longer valid. In-memory OLTP does not save any data pages on disk but only reads and saves records in memory. Therefore, it is possible to avoid the complexity of handling the data pages in the buffer pool and skip physical reads which can result in much faster query execution.
The following examples compare the on-disk and the in-memory OLTP engine with regard to IO reads and memory allocation.
For the following analysis, we nee to create a clustered table with 5,000,000 records:
When a request is sent to the query engine, the traditional storage engine must first read the data pages from disk if they are not yet available in the buffer pool. Reading from the hard disk is still one of the most time-consuming operations and therefore largely determines the execution of the queries. Reading data from memory is much faster than reading from disk but memory was expensive in the past, therefore SQL Server loads data in the buffer pool on demand only. Memory is much cheaper nowadays, so that the core idea of the on-disk engine to regulate the memory allocation is no longer valid. In-memory OLTP does not save any data pages on disk but only reads and saves records in memory. Therefore, it is possible to avoid the complexity of handling the data pages in the buffer pool and skip physical reads which can result in much faster query execution.
The following examples compare the on-disk and the in-memory OLTP engine with regard to IO reads and memory allocation.
For the following analysis, we nee to create a clustered table with 5,000,000 records:
USE AdventureWorks2017; GO DROP TABLE IF EXISTS Sales.SalesOrderDetailMedium; GO -- Create a table with 5,000,000 records SELECT TOP 5000000 * INTO Sales.SalesOrderDetailMedium FROM Sales.SalesOrderDetailBig; GO -- Add a clustered key -- Later we will see that an in-memory table always needs an index. -- Since we are comparing the size of in-memory to an on-disk table, it is -- appropriate to create a similar index. CREATE CLUSTERED INDEX CL_SalesOrderDetailMedium_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetailMedium (