FIX: SELECT with ORDER BY Does Not Maintain Sort Order if Run in Parallel (276060)

SYMPTOMS

A SELECT statement that contains an ORDER BY clause may not return results in the specified sort order if the query is run in parallel on a computer with two or more processors.

You can determine if the query is being run in parallel by viewing the execution plan. To obtain the execution plan, execute SET SHOWPLAN_TEXT ON in a separate batch before the query. For example:

USE pubs
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM authors
GO

A parallel plan contains the word "Parallelism" in the execution plan output; the following example demonstrates this:

|--Parallelism(Repartition Streams, PARTITION COLUMNS:([fvs].[FileID]))
                                          |    |    |    |    |         |
    |--Index
Seek(OBJECT:([LBDB2].[dbo].[FileVersion].[IX_FileVersion_MSNFV] AS [fvs]),
SEEK:([fvs].[MirrorSN] <= [@msn]) ORDERED)
                                          |    |    |    |    |
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([f].[ID]))
                                          |    |    |    |    |
              |--Clustered Index
Seek(OBJECT:([LBDB2].[dbo].[File].[PK_File] AS [f]),
SEEK:([f].[DriveVSN]=1083842463) ORDERED)
                                          |    |    |    |
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([fv].[FileID]))

RESOLUTION

To prevent the specific query from having a parallel plan, specify the MAXDOP option with a value of 1. For example, on the Pubs database:

SELECT * FROM authors OPTION (MAXDOP 1)

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0