The past few chapters have focused on indexing related to specialized indexing capabilities in SQL Server. The common thread through those indexes is their reliance on disk-based storage as the primary medium for storing the data and providing data access. SQL Server 2014 introduces a new manner in which a table can be made available,called a memory-optimized table.

Memory-optimized tables are significantly different from traditional tables, with memory being the primary medium for the tables. In this chapter, you’ll look at how the changes for memory-optimized tables affect your ability to index these tables and how to create the ideal indexes.

Memory-Optimized Tables Overview

Before digging into the indexing options for memory-optimized tables, let’s start with the basics of memory-optimized tables. A memory-optimized table is a new table type introduced in SQL Server 2014. Unlike traditional tables, along with their indexes, memory-optimized tables reside entirely in memory. Memory-optimized tables are supported through disk structures but are not reliant on them for transactional processing. This differs compared to traditional tables, where the table is based on disk storage and typically only a portion of the table and its indexes are in memory.

The value provided by memory-optimized tables is the performance gains that creating tables in this fashion provides for a database. By hosting and managing the entire table in memory, transactions do not need to wait for the disk to bring data to memory for transaction processing to proceed.

The implementation of memory-optimized tables results in a few changes to the way in which tables are architected in SQL Server. First, since the tables are now memory resident, it makes more sense for them to be structured in a manner that is optimal for accessing the data in memory versus retrieving a subset of the data from disk. For this reason, memory-optimized tables use hash and range indexes versus B-trees for storing data. Additionally, the tables don’t synchronize to disk or move around in memory like a traditional table, removing the need for latching between disk and memory structures.

To create memory-optimized tables in a database, there are a few things that need to be prepared within a database. To begin, a filegroup dedicated to memory-optimized data needs to be added to the database with a file to support the memory-optimized tables. Additionally, the database should have the property MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT enabled in most scenarios. This property hints to all transactions against memory-optimized tables to set the isolation level to SNAPSHOT. In Listing 7-1, the database MemOptIndexing is prepared using these settings.

USE master
GO

IF EXISTS(SELECT * FROM sys.databases WHERE name = 'MemOptIndexing')
DROP DATABASE MemOptIndexing
GO

CREATE DATABASE MemOptIndexing
GO

ALTER DATABASE MemOptIndexing
ADD FILEGROUP memoryOptimizedFG CONTAINS MEMORY_OPTIMIZED_DATA

--This file location may change in your environment
ALTER DATABASE MemOptIndexing
ADD FILE (name='memoryOptimizedData'
,filename='C:Program FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATAmemoryOptimizedData')
      TO FILEGROUP memoryOptimizedFG

ALTER DATABASE MemOptIndexing SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
GO

To see how to create a memory-optimized table, review the code in Listing 7-2. In this code example, you create the table dbo.SalesOrderHeader. There are two items to be aware of in the table schema. First, the option that creates the table as a memory-optimized table is the MEMORY_OPTIMIZED=ON option. The second is the inclusion of a NONCLUSTERED HASH index on the table to index the data within memory. Other than those items, the table is much like any other table created in SQL Server.

USE MemOptIndexing
GO
IF OBJECT_ID('dbo.SalesOrderHeader') IS NOT NULL
    DROP TABLE dbo.SalesOrderHeader

CREATE TABLE dbo.SalesOrderHeader(
    SalesOrderID int NOT NULL,
    OrderDate datetime,
    DueDate datetime,
    ShipDate datetime,
    [Status] tinyint,
    CONSTRAINT IX_SalesOrderHeader_Hash PRIMARY KEY
        NONCLUSTERED HASH (SalesOrderID)
        WITH (BUCKET_COUNT = 35000))
    WITH (MEMORY_OPTIMIZED = ON)

IF  OBJECT_ID('tempdb..#tempHeader') IS NOT NULL
    DROP TABLE #tempHeader

SELECT SalesOrderID
    ,OrderDate
    ,DueDate
    ,ShipDate
    ,[Status]
INTO #tempHeader
FROM AdventureWorks2014.sales.SalesOrderHeader

INSERT INTO dbo.SalesOrderHeader
SELECT SalesOrderID
    ,OrderDate
    ,DueDate
    ,ShipDate
    ,[Status]
FROM #tempHeader

SET STATISTICS IO ON
SET STATISTICS TIME ON
PRINT 'Memory Optimized Table'
SELECT *
FROM dbo.SalesOrderHeader
ORDER BY SalesOrderID

PRINT 'Traditional Table'
SELECT *
FROM AdventureWorks2014.sales.SalesOrderHeader
ORDER BY SalesOrderID

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

