IT systems today are required to deliver the maximum performance with zero downtime and with maximum flexibility for developers and administrators. As developers, we all know that if performance of our applications is somehow lacking, then the cause of the slowdown must be in the database!

A major cause for performance problems inside database projects can be followed back to database design mistakes or to the limitations built in to the database engine to ensure data consistency for concurrent connections, or a mixture of both of these aspects.

Microsoft SQL Server adheres to the ACID theory. A simplified explanation of the ACID theory is that it describes how changes made to a database are required to fulfill four properties: Atomicity, Consistency, Isolation and Durability:

• Atomicity states that the contents of each transaction are either processed successfully and completely or fail in their entirety; half-transactions are not possible.
• Consistency states that a transaction brings a database from one valid state to another. This includes the adherence to the logical model inside the database (constraints, rules, triggers, and more). A transaction may not change a database from a logically valid state to a logically invalid state, for example, perform a data change that violates a unique constraint.
• Isolation states that the changes made inside a transaction are isolated from and are unaffected by other concurrent operations/transactions.
• Durability states that the changes made inside a transaction remain inside the database permanently, regardless of whether a system should go offline immediately after the transaction has completed.

To adhere to the ACID theory, Microsoft SQL Server employs the pessimistic concurrency model. Pessimistic concurrency assumes there will be conflicting concurrent transactions attempting to change the same data simultaneously. Therefore, transactions must (temporarily) lock the data they are changing to prevent other transactions from interfering with the isolation of those changes.

However, a system with enough concurrent users will eventually reach a logical limitation for transactional throughput through the design of the pessimistic concurrency model. Assuming unlimited hardware resources, the locking of data through pessimistic concurrency will become the bottleneck for transaction processing inside SQL Server.

Microsoft has developed a solution to this logical limitation, by implementing a method for employing the optimistic concurrency model. This model uses row versioning to avoid the need to lock and block data changes between concurrent users. Their implementation was made available in SQL Server 2014 under the codename Hekaton (Greek for a 100-fold), hinting at the possible performance increases) and the official feature name, In-Memory OLTP.