Press "Enter" to skip to content

Category: Trace Flags

Dealing With Trace Flags In The Registry

Published 2017-09-06 by Kevin Feasel

Wayne Sheffield shows us how to configure SQL Server trace flags within T-SQL using registry access commands:

In a recent post, I introduced you to how to work with the registry directly from within SQL Server. Continuing this theme, this post provides an example situation where you would do so.

In this example, we will want to configure SQL Server to enable a few trace flags (TF) when SQL Server starts. Specifically, let’s set trace flags 1117 and 1118 so that they are enabled when SQL Server starts up, and enable them now. Additionally, we have trace flags 1204 and 1222 now enabled on some servers, and we want to disable those (since we have the deadlocks being captured in the system health XE, we don’t need them in the error log also). We also don’t want to force a restart of the SQL Server services.

I’ve always felt a little icky about writing to the registry from SQL Server, but Wayne shows how to do it right.

Comments closed

Trace Flags Used With Query Store

Published 2017-06-01 by Kevin Feasel

Erin Stellato describes two Query Store trace flags:

Microsoft maintains a list of supported trace flags and I noticed that there are two new ones related to Query Store: 7745 and 7752.  The descriptions for these Query Store Trace Flags are pretty straight-forward, but for those of you not familiar with Query Store, I thought I’d provide some context and details.

Click through for the descriptions of these two trace flags.

Comments closed

Disabling Nested Loop Join Optimization

Published 2017-05-18 by Kevin Feasel

Dmitry Pilugin explains the differences between trace flag 2340 and the DISABLE_OPTIMIZED_NESTED_LOOP query hint:

This optimization provides a great boost with a sufficient number of rows. You can read more about its test results in the blog OPTIMIZED Nested Loops Joins, created by Craig Freedman, an optimizer developer.

However, if the actual number of rows is less than the expected one, then CPU additional costs to build this sort may hide its benefits, increase CPU consumption and reduce its performance.

Read the whole thing.  I think the likelihood of using either this hint or the trace flag is near nil, but crazy things do come up.

Comments closed

Global Trace Flags Are Global

Published 2017-04-21 by Kevin Feasel

Kendra Little points out that when you turn a trace flag on globally, it cannot be off for individual sessions:

In SQL Server 2016, you can now enable the very same optimizer hotfixes controlled by Trace Flag 4199 at the database scope by using ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON.

If you have the setting configured at the database level, it’s much easier to test what would happen if the setting was NOT enabled, because you can compile your query from a different database.

Interesting results.  Check it out.

Comments closed

Creating A Spinlock Convoy

Published 2017-03-15 by Kevin Feasel

Lonny Niederstadt explains how two performance issues can intertwine in an interesting manner:

I’d seen systems that implemented both trace flags as startup parameters simultaneously.  I’d helped organizations implement first T8048, then T4199 (based on the timing of my research and testing of the trace flags).  This was the first time that there was a desire to implement the trace flags one-at-a-time and we had the choice of which to implement first.

I hadn’t chosen to put T8048 in first previously – that was just the way everything worked out.  If I chose to follow that order – I’d be doing what I’d seen and done before.  But… there was also a reason to choose the reverse order, with T4199 first.  Spinlock issues – especially at that time – were considered more exotic performance issues than many of the “plan-shaping” issues that trace flag 4199 addressed.  Many administrators were much more familiar with that type of performance issue – eliminating significant waits, altering plan shapes, making the logical work of queries more efficient – than with the busy wait/management overhead of spinlocks.  Sometimes demonstrating an improvement that someone is already familiar with evaluating is a plus, helping to gain trust.  I didn’t know of a specific reason NOT to put trace flag T4199 in place, followed by T8048 later.  And in this case it seemed like building up some interpersonal capital might be a good idea.

Thinking through the full ramifications of trace flag changes is hard, even for sharp people like Lonny.  Read on for the details of what happened next.

Comments closed

Trace Flag Basics

Published 2017-03-06 by Kevin Feasel

Erin Stellato explains the basics behind trace flags in SQL Server:

Trace flag 1118 addresses contention that can exist on a particular type of page in a database, the SGAM page.  This trace flag typically provides benefit for customers that make heavy use of the tempdb system database.  In SQL Server 2016, you change this behavior using the MIXED_PAGE_ALLOCATION database option, and there is no need for TF 1118.

Trace flag 3023 is used to enable the CHECKSUM option, by default, for all backups taken on an instance.  With this option enabled, page checksums are validated during a backup, and a checksum for the entire backup is generated.  Starting in SQL Server 2014, this option can be set instance-wide through sp_configure (‘backup checksum default’).

The last trace flag, 3226, prevents the writing of successful backup messages to the SQL Server ERRORLOG.  Information about successful backups is still written to msdb and can be queried using T-SQL.  For servers with multiple databases and regular transaction log backups, enabling this option means the ERRORLOG is no longer bloated with BACKUP DATABASE and Database backed up messages.  As a DBA, this is a good thing because when I look in my ERRORLOG, I really only want to see errors, I don’t want to scroll through hundreds or thousands of entries about successful backups.

Click through for more useful information, including a list of officially supported trace flags.

Comments closed

Table Variables And TF2453

Published 2017-02-08 by Kevin Feasel

Tara Kizer investigates Trace Flag 2453:

I recently saw a server with trace flag 2453 configured. I hadn’t come across this trace flag before, so I did a little research. Microsoft says it allows “a table variable to trigger recompile when enough number of rows are changed”. This can lead to a more efficient execution plan. Trace flag 2453 is available in SP2 or greater for SQL Server 2012, CU3 or greater for SQL Server 2014 and RTM or greater for SQL Server 2016.

I was curious how a query using a table variable performed as compared to the “same” query using:

  • trace flag 2453

  • OPTION (RECOMPILE)

  • a temporary table

Click through for a relative performance comparison.

Comments closed

Updating Multiple Statistics Concurrently

Published 2016-12-22 by Kevin Feasel

SQL Scotsman explains trace flag 7471, which allows you to update multiple statistics on a table concurrently:

Running multiple UPDATE STATISTICS commands for different statistics on a single table concurrently has been available under global Trace Flag 7471 since SQL Server 2014 SP1 CU6 and SQL Server 2016 CU1.  Microsoft have documented this trace flag here and here.

It sounds like, for the most part, you might not want this flag turned on, but read the whole post.

Comments closed

Query Optimizer Hotfixes

Published 2016-12-08 by Kevin Feasel

SQL Scotsman covers the query optimizer hotfixes which you can turn on with trace flag 4199:

The query optimiser hotfixes contained under Trace Flag 4199 are intentionally not enabled by default.  This means when upgrading from SQL Server 2008 R2 to SQL Server 2012 for example, new query optimiser logic is not enabled.   The reason behind this according to the article linked above is to prevent plan changes that could cause query performance regressions.  This makes sense for highly optimised environments where application critical queries are tuned and rely on specific execution plans and any change in query optimiser logic could potentially cause unexpected / unwanted query regressions.

Read the whole thing.

Comments closed