To Forceseek or not to Forceseek
Should I forbid the users from using forceseek because it will cause additional trouble that I cannot see on a query plan which will affect the whole server or should I tell them if the driver table is less than 10 million records or 10% of the largest table use forceseek?
If many queries used forceseek hint would that create any other issue that I may not have seen yet?
The hash join query has one primary advantage: it employs effective read-ahead using large I/Os. On the other hand, that query requires a 22GB memory grant. This 22GB has to come from somewhere, resulting in cached data being evicted from cache, tending to increase I/O.
With a number of high-grant queries running, you also risk grant waits as you have noticed. The hash join plan is also quite inefficient in passing very large numbers of rows across repartitioning exchanges. Parallel hash join is a good choice where one input is much smaller than the other, and that is not the case here.
The nested loops plan requires more logical I/O, and does not use read-ahead as effectively. In fact, your plan shows no pre-fetching at all on the joins, which is a little surprising. Nevertheless, the proportion of logical reads that resulted in a physical I/O is still quite low. Overall, I would prefer the NLJ plan here, from a concurrency and efficiency point of view.
Both plans perform significant work in the final Compute Scalar that runs on a single thread over 53 million rows. You may find an improvement in moving those calculations into the parallel zone – an OUTER APPLY with NULL rejection is the trick I most commonly use to achieve that.
Selecting 4.1 GB of data into a table on a single thread is also a bottleneck, but without using CLR to parallel bulk load, you will have to wait for a future SQL Server version to improve that aspect of the execution. You could choose to select into a (global) temporary table instead, primarily for the logging efficiency in tempdb, but you may have good reasons for not doing that. As I say, specific advice is tough without detailed knowledge of the wider system.