April 23, 2003 at 8:45 am
Hi Folks
We have a problem with SQL Server hanging when an Update Statement is executed. WE first noticed this when the web app which was executing the SP returned timeout errors. All the select statements in the SP up to the Update statement execute without a problem. When it tries to execute the update, nothing happens. The process thread is active, but no 'activity' seems to happen. If you kill the thread and try to execute a normal select, nothing happens. If you stop and restart the SQL Servvice, select satements work fine again until you try to update something.
We have the lateest patches/hotfixes and are running SQL 2000 Enterprise.
I don't think it is a security or virus, I have full permissions and am doing the checking on the server in QA. I have also run a virus scan, just in case.
No apparent changes have been made to SQL server or the web site, so it is a real mystery why this is happening. Any thoughts would be most welcome.
April 23, 2003 at 8:50 am
If you can run sp_who2 and sp_lock, I would start there. When you run sp_who2 look at the BlkBy column to see what process is blocking the others. Check that process out in the sp_lock results and start your troubleshooting there. BOL has some good information on the results from sp_lock that will help you to analyze the issue.
Hope this helps get you going in the right direction.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 23, 2003 at 3:08 pm
How many records are affected by the update. There are some articles about breaking update in smaller rowcount updates.
Something to the effect:
set rowcount 1000,
While updated_recordcount>0
update table where somecondition
Note assumes with n update previous updated records will not qualify again.
Lastly is it only an update statement or multi statements in a transaction?
April 24, 2003 at 2:36 am
Thanks for your responses.
I did a bit more digging, using the sp_who2 and it is definitely the update that is blocking everything, however, it is only really blocking access to the table it is trying to update. I made the assumption that the whole db was being dragged down. On investigation, I discovered that updating all other tables is fine, it isonly that table. More specifically it is only locks when you try to update one field with a specific value!
Wierd! The field in question is a 3-character field and only contains 3 values:
OPN; CAN or CLS. I didn't design the database and would have used a look up table for these values, holding the foreign key instead of the actual values, but there you go. The system is an issues tracking system and the field in question represents the status of the issue. When the field is updated from OPN or CAN to CLS, it hangs the database. You can insert any other combination of three letters except CLS. There are no constraints palced on this field and neither are there any indexes which enforce a unique combination of fields.
I think that somehow or other, the table has been corrupted. The odd thing is that there are many records containg this value, and this problem really on started happening out of the blue. Once Again, any suggestions would be most welcome.
April 24, 2003 at 4:15 am
Look at the execution plan of the update that is causing the issue. It will of course lock rows/pages or table based on the plan and something may be dragging the Updates performance.
April 24, 2003 at 5:59 am
Why don't you run a profiler trace on the stored procedures that the web app is executing , maybe you will find something there . Also look at your stored proc's if they have the with recompile option enables - if so ...disable it , let sql use the buffer manager to read execution plans .
Did you run dbcc checktable statement to look at the consistancy of the table ???
Also maybe create a nonclustered index on the column . What data type are you using for the column ? Hope I gave you an idea ...
April 25, 2003 at 7:33 pm
Is this update part of a 'bigger' transaction?
Maybe worth while to start at web and work through COM+, if relevant, to SQL checking where every transaction is started/committed.
What is the isolation level the update is running under?
April 28, 2003 at 2:22 am
Hi Folks
Thanks for all your help. Finally figured out the problem. When the relevant field was updated to CLS, it triggered a call to a dll on the server which sent a message to users of another application (bespoke developed). Someone installed a new service pack on the machine for this application and the dll had changed! It now apparently needs a second argument passed to it. The SP process making the call just hanged and blocked any other SPs run against that table. I would like to take the credit for figuring that one out, but unfortunately I can't. It was one of the IT support staff who administer that app (and also did the Service Pack)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply