Oh man, thousands... network connectivity, security, service shut down, changes to connection strings... Those are just the easy ones. It could be client configuration changes... It's really hard to say.

Actually I would have expected that to be a problem in 2000 as well. All 2000 is doing is an implicit convert, that you should get in 2005 as well....

Looks like you either have a connection error, or possibly your DTC process is down:

Microsoft.CommerceServer.Runtime.CommerceException: [highlight=#ffff11]Could not connect to datasource [/highlight]---> System.Runtime.InteropServices.COMException (0x8004D00A): [highlight=#ffff11]New transaction cannot enlist in the specified...

You could run DBCC UPDATEUSAGE. It's possible the allocation information is messed up because of dropped objects, large scale data changes, etc.

I would start on a problem like this down one of two paths. Either I feel like I know the query & indexes well enough that I'm going to look...

First, if you can define them, get foreign key constraints in place. As the last post said, you're looking at a loss of data integrity. That's worse than any performance...

I'd use the method of creating a new copy of the table with all the right structures, migrate the data over, then drop the other table and it's FK's,...

My memories of 6.5 are fuzzy (and painful) but I think you could backup directly to disk even then, but I could be wrong.

The one reason I can think of...

First disable the automatic affinity in the check box below.

Did you run the Database Tuning Advisor against that system? That looks like artifacts from there. Statistics usually have look something like _WA_SYS_0000003_0425A276.

To your first question, neither of those tables looks terribly fragmented. When the logical fragmentation breaks 30%, you might want to consider reorganizing the index. As it gets more fragmented...

Is it possible the statistics are out of date? Maybe it's working harder to gather information on some servers compared to others because of the status of the statistics.

Yep, that was it. I was just curious what your thought processes were. I do agree.

GilaMonster (3/30/2009)

---

You sure? I'm still wincing from the last one I got back. "I think this section needs a little more information" says the note. Net result, I spend most...

Interesting experiment.

The only way I know to affect compile time is to modify the query itself. The more complicated it is, the longer the compile time, the simpler, the quicker....