Post Undeleted by Josh Darnell
3 Fixed my dumb broken answer thanks to Erik's smart, non-broken blogging skills.
source | link

Prior to SQL Server 2019, the estimated number of rows in a table variable is fixed at one row.Check out this post from Erik Darling:

To get more accurate cardinality estimates for a join to aTable Valued Parameters: Unexpected Parameter Sniffing

The gist of it is that table-valued parameter, you can add a RECOMPILE hintparameters are susceptible to the query with the joinparameter sniffing just like other parameters. This should tell SQL Server to use If the actual number ofexecution plan happened to get cached when 2,000 rows were passed in via the table variable.

What you're describing doesn't sound possible unless you're using SQL Server 2019's deferred table variable compilation, which is still in preview - so you wouldn't be using that in productionTVP, right?then that's the plan you're stuck with :P(until a recompile).

ItAll of the usual solutions to parameter sniffing apply. It would be helpful if you could provide an actual execution plan and query text to get a better idea of what's going on.

Probably the "simplest" solution is to add a RECOMPILE hint to the query that's performing the join to this big table. This will incur higher CPU (due to more plan recompiles) and you lose the "predictability" of the query plan, but it could solve the immediate plan quality issue. 

This should also help with the problem of fixed join estimates, mentioned in Erik's post:

Non-join cardinality estimates behave like local variables (fixed estimates)

...we get 10% for equality, 30% for inequality, and 9% for two inequalities.

Prior to SQL Server 2019, the estimated number of rows in a table variable is fixed at one row.

To get more accurate cardinality estimates for a join to a table-valued parameter, you can add a RECOMPILE hint to the query with the join. This should tell SQL Server to use the actual number of rows in the table variable.

What you're describing doesn't sound possible unless you're using SQL Server 2019's deferred table variable compilation, which is still in preview - so you wouldn't be using that in production, right? :P

It would be helpful if you could provide an actual execution plan and query text to get a better idea of what's going on.

Check out this post from Erik Darling:

Table Valued Parameters: Unexpected Parameter Sniffing

The gist of it is that table-valued parameters are susceptible to parameter sniffing just like other parameters. If the execution plan happened to get cached when 2,000 rows were passed in via the TVP, then that's the plan you're stuck with (until a recompile).

All of the usual solutions to parameter sniffing apply. It would be helpful if you could provide an actual execution plan and query text to get a better idea of what's going on.

Probably the "simplest" solution is to add a RECOMPILE hint to the query that's performing the join to this big table. This will incur higher CPU (due to more plan recompiles) and you lose the "predictability" of the query plan, but it could solve the immediate plan quality issue. 

This should also help with the problem of fixed join estimates, mentioned in Erik's post:

Non-join cardinality estimates behave like local variables (fixed estimates)

...we get 10% for equality, 30% for inequality, and 9% for two inequalities.

    Post Deleted by Josh Darnell
2 I was thinking of MSTVFs with the new CE - not relevant here.
source | link

Prior to SQL Server 2019, the estimated number of rows in a table variable is fixed at one row1.

To get more accurate cardinality estimates for a join to a table-valued parameter, you can add a RECOMPILE hint to the query with the join. This should tell SQL Server to use the actual number of rows in the table variable.

What you're describing doesn't sound possible unless you're using SQL Server 2019's deferred table variable compilation, which is still in preview - so you wouldn't be using that in production, right? :P

It would be helpful if you could provide an actual execution plan and query text to get a better idea of what's going on.

1 I think it can be 100 sometimes, but for the life of me I can't remember when / why

Prior to SQL Server 2019, the estimated number of rows in a table variable is fixed at one row1.

To get more accurate cardinality estimates for a join to a table-valued parameter, you can add a RECOMPILE hint to the query with the join. This should tell SQL Server to use the actual number of rows in the table variable.

What you're describing doesn't sound possible unless you're using SQL Server 2019's deferred table variable compilation, which is still in preview - so you wouldn't be using that in production, right? :P

It would be helpful if you could provide an actual execution plan and query text to get a better idea of what's going on.

1 I think it can be 100 sometimes, but for the life of me I can't remember when / why

Prior to SQL Server 2019, the estimated number of rows in a table variable is fixed at one row.

To get more accurate cardinality estimates for a join to a table-valued parameter, you can add a RECOMPILE hint to the query with the join. This should tell SQL Server to use the actual number of rows in the table variable.

What you're describing doesn't sound possible unless you're using SQL Server 2019's deferred table variable compilation, which is still in preview - so you wouldn't be using that in production, right? :P

It would be helpful if you could provide an actual execution plan and query text to get a better idea of what's going on.

1
source | link

Prior to SQL Server 2019, the estimated number of rows in a table variable is fixed at one row1.

To get more accurate cardinality estimates for a join to a table-valued parameter, you can add a RECOMPILE hint to the query with the join. This should tell SQL Server to use the actual number of rows in the table variable.

What you're describing doesn't sound possible unless you're using SQL Server 2019's deferred table variable compilation, which is still in preview - so you wouldn't be using that in production, right? :P

It would be helpful if you could provide an actual execution plan and query text to get a better idea of what's going on.

1 I think it can be 100 sometimes, but for the life of me I can't remember when / why