CODEWITHSUNDEEP
sql.netdata-access-layersql-execution-planpetapoco
Dotnetsqlcoder
Dotnetsqlcoder

Reputation: 890

Stored procedure query plans with different parameter types

I am using PetaPoco data access layer .
So when calling a stored proc while passing all the values
i get this in SQL profiler 

exec sp_executesql N'EXEC dbo.[sp_GetProducts] @0 , @1',N'@0 int,@1 int',@0=316,@1=5

but when passing NULL the type is not determined and it defaults to nvarchar(4000) like that which works fine 

exec sp_executesql N'EXEC dbo.[sp_GetProducts] @0 , @1',N'@0 int,@1 nvarchar(4000)',@0=316,NULL

My question here is about performance , are we loosing performance because the same stored procedure is having 2 query plans ? or the same plan is being used for both calls ?

Thank you

Upvotes: 2

Views: 880

Answers (1)

William Xifaras
William Xifaras

Reputation: 5312

Yes it is possible there would be performance differences. The same plan may not be used since you are passing different sets of parameters. This is related to the concept of Parameter Sniffing.

If a SQL query has parameters, SQL Server creates an execution plan tailored to them to improve performance, via a process called 'parameter sniffing'. This plan is stored and reused since it is usually the best execution plan. Just occasionally, it isn't, and you can then hit performance problems

There are several ways to deal with this which are highlighted in the link I provided. See section How to Deal With Parameter Sniffing. The potential options are to use the With Recompile option in your stored procedure, or by disabling parameter sniffing by changing how parameter values are used within the stored procedure, or by creating multiple stored procedures.

Upvotes: 1

Related Questions