2

We have a number of stored procedures where we pass in a user defined table type parameter. As per the MS doc below, my understanding is that SQL Server doesn't retain statistics on these (and therefore should only estimate a single row?).

https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017

Recently, we've seen issues with plan generation where the estimated number of rows in the table is a high as 2000 (normally only 1 or 2 rows are in passed in). This is caching a plan which is highly inefficient (wrong join order with a largish table ~160M rows) and causing some issues.

Am I wrong here about what the estimates should be? I could force the join type/order but I'd rather avoid this if at all possible.

Would really appreciate any thoughts/feedback people might have.

Thanks Dave

Improve this question
4
  • In high-frequency SPs, recompiling can be too expensive. And it also makes using the Query Store less fun.. I generally use hinting in these cases, or choose an domain aspect of the TVP data that ‘encourages’ SQL to only consider certain plans. If the TVP has a PK/CI, this is also considered. I’ve found that using range operators generally changes the estimation usage in desirable ways. For a TVP that ranges of just 1..2000, I’d probably hint appropriately as it’s likely the same plan will cover all cases.
    – user2864740
    Commented Feb 19, 2021 at 4:43
  • Another option, to avoid always-recompiles, is to recompile only when the TVP exceeds a certain size (say 100). This requires duplication of the branches or dynamic SQL.. although it might entirely eliminate the need of hints. Of course, if the SP is infrequently called, an always recompile looks like a much more suitable choice.
    – user2864740
    Commented Feb 19, 2021 at 4:47
  • This goes into details about the TV/TVP trace (2453) and RECOMPILE costs and how to calculate the overhead —sqlperformance.com/2014/06/t-sql-queries/… Unfortunately this can’t be used as a query hint. It would be amazing to have an OPTION OPTIMIZE FOR TVP SIZE or RECOMPILE IF. Neither exist.
    – user2864740
    Commented Feb 19, 2021 at 5:10
  • It’s possible to synthesizes a fake row count, with TOP..OPTIMIZE FOR.. (upper limit) and a synthetic “many” TVF apply (to boost cardinality / lower limit). However, at this point I just opt for writing the query to ‘encourage’ SQL Server to limit plan choices to those that are acceptable or write the hints in.
    – user2864740
    Commented Feb 19, 2021 at 5:19

1 Answer 1

Reset to default
4

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.