October 25, 2012 at 3:24 am
Hello, I run a profiler trace to identify bootleneks.
I found a stored procedure which have many reads, but when I run it with SSMS, I don't have any reads.
this SP timeout in my application (more than 30 seconds) but not in SSMS (2 or 3 seconds)
any ideas?
regards
October 25, 2012 at 3:29 am
jlefebvre-924740 (10/25/2012)
this SP timeout in my application but not in SSMS
because we set the "commandtimeout setting in application" which tends to do the time out of any SP when SP exceeds that time limit during its execution.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 25, 2012 at 3:32 am
I don't understand, the same SP with with the same parameters timeout in profiler but not in SSMS
October 25, 2012 at 7:18 am
can you execute query from ssms
1. from ssms on your machine
2. from ssms on database server
3. from ssms on application server
Is the output coming in same time ??
October 25, 2012 at 7:38 am
yes, it's the same pb
October 25, 2012 at 9:59 am
1) drop and recreate SP => dont' work
2) clear all cache for this SP => don't work
3) restart SQL => don't work
4) created a new SP with a different name but the same code => it's work
a strange thing :
the SQL login is in french, with this login in SSMS, it's ok
the connexion string in application have "current language=french"=> not ok
set the connexion string in application to "current language=english"=> ok (but I can't change the connexion string definitely)
change SQL login to english and connexion string "current language=french" => not ok
I don't understand
October 26, 2012 at 2:27 am
It seems there is no issues with SP, the issue is with application code which may cause lock or deadlocks . Can you try using profiler with lock events and enable specially deadlock and lock time out.
Also check wats the connection time out value from your application
October 26, 2012 at 2:59 am
there are no locks or deadlocks, application timeout is set to 30 seconds
in SSMS the SP run in 1s
one more time, when I set application connexion string to "current language=english" instead of French, it's ok
October 30, 2012 at 4:17 am
I found the pb
by using SET arithabort off, I reproduce the low query
in my SP, I use option (keepfixed plan)
by turn off this option, my SP is faster
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply