I was just able to re-create the execution plan on dev (by recreating all the ten indexes, not just the one index that was used by the execution plan).

The...

Exactly. Let me think whats the better way to get these to you.

For those tables that use index seek plus RID lookup, in the execution plan I captured while the...

I would love to, but the query includes the central production tables. I am not sure from business perspective, I am suppposed to post it. But from what I can...

Thanks, Gail. I am a little confused though, shouldn't the optimizer know that the previous execution plan is less costly than the new one and use the old one instead?

Thank you! I will give it a try.

I would think a replay is like an exact re-doing of what has happened in the time period captured by profiler, with the same connections, same transactions, etc....everything.

Thanks Steve.

If I would run a replay. Which counter should I use to simulate the number of users in production environment, batches/sec in perf mon, or the count of connections...

Thanks for the info.

My only and biggest concern with replay thru profiler is it not like a true replay because it does not simulate however many connections I have...

Thanks Noel, it is all clear now.

Thanks Noel. Very helpful!

More questions:

1. You are right, I just tried to add the new distributor and I got 'The server 'abcd' is already defined as a Distributor. To reconfigure...

Thank you all, very helpful!

Thanks Micheal, it makes sense. I am not sure if we are switching to snapshot isolation now. But a quick fix would be to have a small table that contains...

Thanks Carl for your help!

I might not have presented my case well. I was using nolock so I dont miss the orders that are being modified (I dont use nolock...

Dick, thanks for all your help.

I was able to get the snapshots done. I found the snapshot agent process got a waittype 'sos_scheduler_yield'. I figured the contention is on...

Hi, Noel, thanks for the insight.

I was able to get it done this morning. The CPU was actually ~20%. I temporarily disabled most jobs and log reader and distribution...