March 16, 2012 at 3:29 pm
Can sql server use multiple processor while merge joining?
March 17, 2012 at 12:19 am
I believe it would be possible but it really wouldn't make sense. There is an overhead to parallelism: the work has to be divided between CPU's and then the results have to be put back together again. A merge join, by its very nature, would not tend to be a good candidate for this overhead since each execution should be very small.
I'm guessing there is more to the question than you've revealed. Is there some specific scenario upon which you would care to elaborate?
March 18, 2012 at 2:35 am
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.
March 20, 2012 at 2:39 pm
CXPACKET waits are usually more related to IO than anything else from my experience - a thread is waiting for IO to complete.
I find it hard to believe that a MERGE join is only marginally faster than a HASH join regardless of parallelism. I usually find them some orders of magnitude faster than HASH joins on large recordsets. Yours could be a peculiar exception to this.
Todd Fifield
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply