I have a SP that executes in 5 seconds through SSMS
When that same SP is executed through a LINQ-to-SQL excel add-in it times out after 30 seconds (simpler queries for that same SP take a long time but return results)
I then changed the SP so that it reassigns all the input parameters to new local parameters inside the SP. That made the SP run in 36seconds in SSMS (so there is the reason why SSMS was so fast to begin with)
So I'm guessing that SQL server isn't making use of parameter sniffing for my LINQ-to-SQL queries?
So, my question is, is there any way to make the SP as fast in LINQ-to-SQL as it is in SSMS (with it's parameter sniffing)
SQL Server optimizes stored procedures the same way whether you call them from SSMS or from LINQ. But it does use plan caching. A plan is stored for later reuse with the same login + ansi settings. The first values passed in can determine how the plan looks. If a different login/settings starts with different values, that can result in a different cached plan. That's one explanation for performance differences between LINQ and SSMS.
To reset all cached plans, use:
DBCC FREEPROCCACHE
In order to have the SP optimized for exactly the values you're calling with, you could use with recompile:
create procedure dbo.MySP with recompile as ...
This causes the procedure to be compiled for every invocation. This would negate parameterization.
(Your situation is rather unusual. SQL Server has an option to force parameterization, but there's no option to prevent it.)
