Answers to Questions
- Does
OPTION (OPTIMIZE FOR UNKNOWN) have any impact on table-valued variables?
No. OPTIMIZE FOR UNKNOWN is just a variation of OPTIMIZE FOR (@variable_name = <literal_constant>). It only applies to variables for which a literal constant value could be supplied. Table-valued variables cannot be SET to a literal constant value (even NULL).
- Does
OPTION (OPTIMIZE FOR UNKNOWN) have any impact on table-valued parameters?
No, for the same reasons.
- What query hints other than
OPTION(RECOMPILE) can be used to influence cardinality estimates for table-valued parameters?
None. OPTION(RECOMPILE) is the hint to use if the cardinality saved with the plan is not expected to be representative or suitable for the current execution.
Recompilation
A recompilation always sees the current cardinality of a table variable or TVP. Recompilations can be triggered by a wide variety of causes, including statistics being out of date on another table used in the same statement. The point is that whatever the cause, a recompilation always sees current cardinality.
TF 2453 and the Recompilation Threshold (RT)
TF 2453 (available from SQL Server 2012 SP2) added a new possible recompilation cause by enabling the Recompilation Threshold crossing test for table variables and TVPs.
This test causes a recompilation if table cardinality changes by more than RT.
Assuming you do not get a trivial plan for the statement(s) referencing the TVP, recompilation will occur if RT is exceeded, either within the current module or across executions. How changes are recorded and accumulate across module executions is not entirely intuitive—see my linked article just above for details.
Deferred compilation
While an improvement, TF 2453 resulted in an initial compile of the statement in the batch as usual but added a recompilation for the statement(s) referencing the table variable or TVP when RT is exceeded.
Deferred compilation improves on this (for compatibility level 150 and above) by deferring the initial compilation. This new feature therefore made 2453 largely redundant.
Incorrect documentation
The exception to that is the RT crossing test for table cardinality. This test continues to require TF 2453 even on SQL Server 2019 and later, despite what the documentation says.
Starting with SQL Server 2019 (15.x), this became table variable deferred compilation, and Trace Flag 2453 has no effect.
To that extent, the problem of stored cardinality is largely addressed for SQL Server versions with TF 2453 enabled, because a recompilation is triggered by sufficient changes in table cardinality.
TF 2453 is effective for the RT threshold crossing test in all versions from SQL Server 2012 onward, regardless of compatibility level (even 90 on SQL Server 2012). It is not effective with QUERYTRACEON, which is again counter to the documentation.
Demo
SET NOCOUNT ON;
GO
CREATE TYPE dbo.TVP AS table (i integer NULL);
GO
CREATE PROCEDURE dbo.P
@TVP AS TVP READONLY
AS
SELECT
c = COUNT_BIG(*)
FROM @TVP
WHERE
-- Avoid trivial plan
1 = (SELECT 1);
I'm using documented trace flag 205 to report recompilations:
DBCC TRACEON (3604, 205, 2453);
GO
DECLARE @TVP AS TVP;
INSERT @TVP
SELECT SV.number
FROM master.dbo.spt_values AS SV
WHERE SV.type = N'P'
AND SV.number BETWEEN 1 AND 10;
EXECUTE dbo.P
@TVP = @TVP;
GO
DECLARE @TVP AS TVP;
INSERT @TVP
SELECT SV.number
FROM master.dbo.spt_values AS SV
WHERE SV.type = N'P'
AND SV.number BETWEEN 1 AND 510;
EXECUTE dbo.P
@TVP = @TVP;
GO
DBCC TRACEOFF (3604, 205, 2453);
Output:
Data related recompile(card change):
Tbl Dbid: 2,
Objid: -1082584932,
current rowcount: 510,
compile time rowcount: 10,
threshold: 500 (small table)
The execution plans show changed cardinality estimates for the TVP due to the recompilation.
Tidy up:
DROP PROCEDURE dbo.P;
DROP TYPE TVP;
