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

ID: Q247500

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 55597 (SQLBUG_70)

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, indicating that the sort will be performed in parallel.

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 will suppress parallel plan generation. For more information on using this query hint, see the SELECT (T-SQL) topic in SQL Server 7.0 Books Online.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

Additional query words:

Keywords : SSrvTran_SQL kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug