FIX: Can't force Row Mode sort by using session or query level trace flag 9347 in SQL Server 2016

Symptoms
Consider the following scenario:
  • In Microsoft SQL Server 2016, you run a query that uses a SQL Server 2016 Query Processor feature. For example, you run a SORT operator in Batch Mode.
  • You experience query performance degradation when compared to running the same SORT operator in Row Mode.
  • To resolve this problem, you try to enable trace flag 9347 at the session level or use QUERYTRACEON. You do this to disable Batch Mode sorts.
In this scenario, the SORT operator still runs in Batch Mode and ignores the session-level or query-level trace flag.
Resolution
In SQL Server 2016, you can use trace flag 9347 to force SORT operators to run in Row Mode instead of executing in Batch Mode. After you apply Cumulative Update 1 for SQL Server 2016, you can successfully enable trace flag 9347 at the session level or query level.

You can also enable this trace flag globally at the server level, session level, or query level.

Note Using trace flags incorrectly can degrade your workload performance.

For more information, see the "Introduction" section of the following Microsoft Knowledge Base article:

2801413 Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level
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.
Properties

Article ID: 3172787 - Last Review: 09/13/2016 23:45:00 - Revision: 3.0

Microsoft SQL Server 2016 Developer, Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Enterprise Core

  • kbqfe kbfix kbsurveynew kbexpertiseadvanced KB3172787
Feedback