The problem is, I am working on a query which is getting CXPACKET wait type for long time when it uses hash join and when I created an index in such a way that it can get sorted value it used merge join but with 1 degree of parallelism and issued a lot PAGEIOLATCH_EX and PAGEIOLATCH_SH. And at the end i could not see much difference in the performance.
What I personally think is, if we have huge tables involved its better to get advantage of multiple processor of course we have to pay for CXPACKET.
So, i am eager to mix up multiple processor and merge join.
Is there any break-even-point on how big tables can merge join handle? or those laches what I am getting are showing its limit. Actually latches are related to disk subsystem and I could not see any error named SQLSERVER 833 in event log. so, I cannot blame it on disk sub system.