oops
It does seem that OPTIMIZE FOR UNKNOWN has no effect at all on either table variables or parameters. In fact, if you try to specify OPTIMIZE FOR (@data UNKNOWN) or OPTIMIZE FOR (@data = NULL) then you get
Msg 137 Level 16 State 1 Line 7
Must declare the scalar variable "@data".
which tells us this hint is only intended for scalar variables, entirely undocumented.
Historically, table variables did not get any cardinality estimation and had an estimate of 1 or 100 rows. Table Variable Deferred Compilation was introduced using trace flag 2453, and from 2019 as a database which you can disable using a hint. This will usually cause the estimates to be correct, which in your case seems to be undesired.
get the hint
- Turning off TF2453, or using the query hint
USE HINT (N'DISABLE_DEFERRED_COMPILATION_TV') only works on table variables, not parameters.
ROBUST PLAN and FAST 1 do not affect cardinality estimation here.
the bad
You can insert the whole thing into a table variable. This by default gets an estimate of 1 or 100 rows, depending on version.
You also need to turn off TF2453 if it's on. On 2019 and later, you need to instead disable Deferred Table Variable Compilation. You could instead use QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100, but that's a broad hammer.
CREATE OR ALTER PROCEDURE p
@data dbo.t READONLY
AS
DECLARE @data2 dbo.t;
INSERT @data2
SELECT *
FROM @data;
SELECT COUNT(*) AS c
FROM @data2
OPTION (USE HINT (N'DISABLE_DEFERRED_COMPILATION_TV'));
This has the obvious downside of needing to copy the entire TVP, which may be very large.
the ugly
You can use sp_prepare and sp_execute with dynamic SQL, to force compilation without cardinality estimation, as noted in @PaulWhite's article. There is no copying in this instance.
CREATE OR ALTER PROCEDURE p
@data dbo.t READONLY
AS
DECLARE @handle int;
EXEC sp_prepare @handle OUT,
N'@data dbo.t READONLY',
N'
SELECT COUNT(*) AS c
FROM @data;
';
EXEC sp_execute @handle, @data = @data;
This only works with simple statements, otherwise compilation gets deferred and the parameter will be sniffed.
But it makes the procedure a bit of a mess to write and debug.
the good
Just sort your query out. Why does OPTION (RECOMPILE) not solve the issue, or what is throwing off the optimizer when it does have the correct estimate? Solve that instead.
Or just get a good plan and force it using Query Store.
You can see the options in this fiddle.
