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

This article was previously published under Q295037
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 351918 (SHILOH_BUGS)
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 theMicrosoft 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 Northwindgoselect top 1 * from(select orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union allselect orderid from orders union all select orderid from orders) xxorder 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: 01/16/2015 22:17:16 - Revision: 3.2

Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix kbsqlserv2000sp1fix KB295037
Feedback