April 18, 2013 at 3:10 pm
Hello everyone.
Got a situation that I can hope I can explain.
We have an application that at the moment is performing a lot faster from the application front end, than it is when the same SP is run from SSMS or SQLCMD.
Is that possible ?
The apps team tell me that the application function when completed does complete successfully (My first check , was does it really do what they think it does)
when I extract a trace from a successful run , i see the SP and parameters being called.
When I try to run that in SSMS it runs and has to be killed off.
A successful run from application <> 3-5 seconds.
SSMS killed off after 10 mins.
What can I check and confirm or deny in this scenario.
All seems very odd to me ?:w00t:
cheers for any advice or tips.
April 18, 2013 at 3:18 pm
Have you tried executing the proc from SSMS, but run as the user that the application connects to the db with?
Maybe the app doesn't have access to something and an error is being masked?
What does the proc do?
What is it getting stuck on when you run it locally?
April 18, 2013 at 3:23 pm
That was one of my first thoughts that the app is not doing what they think it is.
SP takes a value then updates it , then returns that as a new value.
Is there anything in the query cache that I need to look into ?
If the SP has been run multiple times in SSMS , would the cache have any impact ?
April 18, 2013 at 3:27 pm
please check the connection property in trace for the client application. what is isolation level set there.
what isolation level are you using when running from QA. If application uses read uncommitted it will work even if there are open transaction while it will get blocked in QA. Check for open transaction / blockings.
Other possibility.. are you using the same object? is your schema and application's account schema the same?
Seraj Alam
April 18, 2013 at 3:38 pm
Maybe parameter sniffing?
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply