July 6, 2015 at 4:33 pm
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.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
July 6, 2015 at 11:44 pm
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.
Quick question, can you post the actual execution plans for the two different executions of proc C?
😎
July 7, 2015 at 5:16 am
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
July 8, 2015 at 8:22 pm
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))
All I am doing is two update statements in this procedure ... should I still use OPTION RECOMPILE???
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
July 9, 2015 at 12:21 am
a4apple (7/8/2015)
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))
All I am doing is two update statements in this procedure ... should I still use OPTION RECOMPILE???
As opposed to what??
Also, given you are doing long-running updates, have you monitored for blocking? sp_whoisactive is great for that (and lots of other stuff too).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 9, 2015 at 12:26 am
Quick question, any reason for not supplying the information needed to answer the question?
😎
July 9, 2015 at 1:16 am
a4apple (7/8/2015)
All I am doing is two update statements in this procedure ... should I still use OPTION RECOMPILE???
Very hard to say without seeing the proc and the plans. Basically we're guessing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2015 at 5:33 pm
Eirikur Eiriksson (7/9/2015)
Quick question, any reason for not supplying the information needed to answer the question?😎
Sorry I couldn't do it right away because I don't have access to the execution plans on this box. I requested it from a DBA. so I will provide it once I have it, Thanks Eirikur and apologies for the delay....
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply