0

I have a 16 Core CPU processor and I have created 8 tempdb datafiles with initial size as 2,000 MB with 10% growth restricted to 10,000 MB. The log file is unrestricted and it is in simple recovery mode.

The tempdb is filling up the space, it is not going through the round robin face. Could you please help me here.

0

1 Answer 1

1

For SQL Server versions prior to SQL Server 2016, you need to implement Trace Flag 1117, which enables consistent proportional fill on your server. Proportional fill allows reliable round-robin writes to files that are members of the same filegroup by ensuring that all files have a similar amount of free space. Be aware that TF 1117 affects all databases, not just tempdb.

From technet.com:

Trace flag 1117 (-T1117) can help keep data files the same size in case DB needs to autogrow. Say you have eight DB data files with an initial size of 1000MB and autogrow of 200MB each. If the t database needs to autogrow, the default behavior is for one of these eight files to grow by the specified increment (200MB in this case). This will happen when all of the files are full, and after one file grows, it will be about 83% full (1000MB allocated from 1200MB total). The SQL Server proportional fill algorithm will direct new extent allocations to the file with the most available space, so new extents will be written to the one file that just grew. This defeats the benefit obtained by striping tempdb IO activity (especially extent allocations and de-allocations) across all of the available files.

When -T1117 is enabled, it will cause all the files in a filegroup to autogrow together by their specified autogrow increment. In the DB autogrow scenario described above, instead of one file growing by 200MB, all eight files will grow by 200MB when an autogrow event occurs. This means that all the files will have about the same percent of free space after autogrow, and the benefits of evenly distributing IO will be preserved.

It’s important to understand that –T1117 applies to the entire SQL Server instance, not just to one DB, and it affects all files in the same filegroup in a database. This means that if there are other databases that have multiple files defined for the same filegroup, and an autogrow event occurs in that filegroup, all of the files in the filegroup will autogrow at the same time (by the autogrow increment specified for each file). If there are multiple filegroups in the same database, an autogrow event that occurs in one filegroup will not affect the other filegroups. For a filegroup allocated in a single file, -T1117 has no effect.

When deciding whether to enable –T1117, consider the instance-wide implications and how it may affect other databases that have multiple files for the same filegroup. To avoid blasting your harddrives it is recommended to configure the maximum size of your database files. Always the best option is to presize your files to the assumed maximum.

You may also need to implement trace flag 1118 if you are experiencing tempdb contention. See this question for more info on that.

On SQL Server 2016+, Trace Flag 1117 and Trace Flag 1118 are automatically enabled for the tempdb database. User databases have options to control those settings with a database-by-database granularity.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.