Thread: strange behavior
11-29-05, 10:45 #1Registered User
- Join Date
- Feb 2003
- Montreal, Canada
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 SP4Steve
11-29-05, 13:55 #2Registered User
- Join Date
- Dec 2002
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?
hmscottHave you hugged your backup today?