Hi Friends,
I have written 50 blogs under One Trace Flag a Day Series. Today I am going to put all those blog links at a single place to make SQL Server Trace Flags List or you can say it as an index page for Trace Flag a Day Series. I hope this will make ease of search for you.
1- SQL Server Trace Flags Basics
I am going to start a series on SQL Server Trace Flags. So in my first blog I want to put some light on the introduction and how to use trace flags.
2- SQL Server Trace Flags DBCC TRACESTATUS
There may be a situation when you want to know which trace flag is enabled right now. There is one DBCC command and you can use that to find such type of information.
If we want to use SQL Server standard 64 bit edition along with locked pages in memory (LPIM) feature then we will use this trace flag 845.
Trace flag 3604 is used to redirect the output of some DBCC commands to the result window.
Trace flag 3605 is used to redirect the output of some DBCC commands to the SQL Server Error Log .
Sometimes during troubleshooting you may need such type of information like when checkpoint process started and ended. This trace flag can really help you in such scenario. If you will enable Trace Flag 3502, then it will write checkpoint start and end messages in the SQL Server error log.
Sometimes during troubleshooting you may need such type of information like what checkpoint does behind the scene. This trace flag can really help you in such scenario. If you will enable Trace Flag 3504, then it will write checkpoint internal activity information in the SQL Server error log.
Now think about a scenario like system is already under heavy IO load and checkpoint can also add more IO load on the server. Here we can enable trace flag 3505 to disable automatic checkpoints across the server i.e. for all databases on the instance. So we can say that by using trace flag 3505, we can control the behavior of automatic checkpoints.
Can we get more internal information in the sql server error log for each backup operation? Yes, we can by using Trace Flag 3014 with 3605. If we will enable these trace flags then it will write that output to error log and we can check that.
10- SQL Server Trace Flag 3023
If you will enable the trace flag 3023 then no need to specify CHECKSUM option at the time of taking backups. WITH CHECKSUM option will be automatically applied for all database backup operations.
11- SQL Server Trace Flag 3226
This trace flag can be used to stop the logging of successful backup messages in the log.
12- SQL Server Trace Flag 3004
Database restore internal information will be logged phase by phase.
13- SQL Server Trace Flag 1117
By using this trace flag, if auto grow event will fire under a filegroup then all files under that filegroup will grow.
14- SQL Server Trace Flag 1118
Trace Flag 1118 is used to enable the dedicated extent allocation for objects instead of allocating pages from mixed extents. Here we can say trace flag 1118 completely removes single data page allocation from sql server.
15- SQL Server Trace Flag 1204
Trace flag 1204 provides node base information about deadlock in another words you can say that all nodes which are involved in deadlock. Finally after all nodes information it also provides information about deadlock victim.
16- SQL Server Trace Flag 1222
Trace flag 1222 provides process and resource base information about deadlock in XML format. In another words you can say that all processes and resources which are involved in deadlock.
17- SQL Server Trace Flag 1224
This trace flag will disabled the lock escalation that happens due to the number of locks.
18- SQL Server Trace Flag 2528
To disable the parallelism for DBCC command.
19- SQL Server Trace Flag 1211
if you want to disable the lock escalation in both thresholds scenarios: number of locks as well as memory pressure. Then you can use trace flag 1211 to disable the lock escalation process in both cases.
20- SQL Server Trace Flag 1262
If you want to generate 17883 mini dumps for each occurrence of 17883 then you should use trace flag 1262. This can provide you the more information for troubleshooting the nonyield issue.
21- SQL Server Trace Flag 1260
This trace flag will disable the collection of mini dump during 17883 errors.
22- SQL Server Trace Flag 2340
To overcome this high CPU consumption situation due to sort operation in plan.
23- SQL Server Trace Flag 2301
Queries will use the most optimal plan selected by optimizer rather than using sub optimal plan.
24- SQL Server Trace Flag 2537
By using this trace flag, function fn_dblog() will read both active and inactive portions.
25- SQL Server Trace Flag 2544
To change the default behavior of sql server for passing specific parameters during SqlDumper.exe calls.
To disable this default Read Ahead mechanism behavior.
To disable the default ghost cleanup task behavior.
To allocate all the buffer pool memory through windows large page allocation.
29- SQL Server Trace Flag 2371
By using this trace flag; SQL server will decide dynamic threshold value for auto update of statistics on the tables with more than 25000 rows. Higher the number of rows (cardinality) will use lower the threshold value for auto update of statistics.
30- SQL Server Trace Flag 2388
This trace flag provide us the historical information about statistics update.
31- SQL Server Trace Flag 2389
To change the behavior of SQL Server optimizer. This trace flag was introduced in SQL Server 2005 SP1.
32- SQL Server Trace Flag 2390
To change the behavior of cardinality estimator in the case where leading statistics column marked as ascending or unknown.
33- SQL Server Trace Flag 9481
If am using SQL Server 2014 with new cardinality estimator. Let’s consider that while using new cardinality estimator 98% query workload is running fine but for rest of the 2% performance is not good (consuming much resources) as compared to previous version of SQL Server. Here I want to use Legacy Cardinality estimator for these 2% queries. But the question is, Can we use legacy cardinality estimator for specific queries? Answer is yes. Microsoft provided a trace flag for such type of scenarios i.e. SQL Server 2014 Trace Flags 9481.
34- SQL Server Trace Flag 2312
To run your query with new cardinality estimator while using compatibility level 110 on SQL Server 2014.
35- SQL Server Trace Flag 8011
Under high performance workload we can disable the collection of few additional ring buffers for Resource Monitor by using trace flag 8011.
36- SQL Server Trace Flag 8012
Under high performance workload we can disable the collection of few additional ring buffers for Schedulers by using trace flag 8012.
37- SQL Server Trace Flag 8018
Under high performance workload we can disable the collection of few additional ring buffers for exceptions by using trace flag 8018.
38- SQL Server Trace Flag 8019
Under high performance workload we can disable the collection stack trace information for the exception ring buffers by using trace flag 8019.
39- SQL Server Trace Flag 4013
Trace flag 4013 write entries in error log whenever a new connection established. These entries contain login name and SPID also.
40- SQL Server Trace Flag 4030
Trace flag 4030 writes both bytes and ASCII representation of receive buffer. Here you can see the commands sent by client to SQL Server.
41- SQL Server Trace Flag 4031
Trace flag 4031 writes both bytes and ASCII representation of send buffer. Here you can see the data sent by SQL Server to client.
42- SQL Server Trace Flag 4032
Trace flag 4032 traces all the commands coming from clients. Here you can see the commands sent by client to SQL Server.
43- SQL Server Trace Flag 4136
By using Trace Flag 4136 we can force the optimizer to use density rather than using histogram for cardinality estimation.
44- SQL Server Trace Flag 7806
If you want to use DAC on SQL Server Express Edition then you will enable the trace flag 7806.
45- SQL Server Trace Flag 3213
This is one of the trace flag which can provide you the internal information about backup and restore operations. In simple words we can say that, by using this trace flag we can check the total amount of memory or buffers used for backup or restore operation.
46- SQL Server Trace Flag 3608
This trace flag is used to prevent automatically starting and recovering any database except master database.
47- SQL Server Trace Flag 8602
This trace flag is used to ignore all the index hints specified in query or stored procedure. We can use this trace flag to troubleshooting the query performance without changing index hints.
48- SQL Server Trace Flag 9292
This trace flag is used to get the report about statistics objects considered as interesting by query optimizer during compilation or recompilation of query. Keep in mind that only header is loaded for these interesting statistics. Let me show you this practically.
49- SQL Server Trace Flag 9204
This trace flag is used to get the report about statistics objects which are fully loaded and used by the optimizer for cardinality estimation.
50- SQL Server Trace Flag 8721
This trace flag is used to dump information into SQL Server Error log when AutoStat has been run.
PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.
HAPPY LEARNING!
Regards:
Prince Kumar Rastogi
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook
Hi Prince,
Thanks for taking the time to document these trace flags.
Just a small suggestion – If you were to add another column to the list above with a snippet of what each trace flag does it would indeed be even more useful. At the moment you have to click each one for find out.
Regards
Tony S.
Hi Tony S,
Thank you for your excellent suggestion. Snippet has been added to each Trace Flag.
Thanks & Regards:
Prince Rastogi
Great post.