Trace flags SQL Server’s transformer like Tuning

Just like Windows when you install a SQL Server, it’s more a generalist then a specialist. Trace flags is the road to follow when you as a DBA want to tune your SQL server to look like either a F1 car for OLTP or an Optimus prime monster-truck-like-Australian road-train for DWH queries. Both of them are extremely powerful in their own field, but the key is to use that power correctly.

First things firsts, while a lot of the trace flags mentioned in this blogpost are quite safe to use, some of them are not, so as usual handle with care and knowledge. As such this blogpost is not responsible for any damage to any environment due to careless use of any of these parameters.

So don’t activate them blindly, use them thoughtfully and to your advantage.

Trace flags I’d expect

These are the trace flags I would expect to see on a well configured SQL Server. These can be used with low to moderate risk.

1117: This flag forces all data files in a file group to grow at once, which reduces “hot spots” of data pages. This ensures that all databases with more than one data file will grow properly across all the data mounts, which in turn ensures maximum physical I/O performance.

1118: Switches allocations in tempdb from 1 page at a time (for the first 8 pages) to one extent. There is now a cache of temp tables. When a new temp table is created on a cold system it uses the same mechanism as it did in SQL 8. When it is dropped though, instead of all the pages being deallocated completely, one IAM page & one data page are left allocated, then the temp table is put into a special cache. Subsequent temp table creations will search in the cache for a pre-created temp table. If one is found, this avoids accessing the allocation bitmaps completely. The temp table cache isn’t huge (32 tables), but this can still lead to a big drop in latch contention in tempdb. For more info, please see this blogpost.

2371: This trace flag comes right out of the SAP recommendations and needs SQL 10.5 SP1 or higher. This trace flag changes how automatic statistics are triggered. On enabling this flag, the standard 20% changes to a dynamic value if the table has over 25000 rows, reducing as the count increases.

2562: This trace flag also needs SQL 10.5 or higher. When enabled it runs the DBCC CHECKDB command in a single “batch” regardless of the number of indexes in the database. By default, the DBCC CHECKDB command tries to minimize tempdb resources by limiting the number of indexes or “facts” that it generates by using a “batches” concept. This trace flag forces all processing into one batch. As such this flag improves the internal processing for determining which pages to read from the database. This reduces the contention on the DBCC_MULTIOBJECT_SCANNER latch.

3226: This trace flag actually makes the SQL logs readable as it suppresses BACKUP COMPLETED log entries going to WIN and SQL logs.

4199: Trace flag 4199 is used to collect hotfixes that were intended to become on-by-default in a future release, whereas other trace flags were used for situations in which a fix was not intended to become on-by-default in current form. Starting with SQL Server 2016 RTM, the database COMPATIBILITY_LEVEL 130 setting will be used to enable trace flag 4199-related hotfixes on-by-default. It contains the following query optimizer changes, previously made under multiple trace flags: 4101-4111,4115-4117,4119-4122,4124-4129,4131-4133,4135 (Note that it doesn’t contain 4130!).

DWH trace Flags

Data warehouses being the massively IO hungry beasts that they are require very specific trace flags. This is especially true when you’re trying to keep your system as fragmentation-free as possible.

610: This trace flag enables minimal logged mode for non-empty B-Tree’s. When you insert a large amount of data regularly, you don’t want to create a lot of transaction log when it’s not needed. This trace flag is typically used for ETL or DWH servers but even OLTP servers that have bulk loading in their process flow can benefit from this trace flag. It is mainly used to reduce the size of the transaction log. There are cases where performance might be better, this is not the main goal of this Trace flag.

1117: Was used to increase performance and ensures maximum physical I/O performance.

-E Technically this is not a trace flag but a startup parameter. You need it when your DWH is running on normal SAS drives but has a negative impact on SSD’s, so be careful. This parameter increases the number of extents that are allocated for each file in a filegroup. This option may be helpful for data warehouse applications that have a limited number of users running index or data scans. It should not be used in other applications because it might adversely affect performance.

Large Machine Trace flags

Consider your machine to be large, when you have at least 256 GB Ram and 8 or more cores per socket

8048: Newer hardware with multi-core CPUs can present more than 8 CPUs within a single NUMA node. Microsoft has observed that when you approach and exceed 8 CPUs per node the NODE based partitioning may not scale as well for specific query patterns, using trace flag 8048 all NODE based partitioning is upgraded to CPU based partitioning. Remember, this requires more memory overhead but can provide performance increases on these systems.

1236: FIX: Performance problems occur when database lock activity increases in SQL Server, is only needed when you’re running on SQL Server editions lower then 2014SP1 or 2012SP3 when you see elevated values occur for the LOCK_HASH spinlock count.

