Rewrite options not to spill the sort

There aren't too many options here to increase the memory grant for the sort. The idea of avoiding a spill is up against a number of factors. Not only is there a big skew between threads given the unbalanced data distribution from the driver table, but the memory allocation is also evenly split across 16 concurrent sorts (at DOP 16). Taken together, these factors mean that the memory grant would have to be quite enormous to ensure that no sort on no thread spilled at execution time.

The execution statistics provided by SQL Server do not indicate how many times a sort on a particular thread spilled (though you could get an idea of this using a trace). You have to ask yourself how well this query would perform even if per-thread sort spills could be avoided, and what the cost would be in terms of memory grant.

Ultimately, this query looks to sort 573 million wide rows. This is never going to be a fast operation no matter how much memory and how many threads are thrown at it. There are questions over the overall design, and in particular with a query that wants to perform several collocated merge joins on a common key (PclID, PclSeqNbr) and then also join to a quite different key (BatchDt, BatchSeq).

Refine the design so that keys are common (and ideally with a better data distribution per driver table entry), provide a covering index to avoid the sort, or split the query into separate stages for separate common join keys. It might even be possible to rewrite the query as a driver-within-a-driver, but the query is probably already too complex to maintain effectively.

Huge queries with lots of hints and code tricks are often a sign that something is more fundamentally wrong with the database design.