SQL Server Performance Forum – Threads Archive

Update query seems to hang

I have a rather complex update query that in the past always executed without failure. Now it seems to just hang. The environment is sql server 2000 running on a four processor box with windows server 2003, 8gb of ram, AWE enabled and dynamic memory allocation for sql server from 1.9x gigbytes to 2.9x gigabytes. There doesn’t seem to be any locks being held on any tables involved. Any ideas where I could start checking to look for possible causes? As I said, it has always executed without failure in the past but now for some reason it hangs far more often than not.
Thank you,
Michael

Did you update statistics?
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

I set the sproc to update the statistics on the table but so far it doesn’t seem to make any difference. Also, I noticed from running sp_who2 that the query seems to start with around 18 threads all of which show cpu time greater than zero. After a few minutes, the query is up to around 25 threads, half or so of which show no cpu time, but do show I/O activity. Any idea why so many threads would show no cpu time? Is this significant or not? Is there a trace I can run on this query that would help pinpoint the problem and if so, what items should I monitor in the trace?
Thanks,
Michael

To trace activity run Profiler with Store Procedure event (RPC:Completed, Stmt<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />mtCompleted), and TSQ event (Batch completed). Also run Performance Monitor to see Avg. Disk Queue during SP running. I suggest you to Update Statistics Full Scan, on non production time on that table. (Better on all). Also see how index are fragmented, at least on that table. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell

It appears from running sp_lock that I am getting a huge number of page locks on the table being updated…all have a status of ‘GRANT’. I tried using the WITH(NOLOCK) hint on the table being updated but this does not seem to change anything.

One of the razon (if few time ago all was ok) is index problem or statistics problem, I gess is slow to update and then lock. Try doing suggestion last post.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Thank you, Louis. I should mention that the table being updated is a staging table that is truncated after every data load/transformation is completed. Auto update stats is set to on for the db in question. Should I still update the indexes on the table?

When you set auto update statistics, don’t mean FULLSCAN, means 10% of scan. That’s why I suggesto to Update Statistics on that table with FULLSCAN. About Index, look if they are fragmented, if yes run DBCC RBINDEX for that table.
With profiler and Index Tuning may be you find a better index to implementate. How about Avg. Disk Queue Lenght?
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Average disk queue length is running around .001 with a max of .012 and a minimum of 0.00 – are these good numbers do you think?

More than that, these numbers are wonderfull.!! Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

]]>