Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Unanswered: stored procedure hanging problem

    Hello,

    I'm hoping someone could help me with a problem.

    We often run a certain large stored procedure which is basically a select from a number of tables (these tables can be quite large 1million+ rows).

    Usually the stored procedure runs in under 3 seconds, however, on occasion through query analyser we can manage to get the procedure to pretty much time out - there are no other processes running on the database, so why does the process sit there as 'sleeping' rather than finish?

    Has anyone else had a similar problem?

    Any ideas?

    Thanks in advance,

    Stu.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Is this a test database or a production machine?

    Are we certain there is nothing else going on in the database and on the server?

    Have we run a sp_who and sp_lock to see who else and what else might be gumming up the database?

    Nothing else is going on in the Task Manager?

    Have you thought about running a trace with SQL Profiler?

    When it times out, can you run it immediatly afterwards with success?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to check the waittype of the process that runs the proc. Then go from there. Maybe SELECT is not the right query for what you want to do...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    WRT the 3 million rows, size doesn't matter (contribution from my wife). Unless, that's how many rows you're returning to the client.

    If that's the case, please don't tell my wife.

    To prevent rdjabarov from having to explain another one of my oblique posts (hope you're not charging for that, rdj), how many rows are you bringing back to the client? Cause I suspect what rdjabarov appears to be suspecting: you're blocking yourself.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    "Who's there? Are you my concience?" (Dory, Finding Nemo)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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