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.
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.
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.
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.
This trace flag can be used to stop the logging of successful backup messages in the log.
Database restore internal information will be logged phase by phase.
By using this trace flag, if auto grow event will fire under a filegroup then all files under that filegroup will grow.
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.
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.
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.
This trace flag will disabled the lock escalation that happens due to the number of locks.
To disable the parallelism for DBCC command.
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.
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.
This trace flag will disable the collection of mini dump during 17883 errors.
To overcome this high CPU consumption situation due to sort operation in plan.
Queries will use the most optimal plan selected by optimizer rather than using sub optimal plan.
By using this trace flag, function fn_dblog() will read both active and inactive portions.
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.
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.
This trace flag provide us the historical information about statistics update.
To change the behavior of SQL Server optimizer. This trace flag was introduced in SQL Server 2005 SP1.
To change the behavior of cardinality estimator in the case where leading statistics column marked as ascending or unknown.
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.
To run your query with new cardinality estimator while using compatibility level 110 on SQL Server 2014.
Under high performance workload we can disable the collection of few additional ring buffers for Resource Monitor by using trace flag 8011.
Under high performance workload we can disable the collection of few additional ring buffers for Schedulers by using trace flag 8012.
Under high performance workload we can disable the collection of few additional ring buffers for exceptions by using trace flag 8018.
Under high performance workload we can disable the collection stack trace information for the exception ring buffers by using trace flag 8019.
Trace flag 4013 write entries in error log whenever a new connection established. These entries contain login name and SPID also.
Trace flag 4030 writes both bytes and ASCII representation of receive buffer. Here you can see the commands sent by client to SQL Server.
Trace flag 4031 writes both bytes and ASCII representation of send buffer. Here you can see the data sent by SQL Server to client.
Trace flag 4032 traces all the commands coming from clients. Here you can see the commands sent by client to SQL Server.
By using Trace Flag 4136 we can force the optimizer to use density rather than using histogram for cardinality estimation.
If you want to use DAC on SQL Server Express Edition then you will enable the trace flag 7806.
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.
This trace flag is used to prevent automatically starting and recovering any database except master database.
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.
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.
This trace flag is used to get the report about statistics objects which are fully loaded and used by the optimizer for cardinality estimation.
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.
Prince Kumar Rastogi