Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Unanswered: Excessive blocking - runnable processes

    Hi there,

    Currently using SQL Server 2000 (SP4). The following condition started occurring last week:

    - Server has excessive blocking
    - Majority of the processes are in runnable state
    - Excessive blocking happens for a few mins. and repeats again during the day. Does not happen at night.
    - Nothing on the server errorlog, profiler
    - CPU averages 40 - 50% at that point of excessive blocking

    Any help would be greatly appreciated.

    Thanks.

  2. #2
    Join Date
    Dec 2004
    Posts
    46

    Excessive blocking - runnable processes

    Did the blocking happen with the user tables or in tempdb?

  3. #3
    Join Date
    Oct 2003
    Posts
    20
    It happens on the user tables.... Thanks.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is probably a fault of the database application, not the server. Poorly designed schemas and bad code are the primary culprits of blocking, which may not occur until the application use scales beyond that which it was tested.

    Try to figure out which sprocs or statements are causing the blocking, and then try to optimize them.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2004
    Posts
    46
    Since your job actually finishes, and it did not happened at night. My guess would be:
    1. Some process running long update transaction, or
    2.

  6. #6
    Join Date
    Dec 2004
    Posts
    46
    2. Your IO time slows down due to workload, that may increases chance of blocking too.

  7. #7
    Join Date
    Dec 2004
    Posts
    46
    If you cannot spot abnormalties in profiler, have a look at IO time.

  8. #8
    Join Date
    Oct 2003
    Posts
    20
    Thanks for all your replies. It was pointing to an application issue. Thanks again.

Posting Permissions

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