ALZDBA,

What I meant with keeping prod relationships was, for reporting purposes, it's not always the best/fastest way to get at the data. We pre-aggregate some data on the way to...

You didn't say it was a reporting db -- which might have led to the question, Is it necessary to maintain the production relationships in the first place?

PKs/FKs giving you grief? Seems like a fair trade-off for being able to have faith in one's data...and what's a database without meaning?

As another poster put it above, there's a...

In response to questions above,

Data comes in nightly, and occasionally on weekends. The distribution of incoming data over a week would be roughly 22% on Mondays through 18% Fridays,...

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.