Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2014
    Posts
    2

    Unanswered: Very strange time difference in 2 sprocs run through another sproc.

    Hello. I was lead here with a hint that it is a site that could help me with my problem.
    So to cut a long story short, i would appreciate if you could have a look at the problem i am facing.
    Al the info is here:
    http://www.vbforums.com/showthread.p...-another-sproc
    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Please post your question in this thread.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, I read through the thread on the other forum. If I understand this correctly, you are having a performance problem with running a stored procedure on several remote servers via linked servers. When you do your performance test of the procedure on the remote servers, are you running the procedure while directly connected to the remote server (i.e. via a Management Studio connected to the remote server), or are you running the stored procedure as the call appears in the stored procedure (connected to the local server, and calling [remoteserver].[dbname].[dbo].[procname])?

    Also, what does this procedure do? Is it returning a result set, or making updates on the remote server? If it is returning a large result set, you can be looking at a network latency issue. Use this query to see what the the query is waiting on , and what plan is generated on the remote server looks like. I am willing to bet they will not look the same on all servers:
    Code:
    select getdate(), r.start_time, r.session_id, s.login_name, s.host_name, s.program_name, u.dbname, u.MBUsed, r.command, r.blocking_session_id, r.wait_type, r.wait_resource, r.wait_time, r.last_wait_type, t.text
    , p.query_plan
    from sys.dm_exec_requests r join
    	sys.dm_exec_sessions s on r.session_id = s.session_id join
    	(select session_id, sum(internal_objects_alloc_page_count + user_objects_alloc_page_count)/128 as MBUsed, db_name(database_id) as DBName from sys.dm_db_task_space_usage group by database_id, session_id) u on r.session_id = u.session_id cross apply
    	sys.dm_exec_sql_text (r.sql_handle) t 
    	cross apply sys.dm_exec_query_plan (r.plan_handle) p 
    where r.session_id > 50
      and r.session_id <> @@spid

  4. #4
    Join Date
    Oct 2014
    Posts
    2
    I will have a look at this in a day or 2 since it's our national holiday here.
    The procedure is returning a result set, all is run from the main server, connecting to remote ones.
    Network issues, ok i will try to find anything i can.
    Thank you very much, will post for issues.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •