Post reply

SP takes about 11 sec inside app. -- But instantly in SQL Mgmt Studio

  • March 9, 2011 at 4:57 pm

    #233815

    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

    #1296857

    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

    #1296859

    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

    #1296862

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 9, 2011 at 5:44 pm

    #1296864

    ok.

    Is there a way I can see these settings via Profiler?

    thanks

  • March 9, 2011 at 6:01 pm

    #1296867

    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

    #1296870

    You can see the ansi settings by querying sys.dm_exec_sessions.

  • March 9, 2011 at 7:32 pm

    #1296872

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 10, 2011 at 4:38 am

    #1296991

    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

    #1296996

    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

    #1297024

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 10, 2011 at 8:14 am

    #1297118

    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

    #1297309

    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

    #1297409

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 10, 2011 at 6:41 pm

    #1297411

    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