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

Why did you need to use index hint for idx_Inventory_Abc_Xyz index? — D-Shih
– D-Shih, Commented Mar 10, 2022 at 2:19
Its pretty rare that you can pick a better index than SQL Server. — Dale K
– Dale K, Commented Mar 10, 2022 at 2:23
The thing about investigating performance issues is to simplify the factors as much as possible... — Dale K
– Dale K, Commented Mar 10, 2022 at 2:31
For heavy use of differently sized Table Valued Parameters it might be worth switching on Trace Flag 2453, see brentozar.com/archive/2017/02/…. If you are calling the procedure from a client app then do not use the batch at the top (with the DECLARE... INSERT) instead use the client driver's tools to pass a Table Valued Parameter directly — Charlieface
– Charlieface, Commented Mar 10, 2022 at 12:22
You will find that it doesn't actually generate that code, it is merely a represntation generated by the SQL Profiler of what the inserts might look like if they were T-SQL code. It's actually a high-performance BULK INSERT style codepath sent directly over the TDS protocol, and the recompilation count is actually a artifact that can be safely ignored. See also sqlskills.com/blogs/bobb/tvps-and-plan-compilation-the-reprise and stackoverflow.com/a/56744520/14868997 — Charlieface
– Charlieface, Commented Mar 10, 2022 at 13:48

D-Shih · Accepted Answer · 2022-03-10 05:08:07Z

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

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

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

edited Mar 10, 2022 at 5:08

answered Mar 10, 2022 at 5:01

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

minhhungit Over a year ago

I actually tried OPTION(RECOMPILE) long time ago, I also split stored proc to small stored proceduces to avoid parameter sniffing. My stored proc is called many many times all of day. Base on posts: brentozar.com/askbrent/compilations and sqlskills.com/blogs/bobb/tvps-and-plan-compilation-the-reprise ..... I think it's mechanism of sql server. I'm trying to turn on "optimize for ad hoc workloads" btw.

minhhungit Over a year ago

one more note, I also tried to using brentozar scripts github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit but don't see it warning "parameter sniffing"

D-Shih Over a year ago

If your SQL server support QueyStore I would recommend you use that to analytics. execute plan from your SP learn.microsoft.com/en-us/sql/relational-databases/performance/…

D-Shih Over a year ago

If not you can try to use who is active to collect what's different between slow query and normal query plan, we can discuss it by those information whoisactive.com

Collectives™ on Stack Overflow

Stored procedure with table value parameter but ad-hoc?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related