This is an alternate piece of code that will match your 2nd attempt

SELECT [IN_1_NOT_IN_2] = s1.SYMBOL
     , [IN_2_NOT_IN_1] = s2.SYMBOL
FROM (
  ...

You can write this as a cross-tab query

SELECT  Q1 = pid
      , Q2 = MAX(IIF(mne = 'Q2', mneval, null))
  ...

This is probably Microsoft pushing back to try and force JEFF to move from datetime to datetime2 😉

It doesnt matter what query you run.  The 16 threads are hitting the same table, joining back to itself 8 times.  There will always be contention.

Bottom line - The more...

If all of the self-joins are needed, some filtered indexes might also help.

As always .... TEST, TEST, TEST

CREATE NONCLUSTERED INDEX ix_Design_Parts_BOMitem
ON design (design, designAlternative, version, class, operation,...

I would try to push my predicates up as high as possible, then isolate my top-level values, before getting the rest of the data.

Something like this

;WITH cteVersion...

If you have multiple threads, the inserting of VALUES could result in duplicates.

I would modify this to use the WHERE NOT EXISTS

IF (@@RWOCOUNT = 0)
BEGIN
  INSERT...

Why not pass your values to a stored procedure.

Then in the stored procedure you can do a proper upsert pattern

-- Inherent in the WHERE clause is the...

Adding to the above, this line in your WHERE clause is basically converting your LEFT JOINS to INNER JOINS

and t3.description = 'faulty device'

Just a thought - SQL 2005 still came bundled with SSMS built in.

So maybe RDP onto the box, and see if SSMS was installed.

Question is a duplicate post

https://www.sqlservercentral.com/forums/topic/query-output-results#post-4453822

A CROSS TAB query will work here.  You just need to know how many sets there are

WITH cteGroup AS (
  SELECT *
    ...

duplicate post deleted

If you convert your UPDATE to a SELECT, you will see that you cannot filter the way that you are trying.  The LAG works within your filtered results.

How to post data/code on a forum to get the best help.