Article ID: 295037 - View products that this article applies to.
This article was previously published under Q295037
BUG #: 351918 (SHILOH_BUGS)
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.
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:
(http://support.microsoft.com/kb/290211/EN-US/ )INF: How to Obtain the Latest SQL Server 2000 Service Pack
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.
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