“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)
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:
- Using DBCC TRACEON and DBCC TRACEOFF
- Using –T with the trace flag as a startup parameter for the SQL Server service
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
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
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.
- Misconceptions around TF 1118
- Backup Checksum Default Option in SQL Server 2014
- When is too much success a bad thing?
- SQL Server: Understanding and Using DBCC Commands (Pluralsight course)