In the previous blog post In-Memory OLTP Indexes – Part 1: Recommendations, we gave you an update on the latest features of In-Memory OLTP technology. We also summarized the key characteristics of memory-optimized indexes and shared some guidelines and recommendations on how to best choose and configure an index for your memory-optimized table. At this point, if you haven’t read through the previous blog post, we strongly recommend you do so. In this blog post we continue onwards; take the learnings from the previous blog (Part 1) and using some sample examples, apply them in practice. The learnings from this blog post (Part 2) will be particularly useful if you are experiencing query performance issues with memory-optimized tables; either after migration from disk-based tables or in general, with your production workload leveraging memory-optimized tables.
To summarize this blog post covers the following:
Common mistakes and pitfalls to avoid when working with memory-optimized indexes.
Best practices to follow when configuring your memory-optimized indexes for optimal performance.
Troubleshooting and Mitigating your query performance issues with memory-optimized indexes.
Monitoring your query performance with memory-optimized indexes.
There’s a lot of detail in this post, and tuning these types of indexes isn’t quite the same as normal, disk-based indexes.