March 9, 2011 at 4:57 pm
Hello,
A single stmt stored proc (the stmt is basically a view from many tables) takes about 11-12 sec. from an app. However, it runs instantaneously in SSMS.
Any ideas as to why?
thanks.
March 9, 2011 at 5:09 pm
That is not a lot of information to provide for a performance problem...there could be a lot of reasons for this type of scenario to arise.
- Are you running it with the same input parameters from both?
- How much data does the procedure return?
- Is the application server very busy and/or have insufficient hardware and network resources?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 9, 2011 at 5:23 pm
Sorry, but:
1. Running with exact same parms
2. Server not too busy
3. Same result set -- About 10 rows
March 9, 2011 at 5:31 pm
Check the connectoin settings, specifically the ANSI settings. When these are different, you will get different execution plans for the same query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2011 at 5:44 pm
ok.
Is there a way I can see these settings via Profiler?
thanks
March 9, 2011 at 6:01 pm
I do see a different exec plan for when the app runs, and when I run it in SSMS!
So I guess as you have indicated, the client is probably affecting the plan....
anyway I can see it via profiler on how the client app is setting it?
March 9, 2011 at 6:44 pm
You can see the ansi settings by querying sys.dm_exec_sessions.
March 9, 2011 at 7:32 pm
Or you can get the properties form the Connection event in a trace, even from the Existing Connection event. They show in the text column. You can also get them from the execution plan events in trace. They'll show in the root operator (Select or whatever). You can also see them in plans you pull from the plan cache through DMVs
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2011 at 4:38 am
mymail.default (3/9/2011)
Hello,A single stmt stored proc (the stmt is basically a view from many tables) takes about 11-12 sec. from an app. However, it runs instantaneously in SSMS.
Any ideas as to why?
thanks.
Check with your team.Also check the N/W card.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 10, 2011 at 5:02 am
Hi,
check your request waiting by quering following.
select session_id,start_time,status,command,DB_NAME(database_id) As dbName
,wait_type,wait_time,t.text
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text (r.sql_handle) t
where r.session_id >51
order by r.wait_time desc
Ram
MSSQL DBA
March 10, 2011 at 5:55 am
muthukkumaran (3/10/2011)
mymail.default (3/9/2011)
Hello,A single stmt stored proc (the stmt is basically a view from many tables) takes about 11-12 sec. from an app. However, it runs instantaneously in SSMS.
Any ideas as to why?
thanks.
Check with your team.Also check the N/W card.
Just curious, why would the network card result in different execution plans?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2011 at 8:14 am
Grant,
Oh... i didn't read it fullly.
---
You changed your picture 🙂
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 10, 2011 at 12:19 pm
ok, thanks...
I am trying to capture the eventclass: ExistingConnection, to get the connection settings -- but, when I turn on the profiler on, I see the first few entries for the settings, however, when I run the actual client app. I do not see any existingConnection event class in the profiler...
aren't I supposed to see a new ExistingConnection event class when the client app starts to run?
please help 🙂
thanks
March 10, 2011 at 5:56 pm
No, you won't see an ExistingConnection event for connections that are not yet existing. Also, don't run the Profiler GUI against your production system. While trace events are pretty safe, the GUI consumes them in a way that isn't.
You want to look for the event Audit Connection.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2011 at 6:41 pm
ok. I assume this to be the "Audit Login" event.
Thanks for all ur help.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply