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

Artikli ID: 3172787 – viimati läbi vaadatud: 14. sept 2016 – redaktsioon: 1

Tagasiside