SQL Server Trace Flags are special switches that are used to customize and control specific behaviors of the SQL Server Engine. Trace Flags can be defined in two forms; Session Trace Flags that are activated and visible at the current connection level only, and Global Trace Flags that are enabled and visible at the SQL Server Instance level and applied to all connecting sessions in that SQL Server. Global Trace Flags should be enabled globally in order to take effect, where some Trace Flags that can be either Global or Session Trace Flags can be enabled in the appropriate scope, and its effect will appear on the defined level.
Trace Flags are usually used for performance diagnostic and debugging purposes. Enabling the Trace Flags is not preferred by all database administrators unless these is a hard need to enable that Trace Flag, as enabling the Trace Flags takes effect on all databases that the user connect to in the Session Trace Flags and all databases for all connecting users in the Global Trace Flags.
A five recommended Trace Flags can be considered as a part of the SQL Server database administrator’s best practices checklist; such as the 4199 Trace Flag that is used to enable the query optimizer hotfixes that are made in the previous releases of the current SQL Server version. Trace Flag 8048 is used to enable the SOFT NUMA option for the large systems. Trace Flag 2371 will override the 20% SQL statistics auto update threshold and will use a dynamic threshold value depending on the number of rows in your table. Also, it is a best practice to enable the Trace Flag 1117 to allow auto growth on all database filegroup files together and Trace Flag 1118 to reduce the Shared Global Allocation Map (SGAM) contention. In this article we will concentrate on the last two trace flags in SQL Server 2016.
Before going deeply with the SQL Server 2016 Trace Flags changes, let’s understand how to enable, disable and check the status of the SQL Server Trace Flags. Trace Flags can be enabled using the DBCC TRACEON SQL statement specifying the Trace Flag number only to enable it at the current session only or enable it globally by adding the -1 argument to the statement. The below statement will enable the Trace Flag 1117 on the current session only:
|
1 2 3 4 |
DBCC TRACEON (1117) GO |
Where the following statement will enable the Trace Flag 1118 globally:
|
1 2 3 4 |
DBCC TRACEON (1118, -1) GO |
When you enable a Trace Flag, it will be enabled till the current user disconnect from the SQL Server in the case of the Session Trace Flags or till the SQL Server service restarted in the case of Global Trace Flags. To make sure that the Trace Flags will be automatically enabled globally each time the SQL Server service started, you can use the –T startup option with the Trace Flag number in the Startup Parameters tab of the SQL Server Instance Properties window.
To enable the SQL Server Trace Flag globally from the startup parameters, right-click on the SQL Server Engine service from the SQL Server Configuration Manager tool and choose Properties as below:
In the displayed SQL Server Instance Properties window, choose the Startup Parameters tab, and write (-T4199) value in the Specify a Startup Parameter field to enable the Trace Flag 4199, then click Add button as follows:
The below warning message will be displayed to inform you that the new added startup parameter will take effect after restarting the SQL Server service:
After restarting the SQL Server service, we can check the SQL Server error log as below to make sure that the Trace Flag is globally enabled:
The status of the enabled Trace Flags can be checked using the DBCC TRACESTATUS SQL statement, that shows if the Trace Flag is enabled or not and if it is enabled at the Session or Global level as follows:
|
1 2 3 4 |
DBCC TRACESTATUS GO |
The result in our case will be like:
There is another way that can be used to enable the Trace Flag for a specific query; the QUERYTRACEON table hint statement. A good example of using this method is enabling the 9481 Trace Flag to revert back to the legacy cardinality estimator when you are using SQL Server 2016 as in the below SELECT statement:
|
1 2 3 4 5 |
USE SQLShackDemo GO SELECT * from [dbo].[FactFinance] OPTION (QUERYTRACEON 9481) |
If you manage to disable a specific Trace Flag, the DBCC TRACEOFF statement can be used to perform that. The below statement is used to disable the Trace Flag 4199 on the current sessions:
DBCC TRACEOFF (4199) GO
And the following statement will disable the Trace Flag 4199 globally:
DBCC TRACEOFF (4199,-1) GO
Now, we have a good idea about the SQL Server Trace Flags in general. As we mentioned previously, our main concerns here are Trace Flag 1117 and Trace Flag 1118, which we will discuss in details in the rest of this article.
Trace Flag 1117 is used to enable the autogrowth for all database files under the same filegroup. By default, when a database file that belongs to a specific filegroup reaches the autogrowth threshold, this file only will be extended. But if the Trace Flag 1117 is enabled, all the database files that belong to the same filegroup will be extended with the same amount.
In SQL Server 2016, enabling the Trace Flag 1117 will not force all files under the same filegroup to grow together. Let’s first list our test database files with its size using the below query:
|
1 2 3 4 5 |
SELECT Name , size*8/1024 AS FileSize FROM sys.master_files WHERE NAME like'%SQLShackDemo%' |
The result will be like:
If we try to enable the 1117 Trace Flag, apply large number of inserts and disable the Trace Flag again as the below query:
|
1 2 3 4 5 6 7 8 |
DBCC TRACEON(1117,-1) INSERT INTO [dbo].[CountryInfo]([CountyCode]) VALUES ('AMM') GO 50000 DBCC TRACEOFF(1117,-1) |
Then check the database files again, the result will be like:
As you can see from the previous result, enabling the Trace Flag 1117 didn’t force all files under the same filegroup to grow together. To do that, SQL Server 2016 introduces a new ALTER DATABASE option that enforces all files under the same filegroup to grow together, this option is called AUTOGROW_ALL_FILES. The default value is AUTOGROW_SINGLE_FILE.
The below ALTER DATABASE command is used to will force all SQLShackDemo database files under the Primary filegroup to grow together:
|
1 2 3 4 |
ALTER DATABASE SQLShackDemo MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES; |
If you repeat the insert statement again:
|
1 2 3 4 |
INSERT INTO [dbo].[CountryInfo]([CountyCode]) VALUES ('AMM') GO 50000 |
And check the database files growth, the result will be like:
As you can clearly see from the previous image, all database files under the Primary filegroup extended at the same time with the same amount. This is also valid if the database files are note equally sized, where the database files will grow together too.
The second Trace flag that also changed in SQL Server 2016 is Trace Flag 1118. The 1118 Trace Flag is used to reduce the SGAM page contention in the SQL Server. The SGAM or the Shared Global Allocation Map is responsible for tracking page allocation within the extents. SQL Server data is stored in data pages, with each page size 8 KB, each 8 contiguous pages form a data extent with 64 KB size. In SQL Server versions prior to SQL Server 2016, when a new object is created, the first eight pages are allocated from different extents. This allocation type is called Mixed Extents Allocation. When the object requires more pages, it will be allocated from the same extent, in a technique called Uniform Extent Allocation. With a lot of mixed pages within different extents, the allocation will be more complex, with heavy scans on the SGAM page.
Using the Trace Flag 1118, the first eight pages will be allocated from the same extent when creating a new database object, minimizing the scan operations on the SGAM page, by turning off the Mixed Extents Allocation.
In SQL Server 2016 the Trace Flag 1118 has no effect, as the Uniform Extent Allocation is the default allocation method, where the first 8 pages will be allocated from the same extent once a new database object is created, with the ability to turn the MIXED_PAGE_ALLOCATION ON and OFF using the below ALTER DATABASE statement:
|
1 2 3 4 |
ALTER DATABASE SQLShackDemo SET MIXED_PAGE_ALLOCATION OFF GO |
If we try to add the Trace Flag 1118 to the Startup Parameters in SQL Server 2016 as described previously and check the SQL Server Error Log, a message will be displayed informing us that this Trace Flag is no longer in use and will not take effect, and that it is replaced by the ALTER DATABASE statement:
A new column is added to the sys.databases system view that can be used to check the page allocation method used in the current SQL Server instance, with value 0 for the is_mixed_page_allocation_on column indicates that SQL Server is using Uniform allocation method as follows:
|
1 2 3 4 5 |
SELECT name, is_mixed_page_allocation_on FROM Sys.Databases where name='SQLShackDemo' |
The result will be similar to:
To understand how it works let’s create a new table in our SQLShackDemo database and insert records into that table as in the below script:
|
1 2 3 4 5 6 7 8 9 10 |
USE [SQLShackDemo] GO CREATE TABLE [dbo].[CountryInfoTest]( ID INT ) ON [PRIMARY] GO INSERT INTO [dbo].[CountryInfoTest] (ID) VALUES (100) GO 5000 |
Now, run the below command that list all pages associated with the new table:
|
1 2 3 4 5 6 7 |
SELECT extent_file_id ,extent_page_id ,is_iam_page ,is_mixed_page_allocation,page_type_desc FROM sys.dm_db_database_page_allocations( DB_ID('SQLShackDemo'), OBJECT_ID('dbo.CountryInfoTest'), NULL , NULL , 'DETAILED') |
The output of the previous command will be similar to the below:
As we can see from the previous image, the pages are allocated in our table uniformly, where the first eight pages allocated from the same extent and so on.
Conclusion
SQL Server Trace Flags are useful switches that can help database administrators control the SQL Server Engine behaviors. Enabling some of these Trace Flags becomes a best practice in order to force the SQL Server to behave in the best way at some circumstances. In SQL Server 2016, two of these Trace Flags; 1117 and 1118 are no longer applicable and replaced by new options using the ALTER DATABASE SQL command. This change allows the database administrators to deal easily with these options and take benefits of it.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021