SQL Server 2016: TempDB enhancement – by default turn on trace flags 1117 and 1118

I am exploring SQL Server 2016 and every time I am getting something new and effective enhancements those added in SQL Server 2016.

Whenever you are searching for TempDB optimization tips, you can find one note for trace flags 1117 and 1118 which are for Allocations and Auto Growth.

You can use trace flag 1117 – When growing a data file grow all files at the same time, so they remain the same size, reducing allocation contention points.
You can use trace flag 1118 – When doing allocations for user tables always allocate full extents. It reduces contention of mixed extent allocations.

Eariler, you can turn on these trace flags at server level which impacts to other databases as well.
And now the great enhancement by SQL Server 2016 is, it enables these flags by default for TempDB and customize for other user databases.

In the next post, You can find about How to enable trace flag 1117 and 1118 on user databases.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of