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

score 5 · Answer 1 · answered Feb 24 '15 at 15:18

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)

This is a nice addendum because the documentation is not at all specific that `OPTION (RECOMPILE)` suddenly makes the optimizer consider the cardinality of the table. All it says is that the query plan "uses the current values of any local variables in the query", but I wouldn't infer from that that it suddenly starts counting rows after all. — Jeroen Mostert, Feb 24 '15 at 15:45
@JeroenMostert Yeah, that is a little trick I picked in a local user group meeting. I've also been told that using `TOP (row_count)` in queries with Table Variables will use the correct number of rows, but I haven't been able to prove that so I did not include it here. — Solomon Rutzky, Feb 24 '15 at 15:56

score 4 · Accepted Answer · answered Feb 24 '15 at 12:53

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.

Good info. I realize you did qualify using `WITH RECOMPILE` on the whole proc, but that should probably be emphasized a little more since doing so on a proc that gets called A LOT (i.e. several times, or more, per minute) will show a noticeable drop in performance. Still, +1. — Solomon Rutzky, Feb 24 '15 at 15:53

Parameter sniffing on table valued parameters

2 Answers2