Cardinality Estimation On Memory-Optimzied Table Variables

Jack Li explains that the cardinality estimator works the same for memory-optimized table variables as it does for regular table variables:

In a previous blog, I talked about memory optimized table consumes memory until end of the batch.   In this blog, I want to make you aware of cardinality estimate of memory optimized table as we have had customers who called in for clarifications.  By default memory optimized table variable behaves the same way as disk based table variable. It will have 1 row as an estimate.   In disk based table variable, you can control estimate by using option (recompile) at statement level (see this blog) or use trace flag 2453.

You can control the same behavior using the two approaches on memory optimized table variable if you use it in an ad hoc query or inside a regular TSQL stored procedure.  The behavior will be the same. This little repro will show the estimate is correct with option (recompile).

Jack also explains how this works for natively compiled stored procedures (spoilers:  it doesn’t), so read the whole thing.

Related Posts

Fun With Temp Tables

Kenneth Fisher answers a Brent Ozar pop quiz regarding temp tables: Go ahead and give it a shot .. I’ll wait. So? What do you think? Did you get it right? I did, but I wasn’t 100% certain, nor did I initially think through all of the implications. The question actually has more depth to […]

Read More

Temp Table Caching

Paul White explains how to cache temporary objects: Table variables and local temporary tables are both capable of being cached. To qualify for caching, a local temporary table or table variable must be created in a module: Stored procedure (including a temporary stored procedure) Trigger Multi-statement table-valued function Scalar user-defined function The return value of […]

Read More

Categories