8015: Creates Soft Numa should only be used on single socket/numa machines with a processors exceeding 8 cores (for example the monstrous E 5-2687W v4, E5-2697A v4 or E5-2697 v4 processors, boasting 12,16 and 18 cores on one processor. Another possible case are VM’s that only show one Numa-Node to the guest machine. You should be careful to use it on machines that are using two or more sockets because you cannot create soft-NUMA’s that includes CPUs from different hardware NUMA nodes. On single socket SMB machines there will be only have one hard-numa and increased I/O and lazy writer bottlenecks might occur. The reason for this is that there is only a single I/O thread and a single lazy writer thread for each NUMA node. Depending on the usage of the database, these single threads may be a significant performance bottleneck. Configuring multiple soft-NUMA nodes provides more I/O threads and more lazy writer threads, which could increase performance. Please note that only the SQL Server scheduler and SQL Server Network Interface (SNI) are soft-NUMA aware. Memory nodes are created based on hardware NUMA and therefore not impacted by soft-NUMA.

Somewhat more dangerous, but very useful trace flags

834: Allows large-page allocations for the memory that is allocated for the buffer. Large pages are allocated at startup and are kept throughout the lifetime of the process. Trace flag 834 improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU. Because of that it applies only to the 64-bit versions and you should have the Lock pages in memory right granted to turn this on. On the down side, it might prevent the server from starting if the memory is too fragmented and large pages cannot be allocated.

845: Enables lock pages in Memory for the 64 bit SQL Server non enterprise edition. Please note that Startup time of SQL Server might take longer because SQL Server will allocate all its memory up to the Max Server Memory setting.

836: Uses the max server memory option for the buffer pool and causes SQL Server to size the buffer pool at startup based to the value of the max server mem option instead of the total physical memory. By doing this it will reduce the number of buffer descriptors that are allocated at startup.

1140: TempDB fix for SQL2008 & SQL2005 as described in KB2000471.

1204: Returns the resources and types of locks participating in a deadlock and also the current command affected. In SQL 2012 or higher I prefer to use extended events.

1222: Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD. In SQL 2012 or higher I prefer to use extended events.

1224: Disables lock escalation based on the number of locks while retaining memory lock escalation. The Database Engine will now only escalate row or page locks to table (or partition) locks if the amount of memory used by lock objects exceeds one of the following conditions:

• 40% of the memory that is used by Db Engine, exclusive of memory allocation using AWE. This is applicable when the locks parameter of sp_configure is set to 0.
• Forty percent of the lock memory that is configured by using the locks parameter of sp_configure.

1236: Fixes the Elevated values for LOCK_HASH spinlock count in SQL 2012,2014 and 2016.

1807: Enables option to have database files on SMB share for SQL Server 2008 and 2008R2.

2335: Generates a plan that is more conservative in terms of memory consumption when executing the query for SQL 2008R2,2012,2014 and 2016 as the engine will occasionally generate an inefficient plan for a specific query when you configure a large value for max server memory. Using 2335 as a startup parameter will cause SQL Server to generate a plan that is more conservative in terms of memory consumption when executing queries. It does not limit how much memory SQL Server can use. The memory configured for SQL Server will still be used by data cache, query execution & other consumers.

2430: FIX: Large queries that modify data run slower than expected when many locks accumulate in a SQL Server 2008 R2 or in SQL Server 2012.

2453: FIX: Poor performance when you use table variables in SQL Server 2012 or SQL Server 2014.

2470: FIX: Slow performance when an AFTER trigger runs on a partitioned table in SQL Server 2008 R2 or in SQL Server 2012.

2549: This trace flag also needs SQL10.5 or higher, and improves DBCC CHECKDB because when this trace flag is enabled, each database file is assumed to be on a unique disk drive. Do not use this trace flag unless you know that each file is based on a unique physical disk. KB 2634571

Taking care of Query-Hint-Hell

As a DBA we sometimes have to live with the terror better known as Hints that “help” us to solve issues forced upon our poor query engine, or as Brent Ozar calls it… SQL Server has a couple of similar features that might make your queries faster today – but they’ll make you sing the blues tomorrow.

The problem we’re often facing as a DBA, is how to stop singing the blues, when a third party application is hammering our engines with their hints… We can solve that, but know that when you simply kill all the hints, you just might get better performance but for the same amount of money, it might send your response times right down the drain. So tread carefully here. Please note that these trace flags do not take into consideration any plan guides that might got sneaked in so they are no “silver bullet”.

4136: Forces OPTIMIZE FOR UNKNOWN on SQL Server 2008R2,2012,2014,2016

6498: Increases Query Compilation Scalability in SQL2014

8602: Disable Query Hints on SQL Server 2012,2014,2016

8722: Disable ALL hints except locking Hints for SQL Server 2012,2014,2016

8755: Disables Locking Hints for SQL 2012,2014,2016

As always, tweak carefully.