SQL SERVER – TempDB and Trace Flag 1117 and 1118 – Not Required

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.

SQL SERVER - TempDB and Trace Flag 1117 and 1118 - Not Required not-required-800x204

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)

, ,
Previous Post
SQL SERVER – Stream Aggregate and Hash Aggregate
Next Post
SQL SERVER – Remove All Query Cached Plans Not Used In Certain Period

Related Posts

5 Comments. Leave new

  • Did they upgrade or downgrade based on your post? :-)

    Reply
  • 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!

    Reply
  • small typo.
    ‘SQL Server 2022 to SQL Server 2019’ should read ‘SQL Server 2012 to SQL Server 2019’

    Reply

Leave a Reply

Menu