Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Difference between Profiler and SSMS Expand / Collapse
Author
Message
Posted Thursday, October 25, 2012 3:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:13 AM
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
Post #1376862
Posted Thursday, October 25, 2012 3:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:11 AM
Points: 2,840, Visits: 3,976
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
Post #1376865
Posted Thursday, October 25, 2012 3:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:13 AM
Points: 9, Visits: 85
I don't understand, the same SP with with the same parameters timeout in profiler but not in SSMS
Post #1376866
Posted Thursday, October 25, 2012 7:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 11:14 AM
Points: 19, Visits: 162
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 ??
Post #1376973
Posted Thursday, October 25, 2012 7:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:13 AM
Points: 9, Visits: 85
yes, it's the same pb
Post #1376987
Posted Thursday, October 25, 2012 9:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:13 AM
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
Post #1377101
Posted Friday, October 26, 2012 2:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 11:14 AM
Points: 19, Visits: 162
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
Post #1377434
Posted Friday, October 26, 2012 2:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:13 AM
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
Post #1377439
Posted Tuesday, October 30, 2012 4:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:13 AM
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
Post #1378656
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse