SQL Server 2016: The Death of the Trace Flag

Trace flags are special switches that, when you flip them, enable different behaviors in your SQL Server. Back in the SQL Server 2000-2005 days, these were super-rare undocumented tricks that were handed out by Microsoft support to fix rare cases.

Over time, some trace flags have become so commonplace that they worked their way into some best-practices setup checklists, like:

  • Trace flag 1118 to reduce SGAM contention
  • Trace flag 1117 so when a single file in a database filegroup needs to grow, they all grow together
  • Trace flag 2371 so statistics are recomputed less rarely as tables grow larger
  • Trace flag 4199 to get new query optimizer hotfixes that produce different query plans
  • Trace flag 8048 enables SOFT NUMA when SQL’s NUMA-awareness code doesn’t work on larger systems
  • And a whole lot more trace flags are out there

But trace flags have two big problems: trace flags take effect across the whole server, and most folks aren’t comfortable enabling them.

SQL Server 2016’s New ALTER DATABASE Commands

SQL Server 2016 does away with these unintuitive trace flags by adding new ALTER DATABASE commands:

ALTER DATABASE SET MIXED_PAGE_ALLOCATION ON (or OFF, which is the new default behavior)

ALTER DATABASE MODIFY FILEGROUP [myfilegroup] AUTOGROW_ALL_FILES (or AUTOGROW_SINGLE_FILE, which is still the default)

Plus, file sizes and growths have new sensible defaults: both data and log files default to 8MB in size, and auto-growth is 64MB. (One could argue that 8MB is still on the small side, but some software-as-a-service companies with one client per database still manage to pack thousands of tiny databases per instance.)

So far, so good: the trace flag behaviors are well-tested, almost as if they’ve been in beta for a decade. It’s just that now some of the good ones are on by default, like 1118 and 2371, and the rest are easier to configure safely.

Even Better: DB-level MAXDOP, Parameter Sniffing, Hotfixes

SQL Server 2016 brings a few new fun options with a new command, ALTER DATABASE SCOPED CONFIGURATION. Now, for each database, you can set:

  • MAXDOP – the value of your choice
  • LEGACY_CARDINALITY_ESTIMATION – on or off
  • ONLINE_OPERATION_DEFAULT – on or off
  • PARAMETER_SNIFFING – on or off
  • QUERY_OPTIMIZER_HOTFIXES – on or off

This is simply awesome for shops that have to cram different groups of databases and workloads into the same SQL Server – especially with complex Always On Availability Group deployments with multiple AGs and listeners.

New database scoped configuration options in SSMS 2016

New database scoped configuration options in SSMS 2016

Previous Post
Changes to auto update stats thresholds in SQL Server 2016
Next Post
SQL Server 2016 Installation Screenshot Tour

23 Comments. Leave new

  • MAXDOP per DB!

  • What happens if I turn PARAMETER_SNIFFING on?
    Same as OPTIMIZE FOR UNKNOWN?

  • Erik Darling
    March 7, 2016 3:02 pm

    But we still can’t get rid of auto close and auto shrink. The future stinks.

  • Jay Ramachandran
    March 7, 2016 7:32 pm

    I like the Maxdop per database option as we have lot of shared servers with tons of databases in each.

    We used and liked the 1117 flag on some instances , it’s literally impossible in a large enterprise to handhold and grow every single database/file group. Do you know any way around it ? Because we have seen it causes unbalanced files sizes in file groups.

  • Alex Friedman
    March 9, 2016 1:55 am

    Amazing!

  • Trace flag 8048 doesn’t interact directly with soft NUMA 🙂
    Trace flag 8048 changes the scope of spinlock resources from NUMA node level to CPU level, reducing the possibility of contention while slightly increasing overhead.
    Soft NUMA on the other hand concerns distribution of the workers handling new incoming connections.

    SQL Server 2016 introduces adaptive spinlock scope (although Microsoft probably has another, cooler name for it). When a global or NUMA node level spinlock resource is deemed contended, the scope is automatically promoted. On many systems I’ve recommended trace flag 8048 from SQL Server 2008 through SQL Server 2014. My testing so far with SQL Server 2016 has shown little-to-no benefit of trace flag 8048 above what is already provided by the adaptive spinlock scope.

  • There is no ONLINE_OPERATION_DEFAULT database scoped property. BOL had an error back when you wrote this post.

  • Rexann Kapoor
    August 16, 2016 5:06 pm

    MAXDOP per database is awesome!!

  • Richard Herling
    September 21, 2016 4:14 pm

    Hi Brent,
    You mentioned above that “… ALTER DATABASE SET MIXED_PAGE_ALLOCATION ON (or OFF, which is the new default behavior) … some of the good ones are on by default, like 1118 and 2371…” Can you verify this is the case for tempdb as well? Below it seems MS is saying that this is not enabled for tempdb by default from the following, which states that tempdb is the only system db that supports MIXED_PAGE_ALLOCATION OFF, but that the setting is ON for all system dbs by default. TF1118 was equivalent to MIXED_PAGE_ALLOCATION OFF correct? If so it seems this is only the default for user dbs in SQL Server 2016, not tempdb as well. Please let me know if I misunderstood something here, but it seems like in SQL Server 2016 you would have to SET MIXED_PAGE_ALLOCATION OFF for tempdb to get the benefits of what you previous got from TF1118. I appreciate any clarity you can provide.

    “Note: Beginning with SQL Server 2016 this behavior is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE, and trace flag 1118 has no affect. For more information, see ALTER DATABASE SET Options (Transact-SQL).” – https://msdn.microsoft.com/en-us/library/ms188396.aspx?f=255&MSPPError=-2147217396

    “MIXED_PAGE_ALLOCATION { OFF | ON } controls whether the database can create initial pages using a mixed extent for the first eight pages of a table or index.
    OFF
    The database always creates initial pages using uniform extents. This is the default value.
    ON
    The database can create initial pages using mixed extents.
    This setting is ON for all system databases. tempdb is the only system database that supports OFF.” – https://msdn.microsoft.com/en-us/library/bb522682.aspx

    • Hi Richard,

      It looks like the default is to NOT use mixed page allocations for tempdb, and all user databases. On my 2016 install (where I haven’t changed this setting anywhere) master, model, and msdb are using mixed extents. Nothing else is.

      If you have 2016 installed, just run this query:

      SELECT d.name, d.is_mixed_page_allocation_on
      FROM sys.databases AS d

      If you don’t, you’ll just have to trust me.

      Thanks,
      Erik

  • What about trace flag 653. It disables read ahead read. While I was testing and even though I’ve enabled trace 653 the query optimizer was doing read-ahead reads. Any input?
    The query I was using is as below:

    set statistics io on

    DBCC DROPCLEANBUFFERS
    DBCC TRACEON(653)
    select top 100000 * from filler5
    cross apply sys.objects
    cross apply sys.tables

    SQL Server version: 2016 developer edition

Menu
{"cart_token":"","hash":"","cart_data":""}