Transcript

Query optimizer hotfixes are another way that you can influence how your execution plans are generated in SQL Server.

Historically, we have enabled query optimizer hotfixes using trace flag 4199 in SQL Server

This enables multiple optimizer hotfixes. Now for years, we had trace flag 4199. And new hotfixes kept getting added to it over time. And they didn’t get merged in to the mainline code in SQL Server. So the number of things impacted by 4199 just sort of slowly accumulated over time.

In SQL Server 2016, we got a really cool improvement from the product group…

…where they said, look, we’ve built up fixes in here over the years that help out edge cases, but they, they really help them out, so we’re going to go ahead and merge these into the mainline code base.

Now they didn’t want to just turn it on for everyone and not have any way to disable it. So compatibility level for your database plays into this. On SQL Server 2016, if you set your database compatibility level to 130, latest and greatest, then you will get all of the hotfixes that were ever rolled into 4199 before SQL Server 2016. You don’t have to enable the trace flag, in other words. They’re just automatically…

The previous pre-2016 fixes are on just if you’re at compat level 13

Now, you might be like, well what if I want these fixes, but I don’t want the cardinality estimator. You have a new option at the database level where you can say I want compat level 130, but I actually want the old cardinality estimator.

So you’ve got many different ways to get the settings that you need.

Trace flag 4199 isn’t going away

Trace flag 4199 is still around, and because there’s new little hotfixes that are sometimes released for these edge cases, even after SQL Server 2016, so you can still enable trace flag 4199 to get hotfixes after SQL Server 2016 RTM.

Even if you’re at compat level 130 or higher. For the trace flag version of this, and we do have other ways to turn this on now too, which is cool, we can set DBCC TRACEON to say I want to, using this T-SQL command, I want to enable it for the whole instance.

You can also set it as a start up flag in your SQL Server so that you don’t have to actually run the T-SQL. That means that every query that runs against the entire instance is going to use all of the optimizer hotfixes available, depending on what version of SQL Server you’re on right?

Maybe you’re using trace flag 4199 on SQL Server 2008, right, instance wide. You could also enable it just for your session.

By using DBCC TRACEON, without the negative one, I’m saying just for my session go ahead and enable it. And you can enable it per query using OPTION.

All of these options require SYSADMIN permissions, but there is a workaround

If I just want to use OPTION for a specific query, I can create a stored procedure, owned in the dbo schema and use the query hint, you know put it as a query hint in the stored procedure. And then if I’ve done that, a user who doesn’t have sysadmin rights will still be able to execute the stored procedure because of ownership chaining. Ownership chaining is the actual term. I almost said ownership changing. The connect item on the permissions on this, on Microsoft, lists that as a workaround. And they also mention in there, hey we’re fixing this in a better way in SQL Server 2016.

And the better way is better, it’s really cool, we have new options in 2016. Instead of like, so the trace flag is only global or session or per query.

In SQL Server 2016, we have these new database scoped configuration options

We can say I want to turn on QUERY_OPTIMIZER_HOTFIXES, but only for this one database. It’s the same as implementing the trace flag, but it’s at a different scope. It’s at the database scope. And this is really cool that we can control it that way, for multiple reasons. We may want to just… We may only need it in one database, right?

And we may not want to risk possible query regression in other databases, so it gives us a way to not have to change the T-SQL to put a hint into all these different queries and we still don’t have to do it for the whole instance. It also improves our testing options. So think about this scenario.

Let’s say I enable trace flag 4199 for my entire instance

And I’m troubleshooting a query. You know, I did it last week. Right, cause it fixed something. I’m working on a different query this week. And the query is slow, and I want to know well, would this query be faster if I didn’t have trace flag 4199 on? How do I test that? It’s on globally for the instance. I don’t have a way to disable it for an individual query.

I need to either disable it for the whole instance, which could impact someone else, right? That’s not good. Or I need to go find, restore my database to a different instance and test it over there. It’s not a great option.

With the database level, QUERY_OPTIMIZER_HOTFIXES, I have more options. Because it impacts having this at the database scope means compiling the query when you are using that database. So I could say okay I’m going to use temp db. And I’m going to change my query for testing purposes to reference those tables using three part naming.

But I’m compiling my query in the scope of tempdb. Tempdb doesn’t have query optimizer hotfixes on. So I can actually see how would you compile without query optimizer hotfixes? It’s very cool, and because it’s just database scoped, I have more options taking advantage of that scope. I also now, in SQL Server 2016, after service pack one, have the USE HINT option to enable QUERY_OPTIMIZER_HOTFIXES and this does not require elevated permissions. So we have not only the database scope, but they fixed that permissions issue. Let’s dive in and take a look at these QUERY_OPTIMIZER_HOTFIXES.