Incorrect Indexing Can Slow Down Memory Optimized Tables

Buzz on the street lately is that a Memory Optimized Table (MOT) in SQL Server greatly enhances OLTP SELECT performance. People in the industry are saying this because an entire table can be loaded completely into SQL Server memory without the unfortunate occurrence of table locks or page latching, which both add to the latency of the query completing.

Interestingly enough, our initial tests of this new feature in SQL Server actually proved otherwise; SELECT statements on a memory optimized table were taking significantly longer to complete compared to the same statements on a comparable disk-based table.

This will be Part 1 of a two-part blog series on analyzing the performance of memory optimized tables. In Part 2, we’ll go deeper into selecting the best indexing strategy based on the structures of the Hash and NonClustered index, as well as how they behave when used in SELECT queries.

Setting Up Test Tables

For this test, we used the ubiquitous AdventureWorksDW2014 sample database available on CodePlex. The Fact table for this comparison is the dbo.FactResellerSales table.

To create the IMO table xtp.IMFactResellerSales table, we initially used this statement:

To fill the sample MOT xtp.IMFactResellerSales, we used this statement:

Next, to update this table’s STATISTICS, we used this statement:

**Note: It’s extremely important you update statistics on a MOT after an operation that changes table content.

At this point, we should have 2 Fact tables ready for comparison.

Comparing SELECT Performance

We found this really nifty utility tool online, written by Adam Mechanic, called SQLQueryStress. The tool is very useful when turning queries, as it allows you to specify a test query, the number of times to run the query, and the number of threads to use while doing so. If you’d like to check out the tool, you can do so here.

The SELECT queries to compare were:

We set the SQLQueryStress to run each statement 200 times at 4 threads each. Here are the results!

Using the disk-based table:

Using the memory optimized table:

The disk-based table SELECT outperformed the memory optimized table by 40 percent!

What gives?

Optimize the Index on the Memory Optimized Table

We re-examined the memory optimized table’s INDEXES and wondered about the definitions:

We didn’t use the idx_hash_ProductKey index in our query, so maybe dropping that index might boost performance. Did we really need a primary key index? Maybe changing that to a simple NonClustered composite index might also yield some more improvements.

Then, we changed the INDEX definitions for xtp.IMFactResellerSales to:

We reordered the column “SalesOrderLineNumber” to precede the “SalesOrderNumber” column because the “SalesOrderLineNumber” column has more distinct values than “SalesOrderNumber.” One of the more common rules in indexing is placing the column with more distinct values ahead of other columns.

**Note that we also dropped the index idx_hash_ProductKey as well.

Now we are re-running the same test on the Memory Optimized table. We see a marked improvement in performance:

Design Considerations

It’s clear that using memory optimized tables takes some analysis in order to reap its true advantage, and it’s also clear that memory optimized tables aren’t silver bullets right out of the box. In this sample test environment, we see that the performance of a MOT is only marginally better than a disk-based table with the same structure and content.

The table sizes examined here might also not be representative of the types of tables encountered in real life scenarios. A more realistic test would be to fill both the tables with an amount of rows that cause the spillage of pages to tempdb. 

In part 2 of analyzing the performance of memory optimized tables, we’ll examine these improvements to our test environment.

Check out our post from earlier this week, 3 Advantages a Great Data Warehouse Solution Should Bring to Your Business.



Leave a Reply

Your email address will not be published.