Article ID: 247500 - View products that this article applies to.
This article was previously published under Q247500
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 55597 (SQLBUG_70)
A query containing a sort operation may take significantly longer to execute using parallelism, as compared to being executed without parallelism. When the slow query is executing, sysprocesses shows a waittype of 0x208 or 0x200 and a lastwaittype of CXPACKET or EXCHANGE. This problem only affects a query executed against SQL Server running on a multi-processor server.
To determine whether a particular query encounters this condition, look at the plan and if you see a sort operation anywhere below the topmost parallelism step, this condition occurs. If you are using a graphical display of the plan such as the one generated by Query Analyzer, this would translate to a sort operation anywhere to the right of the left-most parallelism step.
For example, the following query demonstrates the problem:
Here is the summarized plan generated for this query:
Notice that the Sort operation occurs halfway down the plan, before the last Parallelism operator, which indicates that the sort will be performed in parallel.
|--Parallelism(Gather Streams) |--Nested Loops(Inner Join) |--Table Scan(OBJECT:(...)) |--Nested Loops(Inner Join) |--Merge Interval | |--Sort(ORDER BY:(...)) | |--Compute Scalar(DEFINE:(...)) | |--Concatenation | |--Compute Scalar(DEFINE:(...)) | | |--Constant Scan | |--Compute Scalar(DEFINE:(...)) | |--Constant Scan |--Index Seek(OBJECT:(...), SEEK:(...) ORDERED)
To work around this problem, add (MAXDOP 1) as a query hint. This hint overrides the max degree of parallelism configuration option (of sp_configure) only for the query specifying this option, and suppresses parallel plan generation. For more information on using this query hint, see the SELECT (T-SQL) topic in SQL Server 7.0 Books Online.
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561For more information, contact your primary support provider.
(http://support.microsoft.com/kb/254561/ )INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0