Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Unanswered: strange behavior

    I have a SP that usually works fine (0-16 CPU time, 40 ms Duration), but from time to time the server hangs with apparently no reason. The SP has a lock timeout set to 500, so it should abort if a lock timeout error (1222) occurs but it doesn't. The Profiler reports very long execution time (over 30 sec), and because of that all other SP calls are blocked, 'cause the transaction opened by the first sp execution is not finished yet.
    Any other attempts to identify other blocking queries did not show me anything suspect (sp_lock, dbcc opentran) other then the usual blocked chain. I'm starting to think about an IO bottleneck, or IO failure, that could block the disk access and cause the delay. The status of RAID 5 is healthy.

    The server is used as storage system for a website (approx. 2000 concurrent users), and occasionally I noticed an ASP queue, but this strange behavior occurs even during the peak-off hours.


    Any thoughts ?
    -----
    HP Server - 2 CPU @ 3,4 ; 4 GB RAM; SCSI - RAID 5
    Windows 2000 Advanced Server - SQL Server 2000 SP4
    Steve

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Can you post the DDL for the sproc, please? Might also be helpful to post the DDL for any tables that are touched by the sproc.

    Have you checked for locks during the periods when the sproc runs badly? [edit: my bad; didn't read your first post entirely].

    Something else to check: is your database set to auto grow and is it growing when the sproc behaves badly?

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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