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

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

Join Simplification With Table Variables

Erik Darling has an example of how adding a key constraint to a table variable allowed the optimizer to filter it out: I was trying to come up with a demo for something totally different. Don’t ask. Seriously. It’s top secret. Okay, so it’s just embarrassing. Anyway. I had these two queries. Which are actually […]

Read More

Leave a Reply

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

Categories

May 2017
MTWTFSS
« Apr  
1234567
891011121314
15161718192021
22232425262728
293031