Personally, this sounds like a good fit for an in-place upgrade then.  You get to skip over the overhead needed for a migration install and you sound like you have...

The log tells you what to do next:

Next step for SQLEngine: Use the following information to resolve the error, uninstall the feature, and then run the installation process again.

Next step...

One big reason to do a migration method is that you have a quick and easy rollback method.

One big reason to do an in-place upgrade is no duplication of data.

There...

Just a quick thing from my eyeballing on it - it doesn't appear to me that the query you provided gives the results you indicated.

@result doesn't contain the "AS" keyword,...

Have you checked long running queries and/or transactions?  Could be someone took out a transaction and never committed it.  That'll make the log grow.

What about the PHP errors?  You are seeing a jump to 1500 errors in that window when the CPU is spiking.

The NGINX error of 502 is "bad gateway".  So it...

is the column DBSource.dbo.Contacts.ContactID unique?  If it is NOT unique on the source, you could have problems putting it into the destination where it expects it to be unique.

Heh, Scott...

• This reply was modified 4 years, 10 months ago by Mr. Brian Gale.

Do you have an execution plan for this?

Do you have indexes on any of the objects?

Are you getting "SEEKS" or "SCANS" on the tables?

How large is the final data set...

Looking at the attached images, I would say something is "happening" on the server at that time.  Possibly a scheduled thing, possibly a malicious thing, possibly an automated thing.

I would...

I like how the notes for the wrong answer say to use a different method due to deprecated features. BUT the notes for the correct answer method say that in...

What I would try first is run each bit of your code in chunks rather than the whole thing at once.

Mind you, since you indicated that the problem is on...

I have not used Tableau, but I THINK the tool is designed to handle large data sets and process them to display to the end user.  So I would pull...

I think that is expected for some queries.  If the query has no need to go parallel or can't go parallel, then you may get a single core spiking like...

Since you are using SQL Server 2019, would "string_split()" function suit your needs?

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

To add to what Scott said, I would expect that the plans would be different.  One is using a JOIN and one is not.  If you replace the CTE with...