Microsoft KB Archive/295037

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

Article ID: 295037

Article Last Modified on 11/6/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q295037



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 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.



Steps to Reproduce Behavior
 Open a window in Query Analyzer. On the Query menu, select Show Execution Plan.  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  Click the Execution Plan tab and observe the results.  Comment out the following line of the query: union all select orderid from orders </li> Execute the edited query and observe the change in plan on the Execution Plan tab.</li></ol>

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB295037

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.