Difference between Profiler and SSMS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Difference between Profiler and SSMS

Difference between Profiler and SSMS

Rate Topic

Display Mode

Topic Options

Author

Message

jlefebvre-924740

Posted Thursday, October 25, 2012 3:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 6:36 AM
Points: 9, Visits: 69

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

Bhuvnesh

Posted Thursday, October 25, 2012 3:29 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1376865

jlefebvre-924740

Posted Thursday, October 25, 2012 3:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 6:36 AM
Points: 9, Visits: 69

I don't understand, the same SP with with the same parameters timeout in profiler but not in SSMS

Post #1376866

saurabh.x.sinha

Posted Thursday, October 25, 2012 7:18 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, June 16, 2013 11:40 AM
Points: 19, Visits: 90

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

jlefebvre-924740

Posted Thursday, October 25, 2012 7:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 6:36 AM
Points: 9, Visits: 69

yes, it's the same pb

Post #1376987

jlefebvre-924740

Posted Thursday, October 25, 2012 9:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 6:36 AM
Points: 9, Visits: 69

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

saurabh.x.sinha

Posted Friday, October 26, 2012 2:27 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, June 16, 2013 11:40 AM
Points: 19, Visits: 90

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

jlefebvre-924740

Posted Friday, October 26, 2012 2:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 6:36 AM
Points: 9, Visits: 69

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

jlefebvre-924740

Posted Tuesday, October 30, 2012 4:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 6:36 AM
Points: 9, Visits: 69

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 »

Permissions