Sql-server – Should we really turn on trace flag 1118 for every SQL instance

sql servertempdbtrace-flags

It has been recommended that every SQL instance have TF 1118 turned on

What is Paul’s recommendation for using trace flag 1118? Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There’s no down-side to having it turned on.
Paul S. Randal

It is documented in KB 2154845 while it does impact all databases, it's biggest impact is on tempdb

This means that each newly allocated object in every database on the instance gets its own private 64KB of data. Tempdb is usually the place where most objects are created, so it makes the most difference there.
Kendra Little

I have looked around and have not found anyone recommending against its use. So should we make it part of our process to turn it on when we create new instances, as well as adding it retroactively to existing SQL instances as opportunities present?

I am using SQL 2000 through 2014, have not gone live with any 2016 yet. We follow the directions to use 1 tempdb per processor up to 8.

Best Answer

For SQL Server 2000 → 2014

I generally agree with Paul Randal, that this is something you could always turn on, but I do have a little bit of hesitation to say it should always be on and you should leave it on regardless of what you observe (with or without adequate testing).

People can have poorly set up tempdb configurations, for example not enough files, files too small, files not equally sized, improper growth settings, instant file initialization not enabled, and bad coding symptoms where temp tables are not cached and reused (see here and here). In this scenario, with the perfect storm workload, it's conceivable that the trace flag could actually lead to worse overall performance. Think about a case where those settings are not optimal and you have tons of users all trying to create their own, not-from-cache copies of temp tables, and they're all using their own dedicated extents instead of sharing them. There may be some level, even if I can't produce it at will, where the overhead of dedicated extents outweighs the allocation contention savings.

Just because Paul hasn't seen a scenario where the scales tip at some threshold doesn't mean it's impossible, just that it's unlikely / uncommon.

You can likely avoid that by following all of the other best practices, because I suspect it really would require a perfect storm, I just do not feel strong enough to say always and without exception.

For SQL Server 2016

In SQL Server 2016, uniform extent allocation is the default behavior - see Bob Dorr's "it just runs faster" post about it. Other than that, many of the same things above hold true.

You can't turn this off for tempdb; so, effectively, trace flag 1118 is always on in 2016, and is permanent. So my suggestion would be to make sure tempdb works effectively under TF 1118 on your current instances and under your current workload, to avoid any surprises when you upgrade (at which point you will no longer have the option to turn it off).

If you want to turn it off for user databases in 2016, you use ALTER DATABASE:

ALTER DATABASE MyDatabase SET MIXED_PAGE_ALLOCATION ON;
Related Question