SQLskills SQL101: Trace Flags

“You should always use trace flag X for a SQL Server install.”

“Have you tried trace flag Y?”

“We fixed the problem using an undocumented trace flag.”

If you’re new to SQL Server, you might have heard or read some of the above statements.  If you’ve never used a trace flag, you might wonder why you might need one, and how you would know if you did need it.  SQL Server trace flags are used to change the behavior of the engine in some way.  A trace flag is ideally used for improvement, but there can be situations where a trace flag doesn’t provide the intended benefit.  In some cases, it can adversely affect the problem you’re trying to fix, or create a different issue.  As such, trace flags in SQL Server are something to use with caution.  The number one recommendation I always make when someone asks about using a trace flag is to test it, ideally in an identical or comparable situation.  This isn’t always possible, which is why there’s always a slight risk with trace flags.  There are only three (3) trace flags that we at SQLskills recommend, by default, for a SQL Server installation:

  • 1118 (for versions prior to SQL Server 2016)
  • 3023 (for versions prior to SQL Server 2014)
  • 3226

Trace flag 1118 addresses contention that can exist on a particular type of page in a database, the SGAM page.  This trace flag typically provides benefit for customers that make heavy use of the tempdb system database.  In SQL Server 2016, you change this behavior using the MIXED_PAGE_ALLOCATION database option, and there is no need for TF 1118.

Trace flag 3023 is used to enable the CHECKSUM option, by default, for all backups taken on an instance.  With this option enabled, page checksums are validated during a backup, and a checksum for the entire backup is generated.  Starting in SQL Server 2014, this option can be set instance-wide through sp_configure (‘backup checksum default’).

The last trace flag, 3226, prevents the writing of successful backup messages to the SQL Server ERRORLOG.  Information about successful backups is still written to msdb and can be queried using T-SQL.  For servers with multiple databases and regular transaction log backups, enabling this option means the ERRORLOG is no longer bloated with BACKUP DATABASE and Database backed up messages.  As a DBA, this is a good thing because when I look in my ERRORLOG, I really only want to see errors, I don’t want to scroll through hundreds or thousands of entries about successful backups.

You can find a list of supported trace flags on MSDN, and as I alluded to initially, there are undocumented trace flags.  An undocumented trace flag is one that is not supported by Microsoft.  If you ever use an undocumented trace flag and you have a problem, Microsoft will not provide support for that problem; if you decide to use an undocumented trace flag, tread carefully, particularly in production.

How will you know if you should use a trace flag?  Online you’ll typically come across a forum post, blog post, or article that describes a scenario that you might be having, with the recommendation that you fix it with a trace flag.  You could also attend a user group meeting, a SQLSaturday or conference session, and hear the same thing.  You may have it recommended to you by a consultant, or another DBA or developer.  In all cases, it’s important to first confirm that what you’re seeing in your environment matches the behavior described by the trace flag.  If you believe you should enable a trace flag, enable it in a test or development environment first where you can recreate the problem, and then test it thoroughly.  Finally, after it’s gone through rigorous testing, you can try it in production.  Notice I say “try” because even with all your testing, if may not be the right solution for your environment.

If you find you do want to give a trace flag try, there are two ways to enable/disable them:

Enabling a trace flag with DBCC TRACEON is done using T-SQL, and you have the option to set the trace flag at the session or global level.  Typically you want the trace flag to be used by the entire instance, so you enable it globally.  For testing purposes, you may just enable it at the session level.  To enable trace flag 3226 globally you would run:

DBCC TRACEON (3226, -1);
GO

The use of -1 turns on the flag for the entire instance.  To disable the trace flag you run:

DBCC TRACEOFF (3226, -1);
GO

The advantage of using DBCC TRACEON and DBCC TRACEOFF is that you don’t have to restart the instance to use the trace flag.  The drawback is that it can be disabled by anyone who has sysadmin membership and runs DBCC TRACEOFF, and that it will not persist through a restart.  I recommend using this option when testing a trace flag.

For cases where you’ve tested the trace flag and you know that you want it enabled, then you want to add it to the SQL Server service as a startup parameter.  This requires using SQL Server Configuration Manager.  Once you have Configuration Manager open, select Services on the left side and then you’ll see all the services listed on the right.  Highlight the SQL Server service, right-click and select Properties, then select the Startup Parameters tab.  To add a startup parameter use the syntax –T followed by the trace flag, as shown below:

Adding TF 3226 as a startup parameter for the SQL Server service

Adding TF 3226 as a startup parameter for the SQL Server service

Note: There should be no space between the –T and the trace flag (but if you try and put one there, SQL Server removes it for you).

Then select Add so it appears in the Existing parameters: window, and then OK, and you will be notified that the change will not take effect until you restart the instance.  If you are not able to restart the instance immediately, you can apply it using DBCC TRACEON, just be aware that someone could remove it.

Lastly, to check what trace flags, if any, are enabled for your instance, you can use DBCC TRACESTATUS.  In our case, the output shows that we have 3226 enabled globally:

DBCC TRACESTATUS;
GO
DBCC TRACESTATUS output showing TF 3226 enabled
DBCC TRACESTATUS output showing TF 3226 enabled

 

 

 

 

As you can see, using trace flags is pretty straight-forward.  However, deciding whether a trace flag is needed and then testing to ensure it provides benefit and not detriment is what requires real work.  Use trace flags wisely, and always test first!  And remember, if you want to find all of our SQLskills SQL101 blog posts visit SQLskills.com/help/SQL101.

Additional reading:

8 thoughts on “SQLskills SQL101: Trace Flags

  1. Good job. And Happy Birthday Erin. My question is I’m running SQL Server 2012 Enterprise and have the 3026 flag set at startup. Can I have both 1118 and 3023 set to run at startup?

    1. Hi Glenn, thanks for the b-day wishes 🙂 Do you mean that you have trace flag 3226 (not 3026) set as a startup parameter? If so, then yes, you can also have 1118 and 3023. Great question – it’s ok to have to multiple trace flags enabled, but again, it’s good to test and verity. In select cases, using a combination of trace flags may not be optimal.

    1. Hi, as this is a 101 post directed at individuals that haven’t worked with SQL Server much, I purposely only linked to a list of documented, supported trace flags. Thanks for the suggestion.

      Erin

  2. Please also publish the trace flag list which SQL skills recommends for specific environment|situation

    It will be so much helpful

    Thx always

    1. Hi-

      We will not publish the trace flags that SQLskills recommends for specific environments/situations because we need to understand the details of the problem, the environment, and consider other factors about the overall solution before we provide such a recommendation.

      Thanks!

      Erin

  3. Thank you for this article, it is very helpful. One question, what about the trace flags 1204,1222 for deadlocks. I thought it was a best practice to have this on and haven’t seen any articles stating they are no longer needed.
    Thanks

    1. I wouldn’t enable that TF unless I were running into deadlocks and needed more information. Also, remember that in SQL Server 2012 deadlocks are captured by default in the system_health Event Session(which is written to the ring_buffer and event_file targets), and if those files roll over too fast to get the info you can set up a separate Event Session just for the deadlock event.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.