Dave,

Thanks for the input, I'll get back to you next week on this, and have answers to your questions.

P

Agreed. It was a no-brainer to switch to SQL2K from 6.5.

Apart from the fact that our department mandates that we use supported software on hardware under warranty, I could not...

I don't know if this is the ravings of a paranoiac, but it explains quite a bit:

http://blog.wired.com/business/2007/03/enough_about_me.html

Hmm, DBREINDEX has not changed anything.

Interesting: the app calls a stored proc. The sproc took 40 seconds to run from the app, I cut and pasted the call from Profiler,...

Saw that, I've been following...Seems that should have worked.

OK, well it might seem like overkill, but could you get those into a @tmp_table, then do the update, ideally based on the Updated table's PK?

How many rows are being affected?

Could #temp/@temp tables help?

Have you put the traces on?

DBCC TRACEON (1204, -1)

DBCC TRACEON (3605, -1)

The -1 writes the output to the SQL Server error logs. From there, you should be able to...

30,000 tables??? WOW! How on earth does one know what each is for?

One would need a metadata-database just to begin to suspect where to look for anything...

Nope, it's had a long run of successful completions.

Using DTS Transfer DB task.

Yes, separate server, no regular restarts.

I will try one of the suggestions above, rebuilding the indexes, rather than...

I don't see how that would explain the observed behaviour: slows right down on Monday and Tuesday, but not on Wednesday - Friday?

It's the same processes running every night.

Who said anything about optimizing? I described intermittent behaviour...

Not sure if any of what follows explains what you're seeing:

Have you looked at the execution plans on each of the boxes? Since prod is timing out, can you try...

I installed SQL2K on Win2K3 Server SP1 last week, which also had SQL2K5 on it.

Prior to install, I got the message that SQL2K SP2 AND below were not supported, please...

The other thing is, the function which runs against every row in a query amounts to row by row processing, something that could only be accomplished with a while loop...