What are Trace Flags 1117 and 1118 In Sql Server?

Trace Flag 1117

First of all, what is a trace flag? A trace flag is an advanced property with which you can set and change some of the Sql Server characteristics. There are numerous trace flags in Sql but for the profound impacts of trace flags 1117 and 1118 in performance and speed optimization, I decided to dedicate an article to it.

In short, You would set trace flag 1117 when you want all of your files in a filegroup to grow equally in size. Why do we want that? Good question. As you know auto growth is a costly process and it will lead to slowing down the database response time.

When you insert a record into the database table, Sql Server uses a Round Robin algorithm to write the data into the data file. If the so-called data file runs out of space, the auto-growth event will be triggered to expand the file size. Now, this process leads to an unbalanced data distribution across these files.?

If we could use a technique to somehow make all the data files in a file group grow equally in size, then we would be able to somehow mitigate this costly process of auto-growing.

In Sql Server 2016 and upper versions, you can fully control the auto-growth process of data files within a filegroup at the database level. It has also set this flag on for Tempdb as a default feature. As the query shows this feature is on for TempDB:

USE tempd
? GO
? SELECT
? DB_NAME() AS database_name
? ,[name]
? ,type_desc
? ,is_autogrow_all_files
?FROM sys.filegroups        

Now, How can we set this Flag on in our database? Simply by Altering your database using these statements:

You can also set this flag to AUTOGROW_SINGLE_FILE to make it undone. You should know that you cannot change Tempdb Trace Flag 1117 to AUTOGROW_SINGLE_FILE. You would get the following error :

Trace Flag 1118

As you know there are two kinds of extents in Sql Server: Uniform extent (All 8 pages are reserved or filled with only one table's data) and Mixed extent (All 8 pages are shared by numerous objects). Sql Server 2016 and its upper versions changed the default behavior of having mixed extents. Now Trace Flag 1118 is a default set on TempBD and all other user databases; Meaning that now all the pages in these databases are uniform extents. You can set this flag off for all user databases but you cannot change the default trace flag for TempDB. All other system databases like Master, Model, and Msdb have a default set of mixed extents so no Trace Flag 1118 for them :).

You can alter your database to make the extents of your database as Mixed :

ALTER DATABASE DWQueue SET MIXED_PAGE_ALLOCATION ON;        

Thank you for reading this article. I hope this article was helpful to you.