Trace Flags in SQL Server for Performance Optimization

Trace flags in SQL Server are used to change any specific server characteristics or to switch off a particular behavior. This blog post lists the most important trace flags which helps in improving the performance of the SQL environment.

DBCC TRACEON Vs STARTUP PARAMETER

Before seeing in detail about the essential trace flags, it is important to understand the trace flag configuration using DBCC TRACEON vs Startup parameter. These are essentially two different ways of enabling trace flags.

^{DBCC TRACEON Vs STARTUP PARAMETER}

Later in the blog, we would see ways to enable Trace Flags by both the methods.

IMPORTANT TRACE FLAGS

TRACE FLAG 1117

TF 1117 forces all data files on each database to grow uniformly. Whenever data file grows uniformly, I/O will be better, and it improves the speed of read and write operations in SQL Server.

Following is a sample database where the data is spread across multiple database files. Since the TF 1117 has been set, the data files grow uniformly, leading to efficient I/O operations.

TRACE FLAG 1118

By default database extent will be configured as MIXED. Generally mixed extent degrades the performance of your SELECT statements. If we use UNIFORM extent then data page and index page will be stored on separate extents. If you enable TF 1118, the extent is configured as UNIFORM by default. Hence SQL Server performance would be improved. Following is the diagrammatic differentiation between MIXED and UNIFORM extent.

To find the type of database extent configured in your database, the following query can be run from your SQL Query Analyser.

select name, is_mixed_page_allocation_on from sys.databases;

Below snapshot shows whether your database extent is allocated as uniform or mixed. If the output is 0 then it signifies uniform extent and 1 denotes mixed extent.

TRACE FLAG 3226

If you notice the SQL error log there would be lots of backup success messages which gets saved. Sometimes we really don’t need all the success messages for backup. For example, consider the log shipping scenario. Hence, we have TF 3226 which avoids writing success messages into your error log.

For instance, enabling TF 3226 helps us avoid writing unnecessary success messages as shown in the below snapshot.

TRACE FLAG 2371

Whenever huge volume of changes happens on SQL tables, the internal process automatically calls statistics to update all the changes. During this time your query must wait to build the new execution plan. If you enable TF 2371, the query need not wait till statistics update gets completed.

ENABLING TRACE FLAGS

To make sure whether the above TFs are enabled or not, we could use the below command:

DBCC TRACESTATUS(-1)

GO

To enable a particular trace flag, the below syntax can be used

DBCC TRACEON(<<tf_id>>, -1)

GO

The below screenshot demonstrates the same

The DBCC TRACESTATUS command helps us cross verify if the TFs are enabled.

Another way of enabling traceflags is by specifying the startup parameter as shown below:

At GeoPITS, we work with multiple clients across the globe on performance tuning and optimization.