Rewrite options not to spill the sort
Thank you

_20130712_01.pesession
link text
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.
I wil split the bottom section to seperate statement, index it etc and test if that would improve the duration.
Thank you