I’ve been an on-again-off-again DBA for over 15 years now but still get caught out by stupid little SQL Server behaviors! Today I was looking into why a perfectly good SQL statement that in testing hits an index perfectly and runs in milliseconds takes many seconds in Production when run as a stored procedure. The execution plans are completely different between the stored procedure and the raw SQL and it turns out this is just a failing of parameter sniffing. Parameter sniffing is perfectly normal and required as SQL Server has to decide on an execution plan and tends to use the first parameter submitted to the stored procedure as it’s basis for the query. It then caches this plan and uses the same access method each time the proc runs. That’s great if all parameters have similar results and it gets the plan right first time. Unfortunately in my case it had got it all wrong and wouldn’t give up!
To trick the procedure to emulate the plan used in a simple query through SSMS, I ended up just copying the parameter. Sounds stupid but works a treat.
Instead of
create sp_dummystoredproc @param1 int
as
select * from table where col1 = @param1use
create sp_dummystoredproc @param1 int
as
declare @dummyparam1 int = @param1
select * from table where col1 = @param1It’s not a particularly costly fudge so it’ll do as it solves the problem and makes the query use the correct indexes.