Should we really turn on trace flag 1118 for every SQL instance?
5
votes
1
answer
696
views
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](http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/)
It is documented in [KB 2154845 ](https://support.microsoft.com/en-us/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 ](https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/)
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.
Asked by James Jenkins
(6318 rep)
Jun 15, 2016, 03:18 PM
Last activity: May 8, 2020, 05:06 AM
Last activity: May 8, 2020, 05:06 AM