Consider Enabling Trace Flag 1117 on Dynamics AX SQL Server

Correct configuration of the tempdb database is crucial for Dynamics AX workloads. Two of the key recommendations are to maintain one tempdb data file per processor core, and to ensure that all tempdb data files are the same size.

Trace flag 1117 (-T1117) can help keep tempdb data files the same size in case tempdb needs to autogrow. Say you have eight tempdb data files with an initial size of 1000MB and autogrow of 200MB each. If the tempdb 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 tempdb 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 tempdb, 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.

For more information on tempdb configuration for Dynamics AX, please refer to the Microsoft Dynamics AX 2009 White Paper: Planning Database Configuration. The recommendation to consider enabling trace flag 1117 will be published in a future version of the White Paper. This document now also applies to the latest release, Dynamics AX 2012.

For more information on how to enable trace flags at SQL Server startup, please refer to Using the SQL Server Service Startup Options.

Comments

• Anonymous
  September 12, 2011
  Are you referring to AX 2012 and SQL 2008 (R2)?How does this apply to SQL Server 2005 and AX 2009?
• Anonymous
  September 13, 2011
  Actually to my knowledge it should be enough to have all tempdb files to have the same initial file size, then the proportional fill algorithm should kick in: all tempdb files WILL grow at the same time. This is the behaviour I was observing since SQL2000. AFAIK this is the behaviour also Paul Randal is referring to: sqlskills.com/.../Tempdb-configuration-survey-results.aspxAm I wrong ?Thank you!Thx for recent posts on AX perf, keep it up! :-)
• Anonymous
  December 29, 2014
  Great article, you can also consider checking this list of the most important trace flags for SQL Server, I personally have this in my bookmarks as reference sqlturbo.com/the-most-important-trace-flags-for-sql-server