SQL Server 2016 TempDB Changes

SQL Server

With the upcoming release of SQL Server 2016, one thing you need to understand is the changes to the way TempDB works. One goal of this new release is “Out of the box” optimization so an administrator should not have to engage in TempDB tuning exercises or tweaking efforts.   

TEMPDB

One of these important changes is TEMPDB always assumes -T1117 and -T1118 behavior.

  • -T1117 – When growing a data file grow all files at the same time so they remain the same size, reducing allocation contention points.
  • -T1118 – When doing allocations for user tables always allocate full extents.  Reducing contention of mixed extent allocations

This means that in SQL Server 2016 you will no longer be required to turn on trace flags 1117 or 1118 to support TempDB performance tuning.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s