Since this is in a 2008 forum, you should be aware that auto-update statistics settings is often not enough for large tables.  There's a known condition since SQL Server will...

A case where I've seen it works best to divide and conquer, is when you have a query with a large number of joins (it's relative I suppose, I don't...

If the disks are being sourced from a SAN, having separate drives for each tempdb file wouldn't matter, as each "drive" would probably be carved out of the same RAID...

The problem with your last query is each MAX() function is evaluated separately over the GROUP.  To really solve this, we'd need to understand your data more, is it possible...

Having connections that are sleeping is not necessarily a bad thing, there might be connection pooling going on, where an application doesn't physically close the database connection, just leaves it...

I'd agree with Ed's solution, the only difference is depending on how many rows you need to process for this, it may make more sense to do the ROW_NUMBER() OVER...

There's a free tool from Idera Software which I use that does this kind of work:
https://www.idera.com/productssolutions/freetools/sql-instance-check
and they keep track of the patches and updates available for all...

I think I understand what you're doing, you should just need another join back to table2:

SELECT T2max.LinkingKey AS Max_K_Key, T2min.LinkingKey AS Min_K_Key
FROM #TEMP1 T

SELECT DATEADD(yyyy, DATEDIFF(yyyy