FIX: Parallel Sort May Be Slower Than Non-Parallel Sort

View products that this article applies to.

This article was previously published under Q247500

BUG #: 55597 (SQLBUG_70)

Expand all | Collapse all

SYMPTOMS

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:

SELECT t2.ColInt
FROM Table1 t1, Table2 t2
WHERE t1.ColVarChar IN (SUBSTRING(t2.ColVarChar, 3, 12), SUBSTRING(t2.ColVarChar, 3, 8))

Here is the summarized plan generated for this query:

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

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.

Back to the top

WORKAROUND

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.

Back to the top

STATUS

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:

254561 (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

For more information, contact your primary support provider.

Back to the top