Additionally, I am still strongly thinking about adding more files to TEMPDB. Because all the blockees choke on the tempdb latches.

Well. I am pretty sure that it is not due to recompile because it recompiles every time anyway as an old-asp-concatenated sql statement. But also I went the MAXDOP reducing...

Reducing MaxDop helped, actually. elapsed time reduced from seconds to milliseconds now.

Pre-creating temp tables did not change metrics but i understand what you all said regarding that it is the...

I can read plans, to an extent. I just dont know how to attach them here :).

code rewrite is the very last resort (asp pages,....re-testing.. deployment/release...--noone wants do all that...

physical cpu = 24, logical 48.

We have 12 files for tempdb.

I am also thinking to reduce MAXDOP for that query only from 8 (server wide setting) to 4 or 2, too. Considering that LATCH contention...

Thank you very much for the valuable feedback. So does my suggestion to add more data files to TempDb sound reasonable then?

That's what I am doing right now, yes.

And thank you, let me make s strong case for Redgate  SQL Search with my mgmt ...

No source control.... It's a super secret, super ad hoc server...

Any non 3rd party suggestions?

Other than VIEW SERVER STATE, i mean..

a couple  more things we are  thinking about:

Report showing database with excessive number of virtual log files. So we can then shrink and re-grow the tlog files to minimize vlog...

The question is asked incorrectly. Someone asked me

IF I have a RaiseError WITH LOG in Catch block, and  error is 'A transport-level error', should such statement/error be logged still in...

I only gave that query as an example of syntax of plan guides. It's from MS doc

The actual query is different, complex, 100 lines...

also, if you copy a very large table, like the one with millions or dozens of millions rows and a lot of columns,  you may want to do it in...