SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Difference between Profiler and SSMS


Difference between Profiler and SSMS

Author
Message
jlefebvre-924740
jlefebvre-924740
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 85
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
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5346 Visits: 4076
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;-)
jlefebvre-924740
jlefebvre-924740
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 85
I don't understand, the same SP with with the same parameters timeout in profiler but not in SSMS
saurabh.x.sinha
saurabh.x.sinha
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 212
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 ??
jlefebvre-924740
jlefebvre-924740
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 85
yes, it's the same pb
jlefebvre-924740
jlefebvre-924740
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 85
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
saurabh.x.sinha
saurabh.x.sinha
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 212
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
jlefebvre-924740
jlefebvre-924740
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 85
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
jlefebvre-924740
jlefebvre-924740
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 85
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search