Using Indexes in SQL Server Memory-Optimized Tables

Using Indexes in SQL Server Memory-Optimized Tables
Rate this post

Introduction

In this article, we will discuss how different types of indexes in SQL Server memory-optimized tables affect performance. We will examine examples of how different index types can affect the performance of memory-optimized tables.

To make the topic discussion easier, we will make use of a rather large example. For the purposes of simplicity, this example will feature different replicas of a single table, against which we will run different queries. These replicas will use different indexes, or no indexes at all (except, of course, the primary keys – PKs).

Note, that the actual purpose of this article is not to compare performance between disk-based and memory-optimized tables in SQL Server per se. Its purpose is to examine how indexes affect performance in memory-optimized tables. However, in order to have a full picture of the experiments, timings are also provided for the corresponding disk-based table queries and the speedups are calculated using the most optimal configuration of disk-based tables as baselines.

Read More

Creating and Accessing In-Memory OLTP Databases and Tables

Creating and Accessing In-Memory OLTP Databases and Tables
Rate this post

This is the second article in a series of articles about SQL Server In-Memory OLTP.

The introductory article — SQL Server In-Memory OLTP, briefly introduced the basics of the new Hekaton engine. In this part, we will focus on practice. To be more specific, we will see how to create In-Memory optimized databases and tables, and also, how to assess them with help of T-SQL. Read More

SQL Server In-Memory OLTP: The Basics

SQL Server In-Memory OLTP: The Basics
Rate this post

This is the first article in a series of articles about In-Memory OLTP. It helps you to understand how the new Hekaton engine works internally. We will focus on details of in-memory optimized tables and indexes. This is the entry-level article, which means you do not need to be an SQL Server expert, however, you do need to have some basic knowledge about the traditional SQL Server engine. Read More