In-Memory OLTP query plans: The Unexpected

Spills to TempDB

While testing the new features of In-Memory OLTP in SQL 2016, I discovered something unexpected: query plans for interop can spill to TempDB.

It was explained to me that the accessing of memory-optimized tables is done at the lowest level, and that if sort or join operators are not allocated enough memory, they’ll spill. I contacted Microsoft and expressed my surprise, and their reply was: “If the plan spills with disk-based, it spills with memory-optimized”.

This is not possible with natively compiled stored procedures. For sorts or joins, you simply have to have enough memory, and unfortunately the xtp DMVs don’t reveal the level of granularity required to track how much memory is being used. All joins are nested loop, so the memory requirements are typically modest.

So we need to remember that for interop procedure that access memory-optimized tables, spills to TempDB can still occur.

image

image

2 thoughts on “In-Memory OLTP query plans: The Unexpected

  1. epay.pdc4u

    When you have the Query Store enabled on databases that use In-Memory OLTP features like Memory-Optimized tables and natively compiled stored procedures, query and plan information is captured automatically.

    Reply
  2. zzday

    Querying the cache on a production server can have a noticeable affect if the query is not well targeted to only return the data you really want to see and filtered to reduce the the amount of cache that has to be scanned.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *