Yes, it is true that from SQL Server 2016, Trace Flag 1117 and 1118 are not required. Recently I was hired by a large banking firm to help them with the Comprehensive Database Performance Health Check.
Trace Flag 1117 and 1118 – Not Required
The bank has recently upgraded its database from SQL Server 2012 to SQL Server 2019. After upgrading SQL Server on the new platform out of nowhere they suddenly started to see poor performance. I helped them fixed the performance with the SQL Server. While looking at various details about their system, I realized they had enabled trace flag 1117 and 1118 which is no longer required for SQL Server 2016 and onwards versions.
When I explained to their DBA that they do not need to enable the said trace flag as their version of SQL Server 2019 already has default behavior similar to the enabled two trace flags (1117 and 1118).
If you are still using SQL Server 2014 and earlier version of SQL Server, you may need to enable trace flag 1117 and 1118 to get the most performance from your tempDB. However, this behavior is now a default behavior. Let us understand a bit more about the said trace flags.
Trace Flag 1117
Enabling this feature forces SQL Server to auto grow all the TempDB files at the same time. When there are multiple TempDB data files, all files auto grow at the same time and by the same amount depending on growth settings.
Trace Flag 1118
Enabling this trace flag forces page allocations on uniform extents instead of mixed extents, reducing contention on the SGAM page. This trace flag allocates all eight pages to the same extent when creating new objects, minimizing the need to scan the SGAM page.
Final Note – Not Required Trace Flags
If you are using SQL Server 2016, you do not need to enable the said trace flags. However, if you are using SQL Server 2016 and later version and have kept the above two trace flags enabled by accident, you should not be worried as they have no negative impact. The blog is written with the spirit that it is just not a good idea to enable something which is not required.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Did they upgrade or downgrade based on your post? :-)
You have conflicting information in your post.
“If you are still using SQL Server 2016 and earlier version of SQL Server, you may need to enable trace flag 1117 and 1118”
“If you are using SQL Server 2016, you do not need to enable the said trace flags”
Not sure if 2016 needs the trace flags or not!
Your point is valid and I have fixed the blog post based on your feedback.
small typo.
‘SQL Server 2022 to SQL Server 2019’ should read ‘SQL Server 2012 to SQL Server 2019’
Fixed. Thanks for bringing to attention Taiob!
If it default behaviour in SQL 2019, what difference did it make removing the trace flags? Or were the performance issues not related to the trace flags on SQL 2019?
Pinal, how did you fix the performance issue?