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...

First thing I'd do is figure out what that latch is.  Quick google brought me to this:

https://www.sqlskills.com/help/latches/dbcc_check_aggregate/

which states:

This latch class is when a thread is waiting for access to the...

Think the easiest way will be to break out what each REGEX expression you indicated means and build up case statements that reflect this.  I am just going to help...

My opinion, anything labeled "redistributable" is safe to leave as is.

What I would look for is "what was installed at the same time as SQL Server?".  In your screenshots, you...

I would load up extended events or profiler to see what is happening on the database side.  What queries are being sent during the time the application is connecting to...

Can you provide some DDL and a script we can run to reproduce your original results?

If not, one approach would be to do a self join on firstname, lastname, and...

That is a new one for me.  If it is the exact same script, I would expect it to have the exact same results each time.

Any chance anything else has...

My guess (could be way out to lunch) is that your scripts are slightly different for the restore.  What I mean is if you drop the database and recreate it,...