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.