Content type: Best Practices

Published 
6 min read

In-Memory OLTP Index Troubleshooting, Part II 

With the In-Memory OLTP feature in SQL Server 2014 we introduce two new types of indexes for memory-optimized tables: the memory-optimized NONCLUSTERED indexes, and the memory-optimized HASH indexes. Memory-optimized NONCLUSTERED indexes behave similar to traditional NONCLUSTERED indexes from an application point-of-view. The main difference being that memory-optimized indexes are always covering (i.e.
Published 
6 min read

Merge Operation in Memory-Optimized Tables 

In the Storage Allocation and Management for Memory-Optimized Tables, we had briefly touched upon the Merge operation. This blog describes Merge operation in detail. We also recommend you to refer to implementing durability for memory-optimized tables for a good understanding of data/delta files and CFPs The metadata of all Checkpoint File Pairs (i.e.
Published 
6 min read

Storage Allocation and Management for Memory-Optimized Tables 

As described in implementing durability for memory optimized tables, the storage requirements and its management for memory optimized tables is very different compared to the disk-based tables. If you are migrating a subset of your disk-based tables to memory-optimized tables or are just developing a new application with memory-optimized tables, you will notice these differences.
Published 
5 min read

SQL Server 2014 In-Memory OLTP: Memory Management for Memory-Optimized Tables 

Memory-optimized tables must fully reside in memory and can’t be paged out. Unlike disk-based tables where insufficient memory can slowdown an application, the impact to  memory-optimized tables upon encountering out-of-memory can be severe, causing  DML (i.e. delete, insert or update) operations to fail. While this adds a new dimension to managing memory, the application failure due to resource errors is not something new.
Published 
6 min read

In-Memory OLTP: How Durability is Achieved for Memory-Optimized Tables 

In-memory OLTP provides full durability for memory-optimized tables. When a transaction that has made changes to memory-optimized table commits, SQL Server, just like it does for disk-based tables, guarantees that the changes are permanent and can survive the database restart provided the underlying storage is available. There are two key components of durability.
Published 
3 min read

In-Memory OLTP Programmability: Concurrency and Transaction Isolation for Memory-optimized Tables 

The new In-Memory OLTP feature introduces memory-optimized tables and natively compiled stored procedures in SQL Server 2014, to optimize performance and scalability of OLTP workloads. To benefit from this technology in an existing SQL Server application, you need to migrate the performance critical tables and stored procedures to memory-optimized tables and natively compiled stored procedures respectively.