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