Stored procedure with table value parameter but ad-hoc?

Question

I have a stored procedure like this:

CREATE PROCEDURE [dbo].[GetInventoryData] 
    @supplierId UNIQUEIDENTIFIER,
    @numbers dbo.ListNumbers READONLY,
    @locations dbo.ListLocations READONLY
AS
BEGIN
    SET NOCOUNT ON; 
    
    SELECT DISTINCT
        i.Field1,
        i.Field2,
        i.Field3,
        i.Field4
    FROM 
        dbo.Inventory AS i WITH (index(idx_Inventory_Abc_Xyz))
    JOIN 
        @numbers o ON i.OemNumber = o.OemNumber
    JOIN 
        @locations AS l ON l.YardLocation = i.YardLocation
    WHERE 
        i.SupplierId = @supplierId
        AND i.PartType <> 'ABC'
        AND i.PartType <> 'XYZ'
END

This is how I call the stored procedure:

DECLARE @p2 dbo.Locations  

INSERT INTO @p2 VALUES (N'AA1')  
INSERT INTO @p2 VALUES (N'AA3')    

DECLARE @p3 dbo.ListNumbers  

INSERT INTO @p3 VALUES (N'631006CA0A')    


EXEC GetInventoryData 
           @supplierId = 'e418fac4-c89e-4f5d-ad7d-ee7fcba7f41f',
           @locations = @p2,
           @numbers = @p3 

The above stored procedure sometime got timeout while almost time it just took < 1s.

I check system and see that compilations/sec is high, and it suggested that ad-hoc queries can be reason.

Then I used this query to list ad-hoc query:

SELECT text, cp.objtype, cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.cacheobjtype = N'Compiled Plan'
  AND cp.objtype IN (N'Adhoc', N'Prepared')
  AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC 
OPTION (RECOMPILE);

!!! Here is sql plan: https://www.brentozar.com/pastetheplan/?id=BkP5cAOW9

My question is why my stored procedure an ad-hoc query? I guess table value parameter cause it. Can someone explain, also give me some idea to fix issue please

Update: added .NET code to call stored procedure

Thank you

Answer 1

The above stored procedure sometime got timeout while almost time it just took <1s.

Because you might encounter parameter sniffing,

values passed into the parameter are evaluated and used to create an execution plan for stored procedure that stored execution plan in the plan cache.

because compiling queries is expensive, SQL Server will re-use them as much as possible.

That will cause the execution plan might not be the best for every query parameters.

There are some ways you can avoid parameter sniffing

1. Add OPTION(RECOMPILE) in the queries or procedure

that way will tell sql-server don't try to keep the execution plan, so you will get a suitable plan from QO every execution time.

CREATE PROCEDURE [dbo].[GetInventoryData] 
    @supplierId UNIQUEIDENTIFIER,
    @numbers dbo.ListNumbers READONLY,
    @locations dbo.ListLocations READONLY
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON; 
    
    SELECT DISTINCT
        i.Field1,
        i.Field2,
        i.Field3,
        i.Field4
    FROM dbo.Inventory AS i with(index(idx_Inventory_Abc_Xyz))
    JOIN @numbers o ON i.OemNumber = o.OemNumber
    JOIN @locations AS l ON l.YardLocation = i.YardLocation
    WHERE i.SupplierId = @supplierId
    AND i.PartType <> 'ABC'
    AND i.PartType <> 'XYZ'
    OPTION(RECOMPILE)
END

But this way might cause your CPU high, if you execute stored procedure many times in short time

2. Add OPTION (OPTIMIZE FOR UNKNOWN) or DECLARE local var to carry parameter.

OPTIMIZE FOR UNKNOWN That will create a plan that it expects to work well for all values of all parameters.

CREATE PROCEDURE [dbo].[GetInventoryData] 
    @supplierId UNIQUEIDENTIFIER,
    @numbers dbo.ListNumbers READONLY,
    @locations dbo.ListLocations READONLY
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON; 
    
    SELECT DISTINCT
        i.Field1,
        i.Field2,
        i.Field3,
        i.Field4
    FROM dbo.Inventory AS i with(index(idx_Inventory_Abc_Xyz))
    JOIN @numbers o ON i.OemNumber = o.OemNumber
    JOIN @locations AS l ON l.YardLocation = i.YardLocation
    WHERE i.SupplierId = @supplierId
    AND i.PartType <> 'ABC'
    AND i.PartType <> 'XYZ'
    OPTION (OPTIMIZE FOR UNKNOWN)
END

parameter-sniffing-in-sql-server