Esat Erkec
SQL Server batch mode processing

Microsoft SQL Server Trace Flags and usage details

April 13, 2022 by

In this article, we will explore Microsoft SQL Server trace flags with all aspects, and we will learn also how to use them.

Introduction

Trace flags are the advanced configuration options that are used to change specific behaviors of the SQL Server. Trace flags are commonly used to diagnose specific problems of the Microsoft SQL Server but sometimes they can be recommended practices to resolve specific issues related to the database engine. Besides this, some trace flags might be transformed into a feature in newer releases of SQL Server. Such as, trace flag 1117 helps to resolve some tempdb database performance issues under particular circumstances. However, this trace flag functionality has become a default option with Microsoft SQL Server 2016 and the higher versions.

Trace Flag 1117: Tempdb database data file grows with all files at the same time so each of them remains on an equal size.

Microsoft SQL Server 2016 and upper versions this trace flag is implemented as a default setting for the tempdb database. Through the following query, we can display this setting for the tempdb database.

Trace Flag 1117 details

At the same time, we can not change this setting for the tempdb database. As we can see below, when we try to alter this feature, SQL Server will return an error.

How to modify MODIFY FILEGROUP setting of a database

The other remarkable point about the flags is that some flags can be undocumented by Microsoft. In this situation, usage of these trace flags can impact the database workload negatively therefore test the flags in the non-production environment will be a good practice.

Trace flags scopes

Trace flags can be enabled or disabled with different methods in Microsoft SQL Server for the different scopes. We can enable flags manually through the commands for the following scopes:

  • Query-level
  • Session-level
  • Global

How to enable a trace flag in query-level

We can use the QUERYTRACEON query hint in the queries to enable a trace at the query-level. By default, the query optimizer decides to use adaptive join for the following query.

Trace Flag 9398

Trace Flag 9398: This trace flag prevents the use of the adaptive join operator by the query optimizer. This trace flag can be enabled for all scopes.

Now, we add the QUERYTRACEON hint to the query and enable the 9398 flag. In this case, the optimizer will abandon to use adaptive join operator.

A  trace flag change a query plan in Microsoft SQL Server

The select operator includes the TraceFlags property, which lists the trace flags that affect the query during its execution. The IsCompileTime attribute shows true and it means the flag has been enabled during the compilation of the query.

TraceFlags attribute of a query plan

Enabling a trace flag in session-level

We can use the DBCC TRACEON command to enable the flags at the session-level. We only pass the trace flag number into this command. The following command enables a trace flag at the session-level.

When we want to disable the same flag, we need to run the DBCC TRACEOFF command on the same session.

Enabling a trace flag at the global level

To enable a trace flag at the global level we need to pass the -1 value as a second parameter into the DBCC TRACEON command.

In order to disable the same trace flag at the global level, we can execute the following query.

When we enable a flag at the global level to use the DBCC TRACEON statement, it will remain valid until the trace flag is disabled or restarting the Microsoft SQL Server.

Adding a trace flag into the Microsoft SQL Server startup parameters

In some scenarios, we can require to enable a trace flag automatically when the database engine is started. In such a case, we can add the trace flag into the database startup parameters. We can easily make trace flag automatization to use SQL Server Configuration Manager. Firstly, we will launch the Configuration Manager and right-click on the SQL Server service from the service list panel. In the menu, we select the Properties.

SQL Server Configuration Manager

We click the Startup Parameters and then add the trace flag number with the -T switch into the Specify a startup parameter text box. As a final step, we click the Apply button.

SQL Server startup parameters

After adding the trace flag into the startup parameters, it will become active after the SQL Server service restarts.

Getting trace flag status

DBCC TRACESTATUS command helps us to learn the status of the trace flags. When we execute the DBCC TRACESTATUS command with the -1 parameter, it will return the status of all trace flags that are currently enabled globally.

Trace flag status

TraceFlag column specifies the number of the trace flag number and the other columns show the status of the trace flags in the specific level. The 1 value identifies that the trace flag is enabled at and the 0 value identifies the trace flag is disabled. When we execute the DBCC TRACESTATUS without the -1 parameter, it will return the enabled trace flags for the current session.

Monitor trace flag status

Monitoring trace flag activities

Microsoft SQL Server extended event is a lightweight monitoring tool and offers a very effective solution to diagnose the problems. The trace_flag_changed event captures when a trace flag status is changed thus we can use this event to monitor the trace flags. At first, we add a new session and give a name to it. In the General tab, we click the below options:

  • Start the event session immediately after session creation
  • Watch the live data on screen as it is captured

Creating a new extended event session

After that, we click the Events tab and we find the trace_flag_changed, and then add it into the Selected Event list.

Selecting events in an event session

Click the Configure button and select the required global fields, and click OK.

  • client app name
  • database name
  • SQL text

Session properties

On the Data Storage tab, we select the event_file type and click OK. Now we will enable and disable a trace flag for the different scopes.

Result of the event session

As we can see, all trace flag changes are captured by our extended event session. Another method to monitor the trace flags is to read the SQL Server error log records. When a trace flag is enabled, SQL Server writes a log record for these activities. To read the Microsoft SQL Server error logs, we can use the sp_readerrorlog procedure. With the help of the following query, we can filter the error logs and only return trace flag-related activity logs.

Read SQL Server error logs

Monitoring the query-level trace flag usage activities can be a bit different than the session-level and global ones.

When we execute the following query, it will process the rows in a batch mode because of the Microsoft SQL Server 2019 Batch Mode on Rowstore feature.

SQL Server batch mode processing

Now, we will enable flag 9453 for this query, and then the batch mode row processing will not be used by the optimizer.

Trace flag 9453

Monitoring trace flag usage with extended events

As we can see the query level usage of the trace flag has been captured. However, in the next execution of the same query, any event is not captured because the cached plan will be used. An alternative method to monitor the query-level flag activities is to use cached query plans. We can search the ‘%OPTION%QUERYTRACEON%’ pattern in the cached query plans so that we can find out which queries enable the flags.

Find the cached execution plans with a query

Summary

In this article, we have learned usage details of the Microsoft SQL Server trace flags and how we can enable or disable the trace flags for the different scopes.

Esat Erkec
3,729 Views