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.