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
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.