When applying a new SQL Server cumulative update, hot fix, or upgrade SQL Server doesn’t always apply all the fixes in the patch. When you upgrade the database engine in-place, databases you had already stay at their pre-upgrade compatibility level, which means they run under the older set of optimizer rules. Additionally, many optimizer fixes are not turned on. The reason for this is that while they may improve overall query performance, they may have negative impact to some queries. Microsoft actively avoids making breaking changes to its software.
To avoid any negative performance impacts, Microsoft has hidden optimizer fixes behind a trace flag, giving admins the option to enable or disable the updated fixes. To take advantage of optimizer fixes or improvements you would have enable trace flag 4199 after applying each hot fix or update or set it up as a startup parameter. Did you know this? This was something I learned while working with an existing system, years into my career. I honestly assumed it would just apply any applicable changes that were in the patch to my system. Trace flag 4199 was introduced in the SQL Server 2005-era. In SQL Server 2014, when Microsoft made changes to the cardinality estimator they protected the changes with trace flags as well, giving you the option to run under compatibility level 120 and not have the cardinality estimator changes in effect.
Things changed starting with SQL Server 2016.
Click through to see how SQL Server 2016 made it a bit easier.
With the release of SQL Server 2016, these trace flags were rumored to be a thing of the past and hence completely unnecessary. That is partially true. The trace flag is unneeded and SQL 2016 does have some different behaviors, but does that mean you have to do nothing to get the benefits of these Trace Flags as implemented in 2016?
As it turns out, these trace flags no longer do what they did in previous editions. SQL Server now pretty much has it baked into the product. Buuuuut, do you have to do anything slightly different to make it work? This was something I came across while reading this post and wanted to double check everything. After all, I was also under the belief that it was automatically enabled. So let’s create a script that checks these things for me.
Click through for the script and a summary of his findings.
Below is a list of trace flags which, as far as I can tell, have never been publicly documented. I did not fully investigate many of them and many of the descriptions are just guesses. I make no guarantees and none of these should be used in production. All tests were performed on SQL Server 2017 CU2 with trace flags enabled at the global level.
This is combining a bit of database archaeology and database anthropology.
About once a month, I get support ticket regarding SQL Server dropping an application’s or user’s connection. The problem is SQL Server does not just randomly drop a connection and continue to work normally. Some force outside the control of SQL Server breaks the connection. By default, SQL Server does not record when this event occurs.
In my history of working with SQL Server, only in extreme situations have I ever seen SQL Server drop its connections. The most common example is when SQL Server is in the process of shutting down.
Click through for the trace flag and details.
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.