Questions you asked: What happens if a column is not In-Memory?

When we’re talking to customers or giving presentations questions often get asked that seem simple, but could have an answer that is worth showing with an example rather than just saying, “yes, it works this way”. So I’m going to start a series of posts that will address those questions. I will continue the series as I come across what I think are interesting questions.

This first post in the series of “Questions You Asked” will be “What happens if a column is not In-Memory?” And the follow on to that question was “… and will it show up in the execution plan?”

So let’s try it. We know that if all of the columns we are querying are populated in the In-Memory column store (IM column store) that we will see an execution plan that shows “TABLE ACCESS INMEMORY FULL” and our session statistics will also show that we accessed the object in the IM column store. So let’s see what happens if we query an object where not all of the columns are populated into the IM column store and we query one or more of those columns.

We’ll use the LINEORDER table and we’ll populate it minus the lo_orderdate, lo_quantity and lo_tax columns:

Next we’ll query the LINEORDER table and select one of the columns ( lo_quantity) that we didn’t populate in the IM column store:

We can see from the execution plan that we didn’t access the table in-memory. We did a normal full table scan. So the optimizer clearly recognizes that if not all of the columns of an object are populated in the IM column store then it has to access the object through the buffer cache/direct path.

Is this also true if we access one of the columns that isn’t populated through the WHERE clause?

Yes, even though the columns that we’re selecting are populated in the IM column store, the fact that we require a column that is not populated in the WHERE clause also forced us to access the table from the buffer cache/direct path.

Just for fun let’s run our query with just columns that are populated in the IM column store.

And of course we see that we have an in-memory execution plan.

So, if you query an object and one or more of the columns that you are querying have not been populated in the IM column store then your query will access that object through the row store, and the execution plan will not show an INMEMORY access.
 

Original publish date: October 2, 2015