Use trace flags to improve performance - AWS Prescriptive Guidance

Use trace flags to improve performance

Consider using SQL Server trace flags that are applicable to your environment to enhance performance. For example:

  • 4199: Enables query optimizer (QO) changes that are released in SQL Server Cumulative Updates (CUs) and Service Packs (SPs).

  • 8048: Converts NUMA-partitioned memory objects to CPU-partitioned memory objects.

  • 9024: Converts a global log pool memory object to a NUMA-partitioned memory object.

The following examples illustrate how to turn trace flags on and off for SQL Server on Amazon EC2. If you experience any issues when you enable tracing, make sure that you have the appropriate permissions for the account.

To turn trace flag 4199 on, run:

dbcc traceon (4199, -1);

To check the status of the trace flag, run:

dbcc tracestatus (4199);

To turn trace flag 4199 off, run:

dbcc traceoff (4199, -1);
dbcc tracestatus (4199);

For a complete list of trace flags, see the Microsoft SQL Server documentation.