Parameter sniffing on table valued parameters

Question

I'm fairly certain that adding parameter sniffing to table valued parameters is of little or no value however I was wondering if someone could confirm this?

(INT_LIST is a user defined table type which is a single column of type INT)

CREATE PROCEDURE [dbo].[TVPSniffTest](
    @param1 varchar(50),
    @idList INT_LIST readonly
)
AS
BEGIN 
   DECLARE @param1_sniff VARCHAR(50) = @param1 --this is worth doing

   DECLARE @idList_sniff INT_LIST
   INSERT INTO @idList_sniff SELECT value FROM @idList --will this help?

   --query code here
END

Answer 1

As Jeroen already mentioned, there is no parameter sniffing issue with TVPs. And also that one option to mitigate the lack of statistics is to copy the TVP to a local temp table (which does maintain statistics).

But, another option that is sometimes more efficient is to do a statement-level recompile on any queries using the table variable (i.e. the TVP). The statistics won't be maintained across queries so it needs to be done on any query that involves the table variable that is not something like a simple SELECT.

The following illustrates this behavior:

DECLARE @TableVariable TABLE (Col1 INT NOT NULL);

INSERT INTO @TableVariable (Col1)
  SELECT so.[object_id]
  FROM   [master].[sys].[objects] so;

-- Control-M to turn on "Include Actual Execution Plan".
-- For each of the 3 following queries, hover over the "Table Scan"
--   operator to see the "Estimated Number of Rows".

SELECT * FROM @TableVariable; -- Estimated Number of Rows = 1 (incorrect)

SELECT * FROM @TableVariable
OPTION (RECOMPILE); -- Estimated Number of Rows = 91 (correct)

SELECT * FROM @TableVariable; -- Estimated Number of Rows = 1 (back to incorrect)

Answer 2

This has no effect whatsoever -- in fact, it's detrimental to performance because you're copying the whole table first.

The optimizer maintains no statistics for either table-valued parameters or table variables. This can easily lead to bad query plans with cardinality mismatches; the solution for that is usually an intermediate temp table. In any case, parameter sniffing won't be an issue -- the table contents are never used to optimize the query plan.

Incidentally, while you can assign the parameter to a local variable to circumvent sniffing, a more flexible option is to use the OPTIMIZE FOR or RECOMPILE hints in queries that are particularly affected (or WITH RECOMPILE on the whole stored procedure, but that's a little more drastic). This prevents cluttering the procedure with copies of everything.