Enhancing the performance of SQL Server is a critical task for database administrators. It is important to stay updated with the latest changes and best practices to ensure optimal database performance. In this blog post, we will discuss the recent implementation of trace flags 1117 and 1118 by a client on SQL Server 2022 and shed light on why these trace flags are no longer required since SQL Server 2016. They had hired me to help them with Comprehensive Database Performance Health Check.
Unnecessary Trace Flag Implementation
During a recent engagement with a client who had upgraded their database from SQL Server 2012 to SQL Server 2022, I discovered that they had implemented trace flags 1117 and 1118 to improve performance. While analyzing their system, it became evident that these trace flags were unnecessary, starting from SQL Server 2016.
Starting from SQL Server 2016, Microsoft made significant improvements to the default behavior of SQL Server, rendering the trace flags 1117 and 1118 unnecessary. Let’s explore these changes in detail.
Trace Flag 1117: File Growth Equally:
In previous versions, trace flag 1117 was used to ensure that all data files within a filegroup grow in the same proportion. However, since SQL Server 2016, this behavior is automatically enabled by default. SQL Server ensures that when one data file in a filegroup requires growth, all other files in that filegroup automatically grow simultaneously. This native behavior minimizes allocation contention points and enhances performance without the need for the trace flag.
Trace Flag 1118: Favoring Uniform Extents:
Trace flag 1118, which enforced the allocation of full extents instead of mixed extents, is also incorporated into the default behavior of SQL Server since 2016. With this improvement, each newly allocated object in TEMPDB receives its dedicated 64KB of data, reducing contention on the Shared Global Allocation Map (SGAM) page. This enhancement significantly improves performance without requiring manual trace flag activation.
Conclusion
While working on Comprehensive Database Performance Health Check I often end up on such interesting conversation. As we observed with our client’s implementation, it is important to stay informed about the default behaviors and improvements introduced in each SQL Server version. Please feel free to reach out to me if you have any questions. You can always reach out to me on LinkedIn.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
