I'm with everyone else, get back to the old cardinality estimation engine for a short time. Turn on Query Store on your database. Run your load for a bit to...

Sounds like blocking or resource contention. Take a look at the system_health Extended Event session. Excessive waits and blocks are automatically captured there. It should give you a lot more...

Monitoring Query Store through Extended Events is a good way to keep an eye on how it's behaving. For a whole bunch of info on Query Store, I'd like...

In this modern age, don't forget that you don't have to download and install SQL Server. You can pretty quickly spin up containers with SQL Server ready to play with....

Film school drop out with a 30+ year career in IT.

Gather experience and knowledge, not pieces of paper. Very early in your career, the paper can help getting the first...

Latency is a dependency of the volume and distance. There's not a hard and fast measure here. It depends on too many factors. However, standard Availability Groups, not hopping across...

You would be so much better served listening to Jeff and skipping the rebuilding of indexes. Maintaining statistics on indexes is vital, but defragmenting indexes is an idea that has...

I absolutely love Extended Events.

However, for SQL Server 2008R2, I don't recommend you use them. They were a 1.0 release then and the functionality just wasn't quite up to snuff....

You could look at transactional replication. Since you're not talking about multi-way replication, you shouldn't need to modify the existing data structure to make it work. You'd only need to...

A manual script shouldn't take hours for that amount of data. I'd investigate exactly how it's doing what it's doing.

Regardless, another vote for Data Masker. Although I do work...

Availability groups should, under most circumstances, operate within 20 seconds. It's that or transactional replication. No other choices at all.

It really does depend on the two tools we're talking about and why you're using two.

Now, take something like Microsoft System Center as a monitoring tool. It does the bare...

A key lookup occurs when the cardinality estimator does not have a better index than the primary key to use in the query plan.

The solution to this is to...

When handed a system, before anything else, backups. I don't care how fast or slow the system is, if the backups aren't good, all bets are off.

Once that's in place,...

I'll be honest, I'm not even going to try to propose a solution to the query. Sorry.

Instead, let me ask a question. Have you looked at using a columnstore index?...