Transcript

A big question that I often get when talking about optimizer hotfixes is, should I turn this on by default? Trace Flag 4199 does have some really serious fans out there, because over the years, people hit some of these edge conditions, fixed by TF 4199, and they got tired of hitting them.

So, some folks out there do like turning this on by default.

I don’t really love enabling it by default

This is a preference. And the reason that I don’t love enabling it by default is just that it enables a lot of tiny little things which are hard to explain to a change approver, and once it’s on globally, for the whole instance, it’s really hard to test what would happen if we didn’t have this Trace Flag on.

Your choice, there’s no simple work around for this, you cannot turn it off at the session level if it’s on globally, because globally, means really globally.

You can enable at the session level, but there’s no real disable at the session level, if that makes sense. Like, global is still going to apply to you. So, I mean, we should have the ability to restore a database to a separate server, but does that separate server have identical hardware? The amount of memory you have, and the number of processors influence how your query gets optimized by SQL Server. Not everyone has identical hardware to restore their critical production database to, to get the exact same optimization they’d see in production. And even if you do have the identical hardware, you may be testing an upgrade on it right now. You may have a different version of SQL Server on it than you do in production.

It can be a little like testing out what would happen if I didn’t have this, isn’t so great, when it comes to Trace Flag 4199. This is why I love that in 2016, they have merged in all those prior fixes into the mainline code base.

That means the things that 4199 impacts is a smaller set. More targeted, less risk, right? And those, the historical fixes have had a long time to burn in and be proven, the fixes prior to SQL Server 2016. So, I love the fact that if you use compat level 130, and enables all those fixes, and I also love that we have that database option.

I do prefer to only enable it when I know that I need it, and I know that I am fixing at least one specific thing, because every feature that we use, there’s always a risk with any feature that we use. I prefer that targeted use hint option when available, to say, “Okay, I’m going to fix it for these queries.”

Of course, I have to have the ability to apply it to specific queries. I love testing out optimizer hotfixes when tuning a query, too. When you’re going through the process of checking, “Can I make this query faster?” testing out does the- Am I hitting an edge condition? And does 4199 make it faster, is definitely a step I do. I don’t always find that it makes a query faster, but there have been a few cases, where in fact, I was hitting an edge case that was improved by this, and testing, using option querytraceon, with a high permission account, made it easy for me to see whether or not query optimizer hotfixes made a real difference to my case.