Does OPTIMIZE FOR UNKNOWN do anything for table-valued variables/parameters?
7
votes
2
answers
377
views
I have a query that is both prone to parameter sensitivity and is suffering from its table-valued parameter. I'm lazy and just want to solve this with query hints. When I'm lazy, I can solve parameter sensitivity with
OPTION (OPTIMIZE FOR UNKNOWN)
. This gives me three very tightly related questions:
1. Does OPTION (OPTIMIZE FOR UNKNOWN)
have any impact on table-valued **variables**?
2. Does OPTION (OPTIMIZE FOR UNKNOWN)
have any impact on table-valued **parameters**?
3. What query hints other than OPTION(RECOMPILE)
can be used to influence cardinality estimates for table-valued **parameters**?
Both my own experiments and the documentation suggest that this is a lost cause. Table-valued parameters are subject to parameter sniffing, but all that gets sniffed is their cardinality so OPTION (OPTIMIZE FOR UNKNOWN)
ought to do nothing.
As for the question of using other table hints, I know that I can change the estimate for operations on the table-valued parameter (e.g. OPTION (USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES')
) but I do not think that hints can influence the cardinality estimate for the parameter itself.
Asked by J. Mini
(1239 rep)
Apr 29, 2025, 06:19 PM
Last activity: May 2, 2025, 06:35 AM
Last activity: May 2, 2025, 06:35 AM