Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696

    Unanswered: Slow sproc Execution

    Scenario 1: Sproc executed on local server against local tables that took 40 seconds to run, now takes 30 minutes to run.
    - No blocking locks
    - Sometimes "NOP" in command when sp_who2 is run.
    - perfmon shows nothing out of the ordinary when looking at server
    resources. (memory, processors, etc.) there have been NO configuration
    changes.
    - Occaisional lost packets (every 10th) with ping -t
    - I flushed the procedure cache, and rebooted the server.


    Scenario 2: Sproc executed on another server accesses tables on Scenario 1 local server via server link, runs with no problems in 30 seconds.


    SQL Server 2000 SP3a.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Is there a question here?

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Just forget it, I don't know when this site became a magnet for pretentious assholes, ban me, do whatever you want, but I'm done here.

  4. #4
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    There are lot many PMASchmed, who are trying to spoil the image of the site..............




    Thanks,
    Rahul Jha

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Have you done a profiler trace? can you lock down on the query that is taking longer to execute ... i would ask you to do a side by side comparison of the query plans of the queries (scenario 1 vs Scenario 2 ) where the time difference is high between the same queries
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by PMASchmed
    Just forget it, I don't know when this site became a magnet for pretentious assholes, ban me, do whatever you want, but I'm done here.

    Wow, talk about thin skinned

    I would say that a sproc that ran in 30 seconds was a bad performer to begin with
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Turns out it was a bad switch, performance issue was going across a server link.

    Sorry, I was a bit thin skinned yesterday, a lot of issues. I suspected a network issue as the original culprit, but the infrastructure guys were denying it, turns out it was.

    Also, another problem is the developers refuse to use Openquery for server link queries.
    Last edited by PMASchmed; 10-30-07 at 11:54.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I know the feeling well. When you are under the gun you sometimes make bad choices (such as the post without a direct question), then when you get the (predictable) smart-alec answer it really hurts... Been there, done that, still have the blood-stained tee-shirt to prove it!

    -PatP

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just curious, can you post the sproc code?

    Is it still referencing a linked server?

    That can never deliver good performance if it is I believe
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Brett Kaiser
    Just curious, can you post the sproc code?

    Is it still referencing a linked server?

    That can never deliver good performance if it is I believe
    I plan on assisting the developer with the code changes, but right now our operations guys don't mind a 30-50 sec exec time, but we will make it better.

  11. #11
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by PMASchmed
    Just forget it, I don't know when this site became a magnet for pretentious assholes, ban me, do whatever you want, but I'm done here.
    Well, glad you didn't quit the forum and sorry I didn't check back for your response sooner. I don't apologize for being an asshole though because it produces enough positive results for all involved to justify it. But, at the same time, I nev^H^H^H try not to hold it against people when they yell at me or make me look like an idiot in response.

Posting Permissions

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