Skip to Content
Apr 24, 2014 at 01:45 PM

Bad Performance of Merge Join


We are on ASE 15.0.3/EBF 21284 ESD#4.3 working on a application with over 3000 stored procedures.

Our server optimization goal is allrows_mix.

The Merge-Join is giving us problems. When a query uses Merge-Join, it usually take an order of magnitude longer to run than if we force it

to use the other types of joins - nested-loop, n-ary-nested-loop, or hash-join.

The query plan shows sorting on worktables leading into the merge-join.

I know I can disable it with "set merge_join off", or "set plan optgoal allrows_oltp", but I'd rather not if I can fix the problem instead.

Question: Are there configuration options that would help merge-join?

I've done variations of this:

sp_configure "number of sort buffers", 32000

I've also done variations of this in the proc:

set parallel_degree 5

set scan_parallel_degree 4

When I run the following command, I see sort buffer starvation:

1> sp_monitorconfig "sort buffers"

2> go

Usage information at date and time: Apr 24 2014 2:31PM.

Name Num_free Num_active Pct_act Max_Used Reuse_cnt

------------------------- ----------- ----------- ------- ----------- -----------

number of sort buffers 0 82045 100.00 82045 0

(1 row affected)

(return status = 0)

Maybe there are other configuration option to help merge-joins? Any ideas?