Tempdb Enhancements in SQL Server 2016

Tempdb Trace Flags 1117 and 1118 are no longer needed in SQL Server 2016. 1117 flag is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE and 1118 flag is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE.

What are these Trace Flags

TRACE FLAG 1117 – GROW ALL FILES IN A FILE GROUP EQUALLY

Trace flag (TF) 1117 is related strictly to file groups and how data files grow within them. A file group is a logical container for one or more data files within a database. TF 1117 forces all data files in the same file group to grow at the same rate, which prevents one file from growing more than others, leading to the hotspot issue described earlier in this chapter. Enabling this trace flag in earlier versions of SQL Server is a minor tradeoff in performance. For example, if you were using multiple data files in user databases, this trace flag affects them as well as TempDB’s data files. Depending on your scenario, that could be problematic—an example would be if you had a file group that you did not want to grow as a single unit. Starting with SQL Server 2016, the behavior to grow all data files at the same rate is built into TempDB by default, which means you no longer need this trace flag.

TRACE FLAG 1118 – FULL EXTENTS ONLY

Administrators use trace flag 1118 to change page allocation from a GAM page. When you enable TF 1118, SQL Server allocates eight pages, or one extent, at a time to create a dedicated (or uniform) extent, in contrast to the default behavior to allocate a single page from a mixed extent. Unlike with TF 1117, there was no potential downside to enabling TF 1118—it is generally recommended for all SQL Server implementations in earlier releases. Starting with SQL Server 2016, all allocations of TempDB pages use uniform extent allocation, thus eliminating the need to use TF 1118.

In earlier versions of SQL Server, the default configuration uses one data file for TempDB. This limitation sometimes results in page-latch contention, in order to mitigate/remedy this behavior in SQL Server is to add more data files, in turn SQL Server creates more of three special types of pages (SGAM, GAM and PFS) and gives more throughput to TempDB. Importantly, these files should all be the same size.  Creation of multiple data files depends on many factors like number of cores vs CPU sockets, Hyper-threading.  Microsoft suggested in the article KB2154845, about the guidelines/recommendation to reduce allocation contention.

The recommendation in the KB2154845 is now built into product setup in SQL Server 2016. When you install SQL Server 2016, the default configuration of TempDB now adjusts to your environment, as show in the below Figure.

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

Advertisements

About Ramasankar

My name is Ramasankar Molleti, and I have been working on various database systems (SQL Server, Oracle, Postgres, DynamoDB, Redshift) over 10 years, primarily focusing on Database Administration, Architect , Development and Business intelligence. I also have working experience on Amazon Web Services DevOps. I hold various Microsoft certifications (Microsoft Certified Information Technology Professional, Microsoft Certified Technology Specialist, and Microsoft Certified Solution Associate). Disclaimer The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights. The owner of https://sqlram.wordpress.com will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.
This entry was posted in Database Administration, TempDB. Bookmark the permalink.

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