SQL Server Magic Buttons What are Trace Flags

SQL Server Magic Buttons! What are Trace Flags and why should I care? Steinar Andersen, SQL Service Nordic AB Thanks to Thomas Kejser for peer-reviewing

About me § SQL Server nerd since 1995 § SQL Server 4. 21, 6. 0, 6. 5, 7. 0, 2005, 2008, 2012, 2014, 2016, Azure § Former Member of the Board SQLUG (sqlug. se) § Blog: www. sqlservice. se/blogg § Twitter: @SQLSteinar § Speaker at: § § Tech. Days Microsoft Sommarkollo Cloud Seminarium SQL Saturday

What are Trace Flags? § Trace Flags are settings that in some way or another alters the behavior of various SQL Server functions. § Is inherited from Sybase SQL Server code § Some trace flags are still the same such as 1204, 3605, 3607, 3608 § REMEMBER: Be extremely careful with trace flags, test in your test environment first. And consult professionals first if you are the slightest uncertain about the effects of your changes.

Why should I care? § It could save you some day, like it saved me… § http: //www. sqlservice. se/sql-server-2012 -cu 1 -upgrade-stepmsdb 110_upgrade-sql-encountered-error-547/

How do I use Trace Flags? § § § § § How do I turn Trace Flags on and off? *1 – You can use the DBCC TRACEON and DBCC TRACEOFF commands – You can use the -T option in the startup configuration for the SQL Server Service -You can also use the hint QUERYTRACEON in your queries: <querytraceon_hint> : : ={ QUERYTRACEON trace_flag_number } How do I know what Trace Flags are turned on at the moment? – You can use the DBCC TRACESTATUS command Numbers from -1 to 10100 seems to be valid *2 Some Trace Flags only works in combination with others Some Trace Flags only works when used a certain way § Startup, QUERYTRACEON, Global § Turning on all Trace Flags – Do try this at home! (Not at work. . ) *3

Ok, ok! Now show me the Trace Flags! § § § Statistics Locks Memory Performance Troubleshooting Other

Statistics § 2371 § Updates statistics more often on tables with more than 25000 rows § 2389 *4 § Enable auto quick statistics update on known ascending columns § 2390 *4 § Enable auto quick statistics update on all columns § 4136 *4 § Uses density vector instead of histogram for cardinality estimate

Locks § 1200 *5 § Prints the process ID and type of lock requested as every request for a lock is made § 1204, 1222 § Prints deadlock info in the errorlog § 1211 § Disables lock escalation based on memory pressure § 1224 *5 § Disables lock escalation based on number of locks § 1236 § Fixes performance problem in scenarios with high SH-DATABASE lock activity in SQL 2012 and SQL 2014

Memory § 834 § Large Page Allocations (Might be on by default from SQL 2014) § 2335 § Generates Query Plans optimized for less memory § To be used if you have 512 GB ram or more § 2544 § Produce a full memory dump in case of an exception § 8032 § Reverts the cache limit parameters to the SQL Server 2005 RTM setting which in general allows caches such as the Plan Cache to be larger

Performance § 610 § § 661 § § Disable specific SORT optimization in Query Plan 4199 § § Forces the query optimizer to use the SQL Server 2014 version of the cardinality estimator 2340 § § Force Uniform Extent Allocation 2312 § § Simultaneous Autogrowth in Multiple-file database 1118 § § Prints detailed information about the work done by the ghost cleanup task when it runs next. (Use with TF 3605) 1117 § § Disable the ghost record removal process 662 § § Minimally logged inserts to indexed tables Turn on all optimizations. But from SQL 2016 on, MS has an altered strategy. New major versions will have all ”old” 4199 fixes enabled. 8780 § Give the optimizer more time to find a better plan

Troubleshooting § 902 § Bypass Upgrade Scripts § 3607 § Skip recovery on startup § 3608 § Recover only Master db at startup § 3609 § Do not create tempdb at startup § 4022 § Bypass Startup procedures

Other § 445 – ”Compile Issued” statement to errorlog (with 3605) – New! *6 § 2388 – Alter DBCC SHOW_STATISTICS output – New! § 2529 – Displays DBCC memory allocations – New! *7 § 3604 - Redirect DBCC command output to query window § 3605 - Directs the output of some Trace Flags to the Errorlog § 3226 - Turns off ”Backup Successful” messages in errorlog

What Trace Flags should I always turn on? § On new servers: 834 for DW servers, 1117, 1118, 3226, 4199 (not on SQL 2016) § On exsisting servers: 834 for DW servers , 1117, 1118, 3226

Read more! § SQL Service Trace Flag List: http: //www. sqlservice. se/updated-microsoft-sql-servertrace-flag-list/ § SQL Server Central TF List: http: //www. sqlservercentral. com/articles/trace+flags/70131/ § MSDN TF list: http: //sqlserverpedia. com/wiki/Trace_Flags § Albert van der Sel TF list: http: //antapex. org/traceflags_sqlserver. txt § Technet Wiki TF list: http: //social. technet. microsoft. com/wiki/contents/articles/13105. trace-flags-in-sqlserver. aspx § Amit Banerjee TF list: http: //troubleshootingsql. com/2012/07/01/sql-server-2008 -trace -flags/ § Paul Randal discussing TF Pro’s and Con’s: http: //www. sqlskills. com/blogs/paul/thepros-and-cons-of-trace-flags/ § Some trace flags needs to be specified with ”t” rather than with ”T” in startup options! http: //technet. microsoft. com/en-us/library/ms 190737(v=sql. 110). aspx

Read even more! § QUERYTRACEON https: //support. microsoft. com/enus/kb/2801413