Notes From A SQL 2017 Azure Install – Trace Flags

Posted on by

Like most Database Administrators, I have a standard set of Trace Flags that I implement on my SQL Servers.  These are my tried and true trusty companions, used to help tune SQL Server to my specific needs.

However, with SQL Server 2017 some of my long-time trusty Trace Flags are no longer needed.  Below is a list of the Trace Flags that I normally implement, with my notes on why they are no longer used in SQL Server 2017.  Do not consider this a complete list of Trace Flag changes in SQL 2016 and SQL 2017, these are just the ones that I normally use.

  • Trace Flag 1117 – grow all data files at the same time so they remain the same size
    • Enabled in TempDB by default as of SQL Server 2016;
    • Now has to be specified on individual file groups via ALTER DATABASE;
      • ALTER DATABASE mydb MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
  • Trace Flag 1118 – always allocate full extents
    • Enabled in TempDB by default as of SQL Server 2016;
    • Replaced by a new DATABASE setting – MIXED_PAGE_ALLOCATION
    • Now has to be controlled via ALTER DATABASE;
      • ALTER DATABASE <dbname> SET MIXED_PAGE_ALLOCATION { ON | OFF }
      • Default value of the MIXED_PAGE_ALLOCATION is OFF meaning allocations in the database will use uniform extents.  This setting is opposite in behavior of the Trace Flag (i.e. TF 1118 OFF and MIXED_PAGE_ALLOCATION ON provide the same behavior).
  • Trace Flag 2371 – control when the query optimizer generates autostats on a table
    • For databases with compatibility level 130 you no longer have to enable trace flag 2371 because the corresponding behavior is enabled by default. (Microsoft Support Doc)
  • Trace Flag 4199 – Enables query optimizer changes that are released in SQL Server Cumulative Updates and Service Packs.
    • Now has to be controlled via ALTER DATABASE SCOPED CONFIGURATION
    • Enables or disables query optimization hotfixes regardless of the compatibility level of the database. The default is OFF. This is equivalent to enabling Trace Flag 4199.
      • ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }
  • Trace Flag 9481 – Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier versions
    • Now has to be controlled via ALTER DATABASE SCOPED CONFIGURATION
      • Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. The default is OFF, which sets the query optimizer cardinality estimation model based on the compatibility level of the database. Setting this to ON is equivalent to enabling Trace Flag 9481.
      • ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }

Additional notes on Trace Flags 1117 & 1118:

The first server that I spun up, I used an Azure SQL 2017 template.  In it Trace Flags 1117 and 1118 are enabled as startup parameters in SQL Server Configuration Manager, even though they don’t do anything in SQL 2017.  You may want to manually remove them.

This post is part of an ongoing series of blog posts related to my Azure SQL Server 2017 Migration.

 

(Visited 1,264 times, 1 visits today)