How do I optimize this query? It is taking 10 hours on a 32 core machine with SAN drive connections.
Cost Threshold for Parallelism is set to 5
Locks set to 0
Query Wait set to 01
Now, blocking is inherent with parallelism – why did you choose MAXDOP 2? Do you know for a fact that this particular query would not benefit from more parallelism? You will see "blocking", but it's not really blocking, it's the main thread waiting on the worker threads (CXPACKET waits, if you will). Try various MAXDOP values – 2 may not be optimal in this case.