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.

3- SQL Server Trace Flag 845

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.

4- SQL Server Trace Flag 3604

Trace flag 3604 is used to redirect the output of some DBCC commands to the result window.

5- SQL Server Trace Flag 3605

Trace flag 3605 is used to redirect the output of some DBCC commands to the SQL Server Error Log .

6- SQL Server Trace Flag 3502

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.

7- SQL Server Trace Flag 3504

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.

8- SQL Server Trace Flag 3505

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.

9- SQL Server Trace Flag 3014

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.

26- SQL Server Trace Flag 652

To disable this default Read Ahead mechanism behavior.

27- SQL Server Trace Flag 661

To disable the default ghost cleanup task behavior.

28- SQL Server Trace Flag 834

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