Symptoms
Batch mode sorts were introduced in SQL Server 2016 under compatibility level 130. If a query execution plan contains parallel batch mode sorts in conjunction with directly-upstream parallel operators, you may encounter degraded performance compared to row mode sort plan equivalents.
This occurs due to a parallel batch sort outputting fully sorted data via a single thread to the upstream parallel operator (for example, a parallel merge join operator). The performance degradation occurs when the upstream parallel operator uses single-threaded processing due to the incoming single-threaded batch mode sort operator.
This update introduces trace flag 9358 to disable batch mode sort operations under most parallel operators including merge joins, nested loops and stream aggregations. An exception is a batch mode windows aggregation operator above the sort, which will continue to allow a batch mode sort operation under them as windows aggregation operators are optimized for reading batch sort data.
Note The batch mode sort operations are also disabled if any of the following conditions are true
-
Trace flag 4199 is enabled.
-
The QUERY_OPTIMIZER_HOTFIXES database option is enabled (starting in SQL Server 2016 CU6).
-
The ENABLE_QUERY_OPTIMIZER_HOTFIXES query hint is used (starting in SQL Server 2016 Service Pack 1 (SP1) CU4).
Resolution
The issue was first fixed in the following cumulative update of SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. We recommend that you download and install the latest cumulative updates for SQL Server:
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
References
Learn about the terminology that Microsoft uses to describe software updates.
