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.
23 Comments. Leave new
MAXDOP per DB!
Right ? Best day ever
And in a cross databases query, which MAXDOP would apply?, most or least restrictive… maybe that should be configurable too… 🙂
What happens if I turn PARAMETER_SNIFFING on?
Same as OPTIMIZE FOR UNKNOWN?
This is correct
It is the other way round, parameter sniffing off ignores the passed parameters, and so is similar to optimize for unknown. Parameter sniffing is on by default.
But we still can’t get rid of auto close and auto shrink. The future stinks.
Totally agree! Maybe in a near future
Microsoft remembers to do that…
LOL!
Microsoft should at least move them out of the GUI so that newbie DBA’s can’t find them easily.
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.
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.
And BOL still has an error. Thousands of them, probably. 😉 Thanks sir!
MAXDOP per database is awesome!!
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_onFROM sys.databases AS d
If you don’t, you’ll just have to trust me.
Thanks,
Erik
Well that was easy. You are correct, it seems the default is OFF for tempdb as well. I will let poor MS documentation take the fall for this one… I appreciate the quick feedback.
Interesting that “is_mixed_page_allocation” is true for model and yet false for any user database. Would that be considered a “bug” since model is supposed to be used as the template for any new user database?
Jason – sure, the place to ask would be over here: https://feedback.azure.com/forums/908035-sql-server
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
Krunal – for unrelated questions, head to a Q&A site like https://dba.stackexchange.com.