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.
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.
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.
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.
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.
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.
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
a temporary table
Click through for a relative performance comparison.
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.
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.
I was perusing the release notes for SQL Server 2014 SP2 and found this gem:
Ohhhhhh, very cool. This is great information for troubleshooting!
Read the whole thing.