REALITY: This Statement is both TRUE and FALSE.

FALSE: Sql Server 2014 provides an option to create Memory Resident Memory Optimized Tables which can have durable data. If tables are created with this option, data will not be lost even upon System Restart/Crash.

Data Durability is achieved by the DATA and DELTA files in the Memory Optimized file group and by writing to the Transaction Log but the amount of data which is written to the log is reduced considerably. Here DATA file’s will have the Inserted rows, whereas DELTA file will have the ID’s of the Deleted rows. On System Restart the Memory Optimized Tables Data is regenerated from DATA, DELTA files and the Transaction Log.

TRUE: If Memory Resident Memory Optimized Table is created without Data Durability option. Then System Restart/Crash etc will clear the complete Table Data.

REALITY: This statement is both TRUE and FALSE.

FALSE: Any modification to the Data of the Memory Optimized Tables created with Data Durability option needs to be written to the Transaction Log. This writing of log data by the Transaction Log Manger still has Latches. Logging for the Memory optimized table is much more efficient and the amount of log data generated is very less. As No logs are made for the index, schema/structure changes and UNDO information (i.e. Only Committed data is logged).

TRUE: Only for the Memory Optimized Tables which are created without Data Durability option. For such Tables there will not be any logging required, as they are designed to lose the data on System Restart.

REALITY: This statement is FALSE

FALSE: To Achieve NO Locking, Latching and Blocking, In-Memory OLTP engine uses True Multi-Version Optimistic Concurrency Control with FULL ACID Support. That is Transactions in In-Memory OLTP are Atomic, Consistent, Isolated and Durable.

For instance any request for the Data Row from a Memory Optimized Table, the In-Memory OLTP engine gives the version of the Row which is current at the time of the request. Consider a scenario where two Transactions get the same version of the record and trying to update it with different values. In such a Write-Write conflict scenario the Transaction which is updating last will fail as the record which it is manipulating is not a current version of the record while committing the Transaction. Because of this Microsoft is suggesting us to have appropriate Try-Catch and Re-Trying Mechanism built in the Application.

REALITY: This statement is FALSE

FALSE: In-Memory OLTP database engine component is fully integrated into Sql Server 2014 Relation Database engine, it is not a separate database product. The Same SSMS which is used for the Disk Based Tables/Indexes is used for the Memory Optimized Table’s and Indexes. In that way, it provides the same development, Management and administration experience.

REALITY: This statement is FALSE

FALSE: For Memory Optimized Tables I assume SEQUENCE is not the best alternative for IDENTITY column. As request for NEXT SEQUENCE value is Synchronized to assure returning unique value. In case of a sequence object created with NO CACHE option each time a next sequence value is requested, it will write the calculated value to the system table before returning it to the calling statement. But if sequence is created with CACHE of Size 50, it will write to the System table only after each 50 sequence values are served. For more details you can go through the Sequence Cache management and Internals. But in either case it leads to resource contention and waiting for locks and latches, which indirectly blocks In-Memory OLTP from scaling.

If possible please avoid Sequence for Memory Optimized Tables. Try to use something like UNIQUEIDENTIFIER data type and NEWID() function to generate uniqueidentifier value.

Will explore more on this and will come back with performance results with Sequence, With UniqueIdentifier etc.

REALITY: Both the statements are FALSE.

FALSE: In-Memory OLTP Database engine component doesn’t need Buffer Pool and also no 8KB data pages. Memory Optimized Tables provide better performance as the core engine uses the lock free algorithm (i.e. Multi-Version Optimistic Concurrency Control) which doesn’t require any lock and latches when the Memory optimized tables are referenced during the transaction processing. And for supporting Data Durability it still writes to the transaction log but the amount of data which is written to the log is reduced considerably. As the Indexes of the Memory Optimized Tables are only in Main Memory they will not be persisted on the disk, so to the log only the committed table data is written.

But on the other hand in case of disk based tables whose data residing in memory due to Table Scan or DBCC PINTABLE it still needs buffer pool, it will have Locking, Latching and blocking. Any modification to the table data will result in writing both table and index data to transaction log.