It looks like all of the data was read from cache, since you had 203 logical rads and 0 physical reads.

If you do not run CHECKPOINT before running DBCC DROPCLEANBUFFERS,...

UNC path would be preferable.

Where exactly is Z: mapped to? You may not really know unless you log on as the service account.

In an ideal world you should be...

I agree. It is a tautological example.

You would not need an "exists" check if you have a foreign key constraint.

However the part on "not_trusted" is very useful.

And...

I believe, that if ownership chaining is good and the explicit dbo is the same in both databases,

then all that is needed is for the login to be a...

I run most of my maintenance and monitoring from a central server (one id DEV, one in TEST and one in PROD).

I use powershell scripts scheduled via SQL agent jobs.

Most...

What do you see while the backup is running?

Are one or more CPUs pegged?

Do you see disk queue length rising?

Is there an open transaction, holding locks to the table?

Unless you are using credential/proxy to run the SQL agent job step, it will be run

by the service account that runs the SQL Agent service.

So either make sure the service...

What is the datatype of PhoneNumber?

Maybe I am the one confused here.

I am used to adding instances from a slipstreamed install on a cluster.

No, but in order to identify issues related to parallelism going out of whack, it is a place to start.

Also, check the power saving settings on the PROD server.

Try setting MAXDOP to 1-8 on your test server instance and see if that changes things.

Also run some typical queries that are behind some of the slowest actions on each...

Look at the explicit permissions on the Log folder for both instances.

make sure they are the same.

This may have to do with your permissions while you are looking at the...

Is this the log reader agent or the distributor agent? You mention both.

What happens when the agent job restarts?

Do you have the job set to auto restart on...