The additional code in Listing 7-2 inserts data into MemOptIndexing.dbo.SalesOrderHeader and queries that same data. To demonstrate the impact of querying the data in a memory-optimized table, a similar query against AdventureWorks2014.sales.SalesOrderHeader is included. Examining the results, shown in Listing 7-3, provides a few insights into memory-optimized tables. First, there is no I/O impact from the memory-optimized tables. While the AdventureWorks2014.sales.SalesOrderHeader query requires 689 reads, there are no reads for MemOptIndexing.dbo.SalesOrderHeader. Second, the amount of CPU time for MemOptIndexing.dbo.SalesOrderHeader is much lower, at 16 ms, than the CPU for the query against AdventureWorks2014.sales.SalesOrderHeader, which is 78 ms.

(31465 row(s) affected)

(31465 row(s) affected)
Memory Optimized Table

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(31465 row(s) affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 310 ms.
Traditional Table

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(31465 row(s) affected)
Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 78 ms,  elapsed time = 785 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

While there are many more aspects of memory-optimized tables that can be discussed, this overview is intended to provide some of the most basic aspects. The rest of this chapter will examine indexes on memory-optimized tables. While memory-optimized tables are completely in memory, indexes are still required. Being in memory doesn’t prevent the need to find specific data or filter resultsets.

To support indexing of memory-optimized tables, SQL Server supports two indexing options. These are hash and range indexes. Each memory-optimized table can support up to eight indexes. If a primary key is defined, this will be supported by one of the two index types and be one of the allowed indexes. If there is no primary key defined, the table must be created with at least one index. Additionally, since memory-optimized tables do not support schema changes, you will need to define all indexes for a memory-optimized table when it is created.

The remainder of this chapter will focus on the hash and range types of indexes with considerations for building each of them against a memory-optimized table.

Hash Indexes

The first index type for memory-optimized tables is the hash index. Hash indexes separate the data in the table into a fixed number of buckets. Rows inserted into the table then use a hash function to assign rows to the available buckets. These buckets provide the ability for queries to return specific rows based on the point lookup operations. Hash indexes are designed for types of query workloads where individual rows from the tables need to be retrieved.

When creating hash indexes, the number of buckets to create is a function of the number of rows planned, or expected, for the table. If there will be a large number of rows, a larger bucket count is required. This is an important part of creating and tuning hash indexes on memory-optimized tables. As the number of rows in each bucket increases, the time required to retrieve data increases. The ratio of rows to buckets is something that needs to be carefully considered.

It is generally recommended to over-allocate buckets to hash indexes, with the range of buckets recommended to be between two to five times the number of rows. While this is the recommended practice, it is important to consider how you will be using the tables within your environment and size the buckets accordingly.

To demonstrate the impact of bucket size, Listing 7-4 creates two memory-optimized tables. Both tables have 1,000,000 rows in them, with the first table having 1,000 buckets and the second having 1,000,000 buckets. With this configuration, there will be approximately 1,000 rows per bucket for the first table and 1 row per bucket in the second table.

USE MemOptIndexing
GO

IF OBJECT_ID('dbo.SalesOrderHeader_low') IS NOT NULL
    DROP TABLE dbo.SalesOrderHeader_low

CREATE TABLE dbo.SalesOrderHeader_low(
    SalesOrderID int NOT NULL
    ,Column1 uniqueidentifier
    ,CONSTRAINT IX_SalesOrderHeader_Hash_low PRIMARY KEY
        NONCLUSTERED HASH (SalesOrderID)
        WITH (BUCKET_COUNT = 1000))
    WITH (MEMORY_OPTIMIZED = ON);

WITH L1(z) AS (SELECT 0 UNION ALL SELECT 0)
, L2(z) AS (SELECT 0 FROM L1 a CROSS JOIN L1 b)
, L3(z) AS (SELECT 0 FROM L2 a CROSS JOIN L2 b)
, L4(z) AS (SELECT 0 FROM L3 a CROSS JOIN L3 b)
, L5(z) AS (SELECT 0 FROM L4 a CROSS JOIN L4 b)
, L6(z) AS (SELECT TOP 1000000 0 FROM L5 a CROSS JOIN L5 b)
INSERT INTO dbo.SalesOrderHeader_low
SELECT ROW_NUMBER() OVER (ORDER BY z) AS RowID, NEWID()
FROM L6;
GO

IF OBJECT_ID('dbo.SalesOrderHeader_high') IS NOT NULL
    DROP TABLE dbo.SalesOrderHeader_high

CREATE TABLE dbo.SalesOrderHeader_high(
    SalesOrderID int NOT NULL
    ,Column1 uniqueidentifier
    ,CONSTRAINT IX_SalesOrderHeader_hash_high PRIMARY KEY
        NONCLUSTERED HASH (SalesOrderID)
        WITH (BUCKET_COUNT = 1000000))
    WITH (MEMORY_OPTIMIZED = ON);

WITH L1(z) AS (SELECT 0 UNION ALL SELECT 0)
, L2(z) AS (SELECT 0 FROM L1 a CROSS JOIN L1 b)
, L3(z) AS (SELECT 0 FROM L2 a CROSS JOIN L2 b)
, L4(z) AS (SELECT 0 FROM L3 a CROSS JOIN L3 b)
, L5(z) AS (SELECT 0 FROM L4 a CROSS JOIN L4 b)
, L6(z) AS (SELECT TOP 1000000 0 FROM L5 a CROSS JOIN L5 b)
INSERT INTO dbo.SalesOrderHeader_high
SELECT ROW_NUMBER() OVER (ORDER BY z) AS RowID, NEWID()
FROM L6;

Prior to executing the next piece of code for this demo, create an Extended Events session based on the Query Detail Tracking template. The session should be created with the default configuration and then launched to the Extended Events live data viewer. Add the columns session_id, statement, writes, physical_reads, logical_reads, duration, and cpu_time to the live viewer window. Lastly, filter the session_id in the output by the session_id values for Listing 7-5, 7-6, 7-8, and 7-9 and the event sql_statement_completed.

When you execute a query against both tables to return the same row, shown in Listing 7-5, you get a slight performance difference between the two. In this sample execution, the execution time for the first query was 493 μs versus 70 μs, shown in Figure 7-1. While this difference is small in total duration, the difference between them for the same query is significant. In solutions where memory-optimized tables will be used to retrieve results, this kind of performance difference can be important.

USE MemOptIndexing
GO

SET STATISTICS TIME ON

PRINT 'Memory Optimized Table with 1000 buckets'
SELECT *
FROM dbo.SalesOrderHeader_low
WHERE SalesOrderID = 42
ORDER BY SalesOrderID

PRINT 'Memory Optimized Table with 1,000,000 buckets'
SELECT *
FROM dbo.SalesOrderHeader_high
WHERE SalesOrderID = 42
ORDER BY SalesOrderID

SET STATISTICS TIME OFF

It’s important not to interpret the results of the last demo to indicate that a 1:1 ratio of buckets to rows is the best practice. If you run another set of queries that retrieves more than a single row, in this case five discrete rows as shown in Listing 7-6, the performance is impacted. In this case, the performance advantage shifts to buckets with more rows. The results now are 1,221 μs for the query on the first table versus 202,218 μs for the second table’s query, as shown in Figure 7-2.

USE MemOptIndexing
GO

SET STATISTICS TIME ON

PRINT 'Memory Optimized Table with 1000 buckets'
SELECT *
FROM dbo.SalesOrderHeader_low
WHERE SalesOrderID in (42, 5000, 12344, 123562, 373457)
ORDER BY SalesOrderID

PRINT 'Memory Optimized Table with 1,000,000 buckets'
SELECT *
FROM dbo.SalesOrderHeader_high
WHERE SalesOrderID in (42, 5000, 12344, 123562, 373457)
ORDER BY SalesOrderID

SET STATISTICS TIME OFF

When working with hash indexes, it is important to understand how SQL Server is using the buckets in the hash. One important thing to note is that just because there are enough buckets for each table to have their own bucket, that doesn’t mean each row will get its own bucket. To review the statistics for hash indexes created in this chapter, run the query in Listing 7-7 that accesses the DMV sys.dm_db_xtp_hash_index_stats. This DMV provides information on the number of buckets and how those buckets are populated.

USE MemOptIndexing
GO

SELECT OBJECT_NAME(hs.object_id) AS object_name
,i.name as index_name
,hs.total_bucket_count
,hs.empty_bucket_count
,FLOOR((CAST(empty_bucket_count as float)/total_bucket_count) * 100) AS empty_bucket_percent
,hs.avg_chain_length
,hs.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats AS hs
INNER JOIN sys.indexes AS i ON hs.object_id=i.object_id AND hs.index_id=i.index_id

Reviewing the results of Listing 7-7, provided in Figure 7-3, you can see there are a few interesting items to notice. To start, the first table with the 1,000 buckets specified (SalesOrderHeader_low) actually has 1,024 buckets for the index. This is because buckets are created in allocations that align to the power of two. This is the same reason there are 1,048,576 buckets for the 1,000,000 bucket index on SalesOrderHeader_high. The next interesting piece is the number of empty buckets in the hash index on SalesOrderHeader_high. With 1,000,000 rows and more than a million buckets, there are still 37 percent of the buckets that are empty. This happens because with the deterministic hashing function, some hashed values are repeated within the range of values before all the buckets are utilized. This is something to consider when building hash indexes, especially when aiming to have a 1:1 ratio of buckets to rows.

Hash indexes with memory-optimized tables are an important indexing choice when there will be many queries that will access individual rows and seek operations that will provide optimal plans. When building the tables and hash indexes, focus on setting the number of buckets to a size that presents a reasonable ratio of rows to buckets with consideration for the number of rows that will be retrieved through queries.

Range Indexes

The second type of index that is supported for memory-optimized tables is the range index. Range indexes are used to support, as the name implies, range scans of data, along with ordered scans. They leverage a variation of a B-tree, which Microsoft is calling a Bw-tree. The key difference between these two structures is the reference between the nodes in the Bw-tree, which refers to memory locations versus physical page location. When it comes to determining whether to include a range index on a memory-optimized table, the primary consideration will be whether there will be range scans or ORDER BY statements that the table needs to support.

To create a range index on a memory-optimized table, you declare the index within the schema of the table by indicating a NONCLUSTERED index with the key values. As shown in Listing 7-8, the index IX_SalesOrderHeader is a range index on the SalesOrderID column. Unlike hash indexes, there are no other configuration items to consider and the index doesn’t need any bucket consideration when they are created.

USE MemOptIndexing
GO

IF OBJECT_ID('dbo.SalesOrderHeader_high_range') IS NOT NULL
    DROP TABLE dbo.SalesOrderHeader_high_range

CREATE TABLE dbo.SalesOrderHeader_high_range(
    SalesOrderID int NOT NULL
    ,Column1 uniqueidentifier
    ,CONSTRAINT IX_SalesOrderHeader_hash_high_range PRIMARY KEY
        NONCLUSTERED HASH (SalesOrderID)
        WITH (BUCKET_COUNT = 1000000)
    ,INDEX IX_SalesOrderHeader NONCLUSTERED (SalesOrderID)
    )
    WITH (MEMORY_OPTIMIZED = ON);

WITH L1(z) AS (SELECT 0 UNION ALL SELECT 0)
, L2(z) AS (SELECT 0 FROM L1 a CROSS JOIN L1 b)
, L3(z) AS (SELECT 0 FROM L2 a CROSS JOIN L2 b)
, L4(z) AS (SELECT 0 FROM L3 a CROSS JOIN L3 b)
, L5(z) AS (SELECT 0 FROM L4 a CROSS JOIN L4 b)
, L6(z) AS (SELECT TOP 1000000 0 FROM L5 a CROSS JOIN L5 b)
INSERT INTO dbo.SalesOrderHeader_high_range
SELECT ROW_NUMBER() OVER (ORDER BY z) AS RowID, NEWID()
FROM L6;

To demonstrate the value of the range index on memory-optimized tables, let’s use the table created in Listing 7-8 on some queries that will leverage range scans. For this, you will use Listing 7-9, which queries the new table (dbo.SalesOrderHeader_high_range) and the table previously created with just a hash index (dbo.SalesOrderHeader_high). By executing a query against both of those tables for the rows with SalesOrderID between 100 and 10,000, you can see there is a big difference in the execution time. The query with just the hash index on the table runs in 372 ms (see Figure 7-4), while the query against the table with the range index runs in 180 ms. In this case, range indexes provide a substantial performance improvement.

USE MemOptIndexing
GO

SET STATISTICS TIME ON

SELECT *
FROM dbo.SalesOrderHeader_high
WHERE SalesOrderID BETWEEN 100 AND 10000
ORDER BY SalesOrderID

SELECT *
FROM dbo.SalesOrderHeader_high_range
WHERE SalesOrderID BETWEEN 100 AND 10000
ORDER BY SalesOrderID

SET STATISTICS TIME OFF

In a similar fashion, ORDER BY statements are also greatly improved when range indexes on memory-optimized tables are available. Using the code in Listing 7-10, you run two queries against the same tables from the previous demonstration. In this case, using the output in Figure 7-5, you see that the range scan took 103 μs compared to the 372,000 μs that the hash index requires. Again, you get a substantial performance improvement through the use of the range index.

USE MemOptIndexing
GO

SET STATISTICS TIME ON

SELECT TOP 100 *
FROM dbo.SalesOrderHeader_high
ORDER BY SalesOrderID

SELECT TOP 100 *
FROM dbo.SalesOrderHeader_high_range
ORDER BY SalesOrderID

SET STATISTICS TIME OFF

Similar to hash indexes, range indexes provide a substantial performance improvement opportunity when creating memory-optimized tables. The need to perform range scans and order results is a common scenario in many applications. These operations are greatly improved by the use of range indexes.

Summary

This chapter looked at the types of indexes available for memory-optimized tables. These options, which include hash and range indexes, are the power behind the ability for memory-optimized tables to provide the incredible performance that they deliver. As demonstrated, each index types aligns to different querying patterns on your tables, and it is important to understand those patterns to build the right indexes for your memory-optimized tables and the workloads that they support.