The key difference with the In-memory schema from the on-disk database is the organization of the tables. In-memory tables are implemented with hash indexes with no additional indexes created during the schema creation. Although the differences between hash and standard indexes are out of scope for this guide it is recommended to become familiar with the architecture as a key difference is the requirement to create all of the tables memory requirements ‘up-front’ with too little or too much memory impacting performance and therefore monitoring of the memory configuration usage is essential for workloads operating on In-memory databases. For a full implementation of in-memory tables a primary key is mandatory, however by definition the HISTORY table does not have a primary key. Therefore to implement all tables as in-memory and identity column has been added to the HISTORY table. It is important to note that despite the nomenclature of in-memory and on-disk databases in fact most of the workload of the on-disk database actually operates in-memory and high performance implementations can limit disk activity almost entirely to transaction logging in similarity to an in-memory database with persistence. Consequently orders of magnitude performance improvements should not be expected by moving to in-memory compared to a well optimised on-disk database.

During schema creation HammerDB sets the option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT for the memory optimized database. As a result the use of the snapshot isolation mode is mandatory and this will be set without intervention of the user. For the on-disk schema the default isolation level of READ COMMITTED is used with the addition of hints within the stored procedures for specific statements.

In-memory OLTP introduces the concept of Native Compilation for stored procedures that access in-memory tables and the tables configured for HammerDB have been implemented with this in mind. However at current releases supported features of native compilation are highly restricted to the extent that it would not be possible to implement stored procedures in a native compilation form that would then provide a fair comparison with the on-disk schema. For this reason the same T-SQL stored procedures have been implemented with minor changes in areas such as removed hints locks and transaction isolation levels. Native compilation remains a consideration for future releases when the necessary features are supported to provide a fair comparison.

An in-memory database must reside in a memory optimized filegroup with one or more containers. This database must be pre-created before running the HammerDB schema creation. If the database does not exist HammerDB will report the following error:

Database imoltp must be pre-created in a MEMORY_OPTIMIZED_DATA filegroup and empty, to specify an In-Memory build

If the database exists but is not in a MEMORY_OPTIMIZED_FILEGROUP HammerDB will report the following error.

Database imoltp exists but is not in a MEMORY_OPTIMIZED_DATA filegroup

Therefore to create an in-memory database firstly create a standard database using SSMS or at the command line as follows:

use imoltp
GO
ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS memory_optimized_data
GO  
ALTER DATABASE imoltp ADD FILE (NAME='imoltp_mod', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL13.SQLDEVELOP\MSSQL\data\imoltp_mod') TO FILEGROUP imoltp_mod
GO

For SQL Server on Linux specify the filesystem as follows:

ALTER DATABASE imoltp ADD FILE (NAME='imoltp_mod', FILENAME='C:\var\opt\mssql\data\imoltp_mod') TO FILEGROUP imoltp_mod
GO

Once the above statements have been run successfully the database is ready for an in-memory schema creation.