Auto-id (identity in SQL Server) is not always bad as the clustering key (*), but it's disastrously bad to default to using it as the clustering key.  The clustering key...

A lower-level query (llq) can automatically references all columns from a higher level query (hlq).  This is intentional and is not an error.

For example:

SELECT ...

FROM dbo.hlq

WHERE EXISTS ( SELECT 1...

Why are you exclusively locking the table?  That's almost never necessary, particularly on an INSERT.  That could definitely cause blocking / locking issues.

You can explicitly set DB_CHAINING on (ALTER DATABASE ... SET DB_CHAINING ...) for selected dbs if you prefer.  You might want to consider that in this specific case.

You provided no sample data, so no way to test.  But something like this should help.  If table "accession_2" does not contain duplicate "accession_number"s, you should be able to remove...

So you mean to move data to a new filegroup just that one table?

No, all the (very) large tables.  Until the original filegroup is small.

Because it's LOB data, isn't it's...

SQL could have to rewrite roughly 3.4TB of data (the used space) to shrink that file -- that will take a long time.

Are you using data compression on the tables? ...

(1) force large value types of out row (as above).  Note that for an existing table, you need to run an UPDATE to move the actual column values off page. ...

For efficiency, create a clustering key on the table on ( header1, header2 ).  Since SQL Server so strongly prefers unique indexes, if those values aren't unique by themselves, add...

Would help greatly to see the DDL for the tables, including all indexes.

The query could also be rewritten to help performance, but without sample data there's no way to test...

Ouch.

Based on my past experience, you'll continue to have trouble until you move all non-SQL-Server apps to different boxes.  Not just CPU could be an issue, you could easily run...

My guess would be that the log file needed to grow dynamically, and that is very slow.  That partly would depend on the specific UPDATEs that were done and how...

You didn't provide any sample data so I can't test this at all:

SELECT TP.Location, 
       TE.Name, 
    ...

Your second table, CTR_PanelMembers, is defined as a heap.

Instead, it should have a clustered index on:

( CTRseqNo, RecordID )

I don't see anything wrong with the FK definition in the second...

I assumed that the only value after 'Receiver' was the id.  If more string can follow, the code below will need changed.   I was not able to test the code...