April 1, 2011 at 6:13 am
Hi,
We have mulitiple hierarchies (1,2,3)in our product for a specific functionality. Same set of stored procedures will run for this specific functionality. If end user run the code for a 1st heirarchy for the first time , performance is good but if he selects the hierarchy 3(will calculate more details) then due to parameter sniffing the performance is degrading.
Because of this I thought of recompiling the storedprocedure.
But I have few questions on this:
Nested Stored procedures are like this:
In SP A there are other SPs like a1,a2,a3
and in a1 there are SPs like b1,b2,b3
and in a2 there are SPs like c1,c2,c3
and in a3 there are SPs like d1,d2,d3
So now if I recompile SP A(parent) ,will the SPs executing inside A (like a1,a2,a3) recompile?
what about SPs inside a1,a2,a3?(like b1,b2,c1,c2,d1,d2)?
Please suggest me on this....
Thanks,
Vamsy
April 4, 2011 at 11:09 pm
Hi all,
Anyone please suggest me on this..
Thanks,
Vamsy
April 5, 2011 at 7:00 am
Hi,
as far as I am informed the "recompile" is not nested, if you recompile the topmost procedure (with sp_recompile or changing the procedure) the nested procedures are not recompiled.
But if you have problems with bad execution plans it may help to optimize the query for specific parameters or use query hints so the sniffing won't mess up with the plans if that is what happens. But be aware that this may reduce the performance dramatically with the parameters that seem to mess up the execution plan atm.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply