FIX: MERGE UNION Is Not Used with Large Number of UNION Clauses

Article translations Article translations
Article ID: 295037 - View products that this article applies to.
This article was previously published under Q295037
BUG #: 351918 (SHILOH_BUGS)
Expand all | Collapse all

On This Page

SYMPTOMS

Queries that use more than 20 UNION ALL clause branches are executed with a HASH union, instead of the more efficient MERGE UNION, which causes excessive memory usage and may result in the failure of the query. An implication of this is that a distributed partitioned view running on 24 nodes has different, and less efficient, plans than a view on 20 nodes.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

Using a MERGE UNION query hint will circumvent this issue. Make sure that any optimizer hints have been thoroughly tested before being put into production.

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Open a window in Query Analyzer.
  2. On the Query menu, select Show Execution Plan.
  3. Execute the following query:
    use Northwind
    go
    select top 1 * from
    (
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders union all
    select orderid from orders 
    union all select orderid from orders
    ) xx
    order by orderid
    					
  4. Click the Execution Plan tab and observe the results.
  5. Comment out the following line of the query:
    union all select orderid from orders
    					
  6. Execute the edited query and observe the change in plan on the Execution Plan tab.

Properties

Article ID: 295037 - Last Review: November 6, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbsqlserv2000sp1fix KB295037

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com