Process Explorer (sysinternals) or their simpler tool "handle"

It could have been something as simple as an antivirus grabbing the files. It could have been that the cluster service noticed the...

You checked what process was using the files?

SELECT, INSERT, UPDATE, DELETE are definitely not always single threaded. They can all run...

You said the files wouldn't copy, why wouldn't they copy?

Err, what????

That now doesn't match the query you posted (which returns only two columns) and is the result that you're asking for. If you already have that result, why are...

What are the values for a.evs_CodeUsed in the table?

Table definitions (as CREATE TABLE) and some sample data (as INSERT statements) please, if you want a tested answer.

Initial guess, your ROW_NUMBER needs a Partition By

Paul Brewer (12/29/2014)

---

This might be a good way of comparing 'create table' differences between table variables/temporary tables:

http://www.sqlskills.com/blogs/paul/determine-causes-particular-wait-type/

It describes a way of defining an extending events session to capture the internal...

Paul Brewer (12/29/2014)

---

I'm not 100% sure to be honest, no. From experience the latch contention associated with table variables 'seems' a lot less profound but I've never investigated or tested...

Are you sure about...

Is it? 😉

It's surprisingly easy to have SQL tip to a table/index scan when it's using a non-covering...

Yes, it can.

http://technet.microsoft.com/en-us/library/ms190805%28v=sql.105%29.aspx

Missing one or more rows that were not the target of update

When you are...

Yes, and?

I've read that read committed snapshot uses pessimistic writes, and snapshot uses...

NOLOCK is not a performance tuning...

Depends. If you explicitly request an isolation level, you get whichever one you request. If you allow the default of read committed, then you get read committed using row versions.