a4apple (7/6/2015)
Hello everyone,I have a stored procedure which is nested. I have local variables defined to avoid parameter sniffing in both the procedures too.
Proc A calls PROC B, PROC C, PROC D.
PROC C WHEN ran Individually takes a time of 20 seconds.
When PROC A is called, PROC C takes 86 seconds. Data is the same.
Any ideas what can cause this? tried to recompile the proc which didn't give me any results too... Thanks for your time.
1) local variables just shift parameter sniffing to a different form of problem
2) remove local variables and use the sproc parameters and then try option recompile (preferably on just the required statement(s))
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service