CODEWITHSUNDEEP
sql-servert-sqlparameter-sniffing
Umair
Umair

Reputation: 3253

Table valued parameter "sniffing"

I have a stored procedure (SP) in which a table-valued parameter (TVP) is being passed in. The same code in the SP executes a lot slowly than it does outside the SP.

I took a look at the execution plans and they are very different.

At first this seemed like a sign of parameter sniffing, however this is for a TVP! Which works a bit differently (I am not too certain - apparently there is no sniffing for TVP's?).

In any case, if I create a new local TVP and insert the rows into it, then I get a good execution plan!

CREATE PROCEDURE [dbo].[TVPSniffTest] (
    @GuidList dbo.Guid_LIST readonly
)
AS
BEGIN 
    DECLARE @GuidList2 dbo.Guid_LIST
    INSERT INTO @GuidList2 
    SELECT * FROM @GuidList

   --query code here using @GuidList2, produces a good plan!
END

What is going on?

Edit I've tried a number of query optimizer hints, they do not work. Including the one in the suggested duplicate question. It's almost like the bad plan (slow one), is the one that is correct in terms of the estimated number of rows. The fast plan has an incorrect estimation of the number of rows.

Upvotes: 3

Views: 421

Answers (1)

Nickolay
Nickolay

Reputation: 32073

TVPs don't have distribution statistics, but they do have cardinality information.

Parameter sniffing applies to table-valued parameters: the optimizer might reuse a plan compiled for a low-cardinality TVP in a following invocation of the SP on a TVP with many rows.

Sources:

Upvotes: 2

Related Questions