March 23, 2012 at 7:38 am
Hi All,
We have a procedure set WITH RECOMPILE that returns some data. When called from the UI it takes more than 30 sec to return data, but when I capture the parameters with a profiler trace and run it from SSMS, the results come back in 1 sec. Does anyone know what the issue could be?
Removing WITH RECOMPILE did not help.
Thanks
March 23, 2012 at 7:57 am
Network?
Bad code?
Could be anything...
Have you tried DBCC FREEPROCCACHE ?
March 23, 2012 at 8:30 am
Eugene Elutin (3/23/2012)
Network?Bad code?
Could be anything...
Have you tried DBCC FREEPROCCACHE ?
No network issues. The code is making a call to that specific proc in the same fashion it does for all of our "good" performing procs.
Does "WITH RECOMPILE" act differently than DBCC FREEPROCCACHE (sql handle for the proc)?
What is puzzling is that using SSMS and running the same proc with the same parameters does not produce the same behavior.
March 23, 2012 at 8:45 am
When you state 30sec in UI vs 1sec in SSMS, are you talking about complete resultset returned and displayed in both cases or, in case of SSMS you can see some data returned in 1sec?
Can you post stored proc DDL?
March 23, 2012 at 9:27 am
you may want to check if there's a different execution plan used when the proc is called from management studio vs. the application. One of the culprits to check that happens alot is the connection's setting of ARITHABORT. SSMS defaults to ARITHABORT ON, but .Net connections default to ARITHABORT OFF.
March 23, 2012 at 9:36 am
One of the culprits to check that happens alot is the connection's setting of ARITHABORT. SSMS defaults to ARITHABORT ON, but .Net connections default to ARITHABORT OFF.
... meaning that you will have two different execution plans for the same procedure.
March 23, 2012 at 10:37 am
More about it here: http://www.sommarskog.se/query-plan-mysteries.html
March 23, 2012 at 11:50 am
Eugene Elutin (3/23/2012)
When you state 30sec in UI vs 1sec in SSMS, are you talking about complete resultset returned and displayed in both cases or, in case of SSMS you can see some data returned in 1sec?Can you post stored proc DDL?
1 sec for all results to come back. sorry can't post the DDL.
March 23, 2012 at 11:54 am
Chris Harshman (3/23/2012)
you may want to check if there's a different execution plan used when the proc is called from management studio vs. the application. One of the culprits to check that happens alot is the connection's setting of ARITHABORT. SSMS defaults to ARITHABORT ON, but .Net connections default to ARITHABORT OFF.
Chris,
How does one check the execution plan from a .Net application?
I understand that ARITHABORT deals with overflow or divide-by-zero errors, which can't be the case in my situation.
March 23, 2012 at 12:22 pm
Lexa (3/23/2012)
Chris Harshman (3/23/2012)
you may want to check if there's a different execution plan used when the proc is called from management studio vs. the application. One of the culprits to check that happens alot is the connection's setting of ARITHABORT. SSMS defaults to ARITHABORT ON, but .Net connections default to ARITHABORT OFF.Chris,
How does one check the execution plan from a .Net application?
I understand that ARITHABORT deals with overflow or divide-by-zero errors, which can't be the case in my situation.
You will need to run a server side trace to capture this information.
Also, it isn't if you have to worry about overflow or divide-by-zero errors, just a difference in the setting could cause a difference in execution.
March 26, 2012 at 10:00 pm
DiegoPeña (3/23/2012)
Great article, thanks.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply